In this tutorial, we’ll take you through the steps to create a new Pivot Table. It’s generally a good idea to turn the data for your pivot table into a Table first. This will make it easier to add data at a later time and update your pivot table in addition to the many other benefits of tables. You can read more about the benefits of Excel Tables here.
Turn your data into an Excel Table.
- Select any cell in your data range.
- Go to the Insert tab.
- Under the Tables section select Table.
- Make sure your entire range of your data is selected and ensure the My table has headers option is checked since our data has column headings.
- Press the OK button.
Alternatively, you can avoid using the ribbon commands and use the Ctrl + T keyboard shortcut to create a table.
The default name of your new table will be Table1, so it’s a good idea to rename it to something more descriptive.
- Select a cell inside the table.
- A new Table Tools tab called Design will appear in the ribbon. Select the Design tab.
- Under the Properties section type in your new Table Name and press Enter.
Now we can create a Pivot Table based on our data.
- Select any cell in the data.
- Go to the Insert tab in the ribbon.
- Press the Pivot Table button from under the Tables section.
- Your data should be pre-selected and display the name of our table that we named SalesData.
- You can select where you want to create the new pivot table, either in a New Worksheet or you can choose the location in an Existing Worksheet.
- Press the OK button.
You will now have a new blank Pivot Table.
- Place the active cell cursor anywhere in the Blank Pivot Table.
- You will notice two new tabs appear in the ribbon, an Analyze and Design tab.
- You should also see the PivotTable Fields window pane appear to the right.
- If you don’t see the PivotTable Fields you can find it under the Tools section of the Analyze tab > Show > Field List.
I have created a pivot table for the purpose of explaining the different areas in the PivotTable Field window and how they relate to the pivot table in the worksheet. Fields from the Column Heading area can be dragged and dropped into any of the Filter, Columns, Rows or Values sections of the PivotTable Fields window to create your desired Pivot Table, but we will go through this creation process in detail during the next tutorial.
- In the PivotTable Fields window, you will notice the larger scroll box in the top area contains a list of all your Column Headings from the Sales Data.
- The Filters section will allow you to filter out data from your pivot table results so that only data pertaining to the values you select will display. You can have multiple filters and select multiple values within a filter.
- We have added the Sales Representative to the Filters area and in the worksheet we have used it to filter on Sara Snyder‘s sales data.
- The Columns section allows you to view your data summarized across the columns by the field values for any given field in your data.
- We have added the Item field to the Columns section and our pivot table now has the column labels Junk, Stuff, Things and Widgets which are values found in the Item field in our data.
- The Rows section allows you to view your data summarized down the rows by the field values for any given field in your data.
- We have added the Location field to the Rows section and our pivot table now has the row labels Connecticut, Massachusetts, New Jersey and New York which are values found in the Location field in our data.
- The Values section allows you to summarize the numerical fields in your data using the Sum, Count, Average, Variance or Standard deviation aggregating functions.
- We have added the Total field to the Values area and our pivot table now has the sum of the Total field displayed for each Item and Location.