A pivot table is a tool that you can use to summarize data when you have a lot of it in a worksheet. A pivot table can count totals, give an average of the data, or sort data – in addition to other things. In this article, we are going to go in-depth as we learn to create and work with pivot tables.
Structuring Data for a Pivot Table
Although pivot tables can be fairly easy to create, it is important to have the data in your worksheet structured properly before creating a pivot table. Before we learn to create pivot tables in this article, we are going to first learn the correct structure for source data used in a pivot table, as well as why it is important.
Here are the basic rules for structuring your data in preparation for a pivot table.
1. All data should be in a table. In other words, create a table for your data. Do not just have your data listed in a regular worksheet.
2. Your data should start in cell A1.
3. Your columns should all have header field names or titles.
4. The header titles should be unique.
5. You should not have blank cells, but you really should not have blank cells in Column A.
6. Take the time to remove any totals or subtotals in data rows. The pivot table will do the summarizing for you.
You should take the time to commit these rules to memory if you plan on using pivot tables frequently in Excel. These are basic rules, and ones that you will need to follow if you want to create effective pivot tables.
The Role of Rows and Columns in Your Data Source
When you are preparing a data source for pivot tables, you should build your data downward. In other words, you want to keep your important data to rows. It is almost instinct – if it can be instinct in Excel – to use columns to organize our data. For example, if we are listing weekly sales, we create a column for each week. This is actual, useful data! We use rows for products, territories, or sales people. It is a backward way of doing things, and it limits the use of pivot tables as well. Instead, columns should be used to define the type of data. The actual data should be placed in rows so that we can better produce different slices of the data.
Take a look at the table below.
Notice that we have useful data in the columns. The useful data is the weeks in which sales occurred.
Now let's try to create a pivot table from this data.
In the snapshot above, you can see the PivotTable Fields pane on the right. Notice how all the dates are listed.
To the right is our pivot table.
Note in the pivot table that there are columns that are labeled "Sum of…". The week date follows. We were unable to get a sum of the sales for each week because the useful data was listed in a column instead of a row.
Now let's redo our table.
Take a look at how the new table is structured.
The different header fields are shown as columns.
The data records are shown in rows.
Let's repeat that again. Consider these two more rules for pivot tables.
1. The different header fields are shown as columns.
2. The data records are shown in rows.
Our new table contains the same data as the old table. The difference is that it is organized in a way that a pivot table will be an effective way to summarize and analyze the data.
Here is another way to remember how to structure data properly before creating a pivot table.
All values of the same type should be in the same column. This means if you have dates in your table, the dates should all be in one column. If you have people's names, those names should all be in a column.
Let's create a pivot table from the new source data.
You can see that now the pivot table was able to summarize our data for us.
Unpivot Data
In the last section of this article, we looked at a table in which the data was incorrectly structured for a pivot table. The table looked like this:
We then redid the table in another worksheet so that the data was structured correctly. That table looked like this:
We did not have a lot of data to restructure. In fact, since we were only showing you an example, we did not even put all of the data into our new table. We felt like the data we showed you in the new table was enough to show you how it should be structured.
However, creating an entirely new table will not always be possible for you. You may need to restructure the data for a pivot table, but there may be so much data that it will not be humanly possible to just create a new table for it.
In Excel 2013, there was an add-in called Power Query. Once you installed it, you could use it to unpivot data and structure it correctly. Power Query is no longer an add-in for Excel 2016. It is now just known as a query, and it comes as part of Excel.
You will find the query command under the Data tab in the Get & Transform group.
Let's look at our table with the incorrectly structured data again.
We need the dates that appear in columns to appear in rows instead.
To do this, go to the Data tab, then click the New Query dropdown arrow.
We are going to use our worksheet in our workbook, so we will select "From File".
As we just stated, we are going to use a worksheet in our workbook, so we are going to choose "From Workbook" from the menu pictured above.
If you are following along in Excel, you will then see this dialogue box:
Choose the workbook that contains the data you need to restructure, then click the Import button.
We are going to select Book1.
The Navigator window will then open.
Choose either the table or worksheet that contains the data you want to restructure.
We have selected the table that contains our data.
Click the Edit button.
You will then see the Query Editor.
Click the Transform tab in the Query Editor's Ribbon.
It looks like this:
Select the columns that you need to unpivot. These are the columns with useful data that should have been put into rows.
We have selected ours below.
Next, go to the Any Column group under the Transform tab. Click the Unpivot Columns downward arrow, and select Unpivot Columns.
Excel now restructures the data for you, as you can see below.
Let's rename the two new columns.
Now all we need to do is output the table to a new worksheet.
To do this, go to the File tab. Select Close & Load.
The query is loaded onto a new worksheet in your workbook.
Using Recommended Pivot Tables
Recommended Pivot Tables is a feature that was new to Excel 2013. With Recommended Pivot Tables, Excel analyzes the data that you have in your spreadsheet, then suggests possible pivot tables for you to use.
Take a look at our worksheet below:
This worksheet is simply a list of sales, the employees who made the sales, and the weeks in which the sales were made. It also shows the territory for each employee.
To see the recommended pivot tables, click anywhere in the worksheet.
Go to the Insert tab, then click Recommended Pivot Tables in the Tables group.
You will then see the Recommended PivotTables dialogue box.
In the dialogue box, you will see Excel's recommended PivotTables.
As you can see, in our recommended pivot tables, Excel summarizes the data by the price of each item, the total price, and the number of items.
If you want to use one of these suggested pivot tables, simply click on the pivot tables in the column on the left.
We have chosen the total price for each item.
That pivot table is now visible in the right column.
Click the OK button.
The pivot table is then placed into a new sheet, as shown below.
Creating a PivotTable from Scratch
In the last section of this article, we learned how Excel can create pivot tables for us. Now let's learn how to create a pivot table on our own from scratch.
We are going to use the same worksheet that we used when working with Recommended PivotTables. It is pictured below.
In starting to create your own pivot table, you do not need to select or highlight data. The only way that you would be required to select data is if you had a blank column or row within your data. Of course, you would not want that included in your pivot table, so you would select the data you did want included.
To start creating your pivot table, click within the data, then go to the Insert tab. Click the PivotTable button.
You will then see the Create PivotTable dialogue box.
In the Select a Table or Range field, Excel fills in the range of cells that contains your data.
You can compare the range of cells listed above with our worksheet, and you can see that all the data was included. Unless you need to change the range of cells, you do not need to enter anything into this field.
Next, decide if you want the new pivot table to appear in the existing worksheet (with your data) or in a new worksheet. It is recommended that you place it into a different worksheet, but the choice is ultimately yours. If you place it in the existing worksheet, you will need to specify the location where you want to place it.
Click OK.
This is what you will see in a new worksheet:
This is where your pivot table will start.
If you look to the far right side of the Excel window, you will see PivotTable Fields, as shown below.
You will use this to create your pivot table.
In the top section, you will see your columns listed.
The bottom section contains the sections of your pivot table.
-
Filters is the filter above the pivot table.
-
Columns are the column headings.
-
Rows are row headings.
-
Values are the crossover of the rows and columns.
The only thing in the bottom section that you need to make a pivot table work is Values. You will find that Rows, Columns, and Filters help to organize the data and information in the pivot table.
To see what we mean, let's choose a column from the top half.
We are going to choose Employee. We want each employee to appear in a row, so we drag it to the Rows section in the bottom half.
Next, we are going to click on Sales in the top half, and drag it to the Values section.
We chose Sum of Sales.
We are going to drag Week to the Columns section.
Now, if we look at our pivot table, we see that Excel has summarized the number of sales in our worksheet.
With pivot tables, there is something you need to keep in mind. If you drop a text field into values, Excel will assume you want to count the values. We did this, and it counted the number of occurrences of the item in our data. If you drop a numerical field into Values, Excel will assume you want a sum of the items.
If you make a mistake and drag something into Values, Columns, Filters, or Rows that you do not want there, you can simply click and drag it back to the top section. This will remove it.
You can also drag and drop to and from Values, Columns, Rows, and Filters.
You can do these things to create the pivot table that you want.
Changing the Formatting and Formulas of PivotTables
It is easy to create a pivot table in Excel 2016, but that is just where the fun begins. Now that you created a pivot table, it is time to learn how to format it.
Below is our pivot table.
If you wanted to format the data in the pivot table, you could do so by selecting a column or row, then going to the Home tab and applying formatting, such as changing the font type, font size, or font color. Those are very basic Excel skills and easy enough for you to do.
However, if you applied formatting in this manner, if you ever refreshed the data in your pivot table or added rows or columns, the formatting might not be applied.
Instead, go to the panel on the right side of the Excel window where we created the pivot table. In the bottom section of the pivot table, click the downward arrow to the right of the field you want to format.
In the snapshot below, we are going to click the downward arrow to the right of the Employee field.
Click on Field Settings, as shown above, to format the field.
However, in order to show you a better example, we are going to click on the field Sum of Sales in the Values section, then choose Field Settings from the menu.
We then see the Value Field Settings dialogue box.
In this dialogue box, we can change the name of the field in the pivot table by going to the Custom Name field.
Right now, we have the cells in this field formatted so that all numbers display as currency. If we wanted to change that, we would click the Number Format button.
Choose the formatting for our cells, then click OK.
This will return you to the Value Field Settings dialogue box.
Also in the Value Field Settings dialogue box, we can change the function. Right now, we have it at the default, which is Sum. It can be changed to Count, Average, Max, Min, etc. All of these functions relate to the total sales. For example, average of total sales, and so on.
Click OK when you are finished.
You can also add the same field more than once.
This means that, if you wanted, you could change the function for the Sales field to Count. Then, you could drag the Sales field to the Values section again, which would display the Sum of Sales.
Just make sure you change the number format that matches the function. We would not want Currency for Sales function, for example.
Creating Different PivotTables Using the Same Data
You can create as many pivot tables as you need to using the same data from the same worksheet. You can choose to place the pivot tables together, or you can place them in different worksheets.
We are going to create another pivot table by clicking in the data, then going to the Insert tab, then click on the PivotTable button.
As you can see in the dialogue box pictured above, we have chosen to place the new pivot table in the same worksheet as the pivot table that we created earlier in this article. We did this by choosing Existing Worksheet, then clicking on the tab of the worksheet that contained the pivot table. Next, we clicked on the cell where we want the upper left hand corner of the pivot table to be placed.
Click the OK button in the Create PivotTable dialogue box.
You can now see our new pivot table below our existing pivot table.
We can now add columns, rows, and values to our new pivot table by following the steps we learned earlier in this article.
Moving PivotTables
You can move a pivot table to a new location within a worksheet or to a new worksheet entirely.
To move a pivot table, click within the data of the pivot table, then click the Analyze tab under PivotTable Tools in the Ribbon, as pictured below.
Next, click Move PivotTable in the Actions group.
You will then see the Move PivotTable dialogue box.
You can choose to move the pivot table to a new worksheet, or you can click on a cell in the existing worksheet where you want to place the pivot table.
Click the OK button when you are finished.
The pivot table is moved for you.
Deleting PivotTables
To delete a pivot table, click within the data, then go to the Analyze tab under PivotTable Tools in the Ribbon.
Click the Select button, then choose Entire PivotTable.
This selects the PivotTable.
You can then press Delete on your keyboard.
The Report Filter Option
In the snapshot below, we have a simple pivot table.
You can see that we have dragged and dropped the Employee field into the Rows section, and the Sales field into the Values section in order to create the pivot table.
Now we are going to learn what happens when you drag a field to the Filters section.
For this example, we are going to drag the Territory field to the Filters section.
When we do this, we can see the filter is added above our pivot table.
If we click the downward arrow to the right of Territory, we see our filtering options:
We can now filter the data in our pivot table by the territory of the employee.
Of course, we can also add another filter to our pivot table to further refine the data that is summarized
Sorting Data in a PivotTable
Data in a pivot table can be sorted by row or column labels, as well as values.
Whenever you create a pivot table, Excel does the sorting for you. Excel puts row and column labels in the order that they appeared in the original data worksheet.
If you want to sort row or column labels, simply click the dropdown arrow that appears to the right of either Row Labels or Column labels.
You can see Row Labels circled in red below.
We are going to click the downward arrow.
As you can see in the next snapshot, we are now given the ability to sort the labels alphabetically from A to Z, or Z to A.
We are going to click on Sort A to Z.
Now, if you want to sort values in a pivot table, click the downward arrow again.
This time, click on More Sort Options.
You will then see the following dialogue box.
Choose if you want to sort the values from A to Z or Z to A by putting a check beside your choice.
Next, click the downward arrow in the field below your choice, as shown below.
In the snapshot above, you see that we can also sort by Sum of Sales – or our values.
Select the option, then click OK.
Our data is then sorted by values.
Refreshing the Data in a PivotTable
A pivot table is based on data that is contained in a worksheet. If you change the data in the worksheet after you have created a pivot table, you will need to refresh the data in the pivot table so that it reflects the current data in the original worksheet.
Let's show you what we mean so that it makes sense.
Below is our pivot table.
Now, we are going to go back to our original worksheet.
Let's say, as an example, that there has been a sales increase for the employee named Smith. It is currently showing that he made 21 sales on the week of January 27th, but we are now going to make it show that he actually had 31 sales. To change that data, all we have to do is click in the cell, then make the change.
However, when we go back to our pivot table, our Sum of Sales column still reflects the 21 sales we originally said we made.
We need to refresh the data so the changes made in the original data are reflected in our pivot table.
To do this, go to the Analyze tab under PivotTable Tools. Go to the Refresh dropdown menu, and select Refresh All.
As you can see, the data in our pivot table is now refreshed.
Another thing you can do to make sure that your data stays refreshed is to set your options in Excel so that the data in your pivot table is refreshed each time you open the workbook.
To do this, go to the Analyze tab again.
Click the Options button in the PivotTable group.
You will then see this dialogue box:
Click the Data tab.
Put a checkmark beside Refresh Data When Opening the File, then click OK.
Verifying the Data in a PivotTable
It is easy to take for granted that the data presented in a pivot table is correct.
However, if you ever wanted to double check to make sure that the data is correct, there is an easy way to do that.
Let's say we want to verify that the employee named Smith really made 31 sales.
To do this, double click on the cell that contains the value that you want to double check. In this case, it is the cell we have selected above.
When you double click on the cell, Excel pulls up all the data that it used to come up with this number.
In our case, it is the row entry for Smith and his territory. However, if the value you clicked on used multiple bits of data, it would list all the data that it used to produce the value.
The data shown above is displayed in a new worksheet. You can choose whether you want to keep the worksheet or delete it.
