ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software»
  • Office Software Suites»
  • Microsoft Office

How to Make a Pivot Table in Excel

Updated on November 29, 2012
Example of a Pivot Table with all of the months included
Example of a Pivot Table with all of the months included | Source

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.

Sales by Customer Data Table
Sales by Customer Data Table | Source

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.

Click thumbnail to view full-size
Example of how to handle "Spacer" columnHighlight data that you want to include in your Pivot Table
Example of how to handle "Spacer" column
Example of how to handle "Spacer" column | Source
Highlight data that you want to include in your Pivot Table
Highlight data that you want to include in your Pivot Table | Source

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.

Click thumbnail to view full-size
Insert Pivot Table ButtonPivot Table Popup
Insert Pivot Table Button
Insert Pivot Table Button | Source
Pivot Table Popup
Pivot Table Popup | Source

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.

Click thumbnail to view full-size
Pivot Table Control Panel to the rightBuild your Pivot TablePivot Table Formatting Window
Pivot Table Control Panel to the right
Pivot Table Control Panel to the right | Source
Build your Pivot Table
Build your Pivot Table | Source
Pivot Table Formatting Window
Pivot Table Formatting Window | Source

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.

Update the Data Table
Update the Data Table | Source

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.

Have you ever made a pivot table before?

See results

Comments

    0 of 8192 characters used
    Post Comment

    • ercramer36 profile image
      Author

      Eric Cramer 4 years ago from Chicagoland

      Lol I know what you mean with the apprenticeship time crunch.

    • watergeek profile image

      watergeek 4 years ago

      I can't say I understood this, but one of my staff made a pivot table to use in a contract report template once, so I know they're useful. One of these days, when time has taken me far enough from the apprenticeship program that I can relax a little, I'll go through this again and figure it out (lol).