Excel's PivotTable feature is an incredibly powerful tool that makes it easy to tabulate and summarize data in your spreadsheets, particularly if your data changes a lot. If you are finding yourself writing lots of formulas to summarize data in Excel (using functions such as SUMIF and COUNTIF) then PivotTables can save you a lot of time and work and give you insights into your data that are otherwise too hard to discover. Not only that, but they also allow you to quickly change how your data is summarized with almost no effort at all. This lesson will show you how to create a simple PivotTable in Excel to summarize a set of daily sales data for a team of several sales people.
This lesson shows you how to create a PivotTable in Excel 2016 (both Mac and Windows versions), Excel 2013, Excel 2010 and Excel 2011 (Mac).
What you'll learn in this lesson
In this tutorial, you'll learn how to:
Set up your data in Excel so it is in a format that you can use for a PivotTable.
Create a PivotTable with that data
Change the PivotTable report to reflect different views on the same data.
The data we'll work with in this example is an Excel table that has two months of daily sales data for a team of four sales people, broken down by product. The first few rows are shown below:
In fact, this spreadsheet extends down for 688 rows of sales data, for all of January and February (you can download a copy of the spreadsheet here). So while you might look at the data in the table above and think "I could summarize that quickly by hand or with a few clever formulas", the likelihood is that it would all get too much - and would certainly take too long to do by hand. That's where PivotTables are by far the best solution - you'll be able to convert this data in under a minute, and be able to get different summaries with a few clicks of the mouse.
Getting started with PivotTables - make sure your data is ready
There are some important rules you need to follow if you want to create a PivotTable from your data:
Your data should be organized in columns with headings. These headings will be used when you create the PivotTable, and things will get very confusing without headings.
Make sure there are no empty columns or rows in your data. Excel is good at sensing the start and end of a data table by looking for empty rows and columns. If it finds an empty row or column, it assumes your data stops at that point.
A quick way to check if your data is ready to be used in a PivotTableis to click a single cell anywhere in the data table, then press SHIFT+* (or CTRL+SHIFT+8). This automatically selects the whole table. If any data remains unselected, you need to check for empty columns or rows within the data table.
Note that empty cells within your table are OK. What isn't OK is a whole row or a whole column of empty cells.
Consistent data in all cells.
If you have a date column, make sure all the values in that column are dates (or blank).
If you have a quantity column, make sure all the values are numbers (or blank) and not words.
At this point, if everything is looking OK, you're ready to move on to the next step.
Create a blank PivotTable
To start your PivotTable, follow these steps:
Click on a cell in the data table. Any cell will do, provided your data meets the rules outlined above. In fact, at this point it's all or nothing - select the whole table or just one cell in the table. Don't select a few cells, because Excel may think you are trying to create a PivotTable from just those cells.
Click on the Insert menu and click the PivotTable button:
This button looks like this in Excel 2007 and 2010 for Windows:
The button looks like this in Excel 2013 and 2016 (both Windows and Mac versions of Excel 2016). Note the new feature to see Recommended PivotTables. This is the fastest way to create a PivotTable. However, we'll choose the PivotTable button for now so you can learn how to build a PivotTable from scratch:
If you're using Excel 2011 for Mac, you can choose PivotTable... from the Data menu (although we think Excel 2016 for Mac is well worth the upgrade. It's faster, and is almost identical to Excel 2016 for Windows).
The following dialog box will appear. This is the same on all versions of Excel from Excel 2007 right through to Excel 2016:
Note that the Table/Range value will automatically reflect the data in your table (you can click in the field to change the Table/Range value if Excel guessed wrong). Alternatively, you can choose an external data source such as a database (we'll cover that another day!)
Also notice that you can choose where the new PivotTable should go. By default, Excel will suggest a New Worksheet, which I think is the best choice unless you already know you want it on an existing worksheet.
Be warned that if your data changes a lot, or you find yourself changing the PivotTable layout, then refreshing the data in your PivotTable can result in the PivotTable changing shape and covering a larger area. If you have data or formulas in that area, they'll disappear.
Therefore, putting a PivotTable on the same page as your data or other information can cause you real headaches later on, which is why I recommend New Worksheet as the preferred option.
Once you've completed your selections, click OK. Assuming you chose the New Worksheet option, Excel will create a new worksheet in the current workbook, and place the blank PivotTable in the worksheet for you. You are now ready to design your PivotTable.
Designing your PivotTable layout.
When you switch to the worksheet with your new PivotTable, you'll notice three separate elements of the PivotTable on the screen, starting with the PivotTable report itself. The presentation of this screen will be different if you are using Excel 2007 or Excel 2011 for Mac, but this shouldn't make any difference to the next steps in this lesson.
Then you'll see the PivotTable Field List (or PivotTable Builder on Excel for Mac) and under that the field layout area (I've shown them side by side here).
Note that it should show the column headings from your data table. If not, you may need to check that your whole data table was selected.
Here's a quick tip - if you click on any cell in your spreadsheet that is outside the PivotTable, the PivotTable Field List will disappear. You can make it reappear simply by clicking inside the PivotTable report again.
To create the layout of your PivotTable, you first select the fields you want in your table, and then place them in the correct location in the field layout area.
I recommend you drag and drop each field to the area you want it to be.
The alternative is to check the boxes for the fields you want to include, and let Excel guess where to put them. However, Excel sometimes guesses wrong, so manual selection is likely to be faster.
As an example, here are the Field List and the Field Layout area above with the fields in place to show a report with:
Each day down the left, with each sales person listed separately for each day
Items shown across the top.
The total quantity of items sold for each row in the PivotTable.
Here is how to define the layout this report:
The PivotTable report that is generated from these selections looks like this:
At this point, the PivotTable has created a table that shows the number of each product sold by each sales person on each day.
Changing your PivotTable
So far, we've seen how quickly a PivotTable can create a report that would have taken hours to produce by hand. Now, let's try changing the PivotTable to show the report another way, i.e. show Products down the side, and Sales people across the top.
Click inside your PivotTable report (i.e. the report shown above). The PivotTable Field List should reappear.
Change the layout of your PivotTable to look like this:
When you make this change, you'll see your PivotTable change instantly to reflect the new layout (unless you chose Defer Layout Update - this feature is useful if you have a lot of data, and changing the layout of your PivotTable takes a long time to recalculate)
The speed with which you were able to make this change demonstrates the real power of PivotTables - the ability they give you to tabulate data quickly, and then rearrange your data into different reports amost instantaneously.
Hopefully this lesson has got you started with PivotTables. You might also like to try this book by Bill Jelen which provides a comprehensive guide to PivotTables and how to use them.
We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours.