- Spreadsheets To Practice Pivot Tables
- Excel Pivot Table Tutorial Pdf
- How To Do A Pivot Chart In Excel For Mac Shortcut
- How Do I Make A Pivot Chart In Excel For Mac
In a post I wrote last week, I demonstrated in a video how to use advanced filters (one of Excel’s best-kept secrets) to divide a list of keywords into categories. I promised a commenter I would create a follow-up video demonstrating how I took those data sets and created a pivot chart that updated when you choose a new category.
Spreadsheets To Practice Pivot Tables
Select any of the cells from your pivot table. Go to Insert Tab → Charts → Pivot Chart and select the chart which you want to use. When i insert a new pivot table, and point to an Access database, MS Query for Mac opens. I can select tables, but i cannot see how to create joins. If i return the data to Excel, there's no 'Relationships' button on the ribbon. Drag fields to the Rows and Columns of the pivot table. Start building the pivot table; To add the text to the values area, you have to create a new special kind of calculated field called a Measure. Look at the top of the Pivot Table Fields list for the table name. Right-click the table name and choose Add Measure. Click on the PivotTable button and select Create Manual PivotTable from the popup menu. A Create PivotTable window should appear. Select the range of data for the pivot table and click on the OK button. In this example, we've chosen cells A1 to D13 in Sheet1.
Download Excel File
If we check the pivot table, we see it is filtered in exactly the same way. In fact, because the pivot table and pivot chart are linked together, you might want to turn off the chart buttons, and just use the pivot table to control filtering. This will give your chart a much cleaner look, but might need to label the chart make active filters clear.
If you’d like to download the Excel file I used, you can access it here. Feel free to kick it around and test out some of the filters I demonstrate in the video.
Caveat
Much to my chagrin, pivot charts are PC swim only. You can’t create a pivot chart in Excel 2011 for Mac. (Boo hiss!!!) But not only can you not create one, you can’t even use a report filter on the Mac. You can see this epic fail in action here.But if you’re creating a pivot chart, and there’s the chance that anyone might have to access it from a Mac, you cannot use report filters.
I created a really cool pivot chart for Loren Baker to show a client once, but he was using a Mac and couldn’t use the report filter drop-down. He was in a hurry and asked me to just create screenshots of each of the charts, and he showed those to the client instead. I’ve never quite forgiven Microsoft for that. :/ (Yes, I take making data sexy very seriously. And don’t see the light of day much.)
Video Tutorial
I created a video demonstrating how to take the data spawned from the advanced filters. And even though I said I wasn’t going to get into chart formatting since I’ve covered that in this post from my SMX Advanced presentation and this Search Engine Land post, I did anyway. Because it’s a compulsion. Have you ever tried to control a compulsion? Not. Easy.
Anyway, check it out.
How To Change Dashes To Zeros
As I mention in the video, I really don’t like how Excel formats zeros as dashes in charts. But you can see a quick tutorial on how to change those dashes to zeros or check out my more comprehensive post on number formatting on the Search Engine Land site.
Learn More
You can learn more about data formatting in my Annielytics Dashboard Course, a video course that will teach you how to put your data in stilettos and work the pole. 🙂
Picture this: Your boss just sent over a massive Excel file. It has hundreds—or maybe even thousands—of rows of data. And, to make things worse, within all of those digits he would like you to find a very specific trend or piece of information.
Is your head spinning? Are your eyes glazing over? You aren’t alone. Staring at all of those rows, columns, and numbers is overwhelming.
Fortunately, Excel is incredibly powerful and literally exists to take the elbow grease out of the very data analysis that’s being requested of you.
In this Pivot Table tutorial, we’re going to show you how to use this awesome feature, in five easy steps.
Practice makes perfect!
Download your free excel spreadsheets to practice
What is a pivot table?
Don’t run for the hills just yet. You’ve likely heard of pivot tables before. And, if you’re like most people, you immediately wrote them off as yet another Excel tool that was too complicated for you to master.
But, they can actually be surprisingly simple to utilize—and, they’re sure to remove a lot of headaches from your data management duties.
Put simply, a pivot table summarizes your data. It empowers you to extract significant trends or findings from what is otherwise a totally overwhelming spreadsheet.
Curious? Check out this video to see all of the different capabilities of an Excel pivot table:
But basically, all you need to know is that something that would typically take a long time can be done quickly and painlessly when you build a pivot table. And don't worry, this pivot table tutorial will guide you!How to build a pivot table: A case study
You get it—pivot tables are awesome. However, that doesn’t change the fact that you have no clue how to build one.
Well, have no fear! We’ll walk you through it step by step. And, since there’s nothing like an example to add some clarity, let’s look at a specific scenario when a pivot table could be helpful.
Meet Jason. Jason brews and sells craft beer in a quaint brewery in his hometown. In order to better manage his inventory and brewing schedule, he wants to see if there are any trends in terms of the type of beer that sells most each quarter.
For example, do people drink more dark beer in the wintertime? Getting a better grasp on any seasonality would help him a lot, but to start he only has a spreadsheet that breaks down his sales of each type of beer (stout, pilsner, IPA, and an amber) per quarter in 2016 and 2017.
So, to make this easier, Jason has decided he’s going to build a pivot table to see which beer he sold the most of during each quarter.
Let’s walk through the steps along with Jason.
1. Clean your data
Before you start building anything in Excel, it’s smart to take a quick look at your data to ensure that everything looks correct.
Spelling is something that you’ll want to pay especially close attention to when building a pivot table. For example, if Jason had a typo that said “Sout” instead of “Stout” somewhere in his spreadsheet, the pivot table would pull both of those into the data summary.
Want a quick way to check if there are any spelling errors? Highlight only the first row of your data, click on the “Data” tab of your ribbon, click “Filter,” and then click the arrow that appears on the column header to see all of the different items that are listed within that column. Obviously, this tip works best for more manageable data sets.
Finally, while we’re talking about column headers, it’s important to note that Excel will not create a pivot table unless each of your columns has a heading. So, make sure those are in place before you get started! Try using PowerPivot to clean large data sets quickly and easily.
2. Insert pivot table
Believe it or not, we’re already to the point in the process when you can insert a pivot table into your workbook.
To do so, highlight your entire data set (including the column headers), click “Insert” on the ribbon, and then click the “Pivot Table” button.
Excel Pivot Table Tutorial Pdf
3. Choose where to place your pivot table
After clicking that “Pivot Table” button, you’ll be met with a popup that asks where you’d like to place your pivot table. You have two options:
- New Worksheet: Meaning it’ll appear on a separate tab from where your raw data lives
- Existing Worksheet: Meaning it’ll be placed right within that same tab with your raw data
Ultimately, this comes down to personal preference. So, Jason is going to go ahead and choose to put his pivot table in a new worksheet. He selects that option, clicks “OK,” and then his pivot table and another popup will appear.
4. Select what your pivot table should include
This is the part that gets the most confusing for people. In this step, you need to select the data that you want to pull into your pivot table.
So, in Jason’s case, he wants to see the different quarters, the types of beer, and the gallons sold of each during those quarters.
To pull them into the pivot table, Jason will simply drag them into the appropriate spots for rows, columns, and values.
There are obviously exceptions to this rule, but when you’re just getting started, this is a good way to think about each of these things:
- Rows: Will be your time increments, whether that’s quarters (like Jason), months, years, etc.
- Columns: Will be your identifiers—the different ways that you could ID the data. In Jason’s case, that’s the types of beers.
- Values: Will be the metric that you’re measuring. Again, in Jason’s case, it’s gallons of beer sold.
After you’ve dragged those into the appropriate spots, close out of that pop-up and you’ll see your pivot table.
5. Do your analysis
Now, your pivot table will be populated. Before rolling up your sleeves and diving into the analysis, this is a good opportunity to double-check things. Are you seeing all of the elements that you selected? Does anything look off to you?
How To Do A Pivot Chart In Excel For Mac Shortcut
If not, you’re good to go—you’re ready to use your pivot table to identify trends!
For example, Jason can see that he sells far more stouts (which is a darker, heavier beer) in the first and fourth quarters—which makes sense, as those are the colder months. In contrast, he sells way more pilsners (a lighter, crisper beer) in the second and third quarters—which are the warmer months.
Now, he can leverage the information he discovered through his pivot table to better map out his brewing schedule and manage his inventory of craft beer.
Is your thirst not quenched with our beer example? Here's another real-world case study from Microsoft on how to manage household expenses with pivot tables.
How Do I Make A Pivot Chart In Excel For Mac
Ready to get started?
We’ve only started with the basics with this example. There’s so much more you can do to slice your data in different ways and really zone in on different pieces. Check out our advanced pivot table techniques article to find out more!
Eager to learn more? Sign up to our Pivot Tables Course to discover even more tips and tricks that you can use to leverage this powerful tool to your advantage. We promise—once you master them, you’ll be hooked!
Level up your Excel skills
Become a certified Excel ninja with GoSkills bite-sized courses
Start free trial