How to Make a Pivot Table in Excel
If you work with large amounts of data and need to be able to summarize, analyze, or produce reports it is critical that you learn how to use pivot tables in Excel. Pivot tables are a powerful tool that allows the user to take a large amount of data and crush it into a more useful format. As an accountant, I use them on a regular basis to manipulate data.
Step by Step Guide on how to Create a Pivot Table
Creating a pivot table in Excel is a relatively easy process once you have done it a time or two. In the picture above, I set up a table that has the sales of a fictional company that I am going to use as an example of how to create a pivot table.
Step 1: Create your Data Table
The first step is to create your data table and populate it. Make sure all of the columns that you want to include in your pivot table have a heading. If you have any spacer columns between your data, that too will need a column heading. I realize that may make your table look bad so I suggest that when you enter the heading that you change the text color to the same color as the background. Column C in the picture to the right is an example of what I am talking about.
Step 2: Highlight the Data to be Used in the Pivot Table
The next step is to highlight all of the data that you want to include in the pivot table. It is okay if the highlighted selection includes items that you do not want. We can weed them out later. Also, if you plan to add more data to your table in the future, it is a good idea to include a few blank rows underneath your data so that you will not have to redo the entire pivot table. When you go to add more data, be sure to insert new rows in the area that was included in the area that you originally highlighted.
Step 3: Insert the Pivot Table
To insert the pivot table, go to Excel’s “Insert” tab and click on the “Pivot Table” button. This will up a popup window. Click OK as long as you do not want to insert the pivot table in the current worksheet. Occasionally I will do that, but most of the time I insert the pivot table on a new tab.
Step 4: Build the Pivot Table
The final step is to build the pivot table how you want it. The order that you select the fields in will dictate how Excel will group your items on the pivot table. Start out by clicking the checkbox next to “State” and the three states that were on the data table will now appear as a “Row Label.” Now, check the checkbox next to “2012 Total.” You will see another column added to your table that lists the total sales for each state. Now we need to edit this by left-clicking on the “Sum of 2012 Total” bar located under the “Values” heading on the right side of the screen. Change the “Custom Name” to “TTL 2012” and then click on the “Number Format” button. Select either “Currency” or “Accounting” from the list and click OK on both popup boxes. This will make the pivot table look much nicer. Feel free to play around and add more categories as you like. I would also right-click on the tab name and change it to “Sales Pivot” or something like that.
This step may take some playing around with depending how Excel reads your original data table. Sometimes, it will treat numbers as text and when they are inserted on the pivot table show up under “Row Labels” rather than “Values.” You can drag and drop the pivot table fields after you have put a check mark next to them into any of the four areas on the bottom of the pivot table control panel. If for instance, you move “State” under the Report Filter box the pivot table will change and give you a single total for 2012. It will also cause a drop down box to be inserted that allows you to change how the pivot table is featured.
How to Update a Pivot Table
There is one more thing that I want to show you in regards of how to update the pivot table. Go back to the “Sales Report” tab and change the state for CDE Company to California. Now go back to the “Sales Pivot” tab and left-click anywhere on the pivot table to bring up the pivot table control panel. Once the panel comes up, right-click anywhere in the table and select “Refresh.” You will see that Illinois disappeared and the sales dollars were added to the total of California.
Creating pivot tables gets easier the more that you work with them. There are so many different things that you can do with them.