The wizard who dreamt up Pivot Tables deserves a place in the hallowed halls of Hogwarts. Seriously, learning how to create a pivot table in Excel is something that everyone in a business setting should be required to learn. Also — and here’s the big secret — it’s actually not all that hard. If you read this short article and follow along, you’ll be able to make a Pivot Table.
There’s a caveat, of course. You won’t become a Pivot Table master by reading this short article. Rather, you’ll know the basics and can go off into the world to impress folks around your office. Pro Tip: use this article to learn the basics.
How to Create a Pivot Table
Start by downloading this data, then follow along. Typically, you’ll work with a lot more data than what is shown in this sample worksheet, but let’s not start by getting overwhelming. Starting with a smaller-than-usual data set allows us to wrap our heads around what’s going on, then scale up as needed.
First, you need to select the data you want to work with. You can click and drag to select the data, but I’ll use this opportunity to teach you a shortcut. Windows users, click anywhere in the table and press the CTRL and A buttons at the same time. Mac users, press COMMAND and A at the same time. Picking up these useful keyboard shortcuts as you learn Excel is crucial.
With your data selected, click on the Insert tab on the ribbon, and then click Pivot Table.
The dialogue box that appears will ask you to select your data, which you’ve already done, and where you want your Pivot Table. It’s cleanest to select new worksheet, so pick that one.
Now the fun begins. On the right side of your screen, you’ll now see the PivotTableFields. You’ll use this to summarize your data and build your PivotTable.
Let’s go field by field. We’re going to build this PivotTable from the ground up, thinking about what we want to learn from the data. Let’s imagine that we want to learn more about in store vs. online sales over time.
- Values: Well, we definitely want to learn more about the revenues, so let’s drag the Revenue field to the Values box. After you drag it, Excel automatically detects that you want to sum. However, if it doesn’t, click on the i icon to change how it summarizes the data. Note that the Pivot Table sums all of the revenues right away.
- Rows: Drag the Year field to the Rows box. Note that the revenue is now summarized by year in the rows of the Pivot Table.
- Columns: Drag the Order method type field to the Columns box. Note that the revenue is now categorized even further, still by the years on the side and by the order method type in the columns across the top.
- Filters: Drag the Product Type field to the Filters box. This will allow you to dive into the data as necessary.
Next, let’s do a little cleanup. Highlight cells B6:D8 and then change the formatting to Accounting (denoted by a $ sign in the ribbon). Hide the two decimal points, and now your data is beautifully formatted.
Here’s a screenshot of the finished product. Does yours match?
Need a little more help? Check out the finished workbook here.
Now that your Pivot Table is complete, take some time to interact with it to get some insight into your data. Filter it by different products. Move some of your value fields around. Although it is important to follow some step-by-step guides when you start learning, it is much more important to try (and fail!) on your own.
Now that you’ve had a prescribed way to approach these articles, play around and try it yourself. What else might you want to summarize by? I also highly recommend getting your own data to try in a PivotTable. What can you come up with?
Go forth, young wizard, and begin your Pivot Table training! Can’t get the Pivot Table spell? Let me know in the comments, and we’ll work together on it. Don’t forget, Magoosh offers Master Excel: Beginner to Advanced, a great series of video lessons that can help you improve your Excel skills.