Let's take a moment to look at the data. We have a spreadsheet that shows us a list of stores by city, state, and region. The sales representative and gross sales for each store are also listed. There was no natural unique identifier, so the stores have been numbered from 1 to 60.
When creating a pivot table, we place fields as either a column label or a row label. To help decide whether something should be a column label or row label, think about how you want that item to appear in the pivot table and how you want to summarize the data. With the row and column labels set, add the field to be calculated to the values area.
Remember, how you set up a pivot table isn't set in stone. If you don't get the view of the data you want, simply move labels between the Row, Column, Filter, and Values areas.
We will create a pivot table and use the COUNT function to count the number of stores in each region and state.
The pivot table will look something like this before we modify the layout:
In this pivot table, the Region field is a Column label and the State field is a Row label. The Values are a count of Store ID numbers.
Before creating a pivot table, let's confirm that the active cell is within the data we want to use for the pivot table.
To select a cell in the correct range, in the Sales worksheet,
Click a cell in the range A1:F61
To move to the Insert tab, on the ribbon,
Click the Insert tab
To begin creating the pivot table, on the far left side of the ribbon,
The PivotTable from table or range dialog box opens:
This dialog box shows the range of cells that will be used to create the pivot table, A1:F61, and gives the choice of whether to put the pivot table in a new worksheet or the existing worksheet. We will accept the default of a new worksheet.
To accept the suggested range and pivot table placement, in the dialog box,
A new worksheet opens:
This worksheet gives us space to lay out the fields for our pivot table. We see the layout on the left of the new sheet and the PivotTable Fields list on the far right. Each field (column header) from our table appears in this list preceded by a checkbox. Below the list of fields are the areas: Filters, Columns, Rows, and Values. The pivot table is built by putting fields in these areas.
On the ribbon, we now have new contextual tabs, PivotTable Analyze and Design. These are tabs that are only available when we are performing specific tasks related to analyzing and designing pivot tables.
Before we build the pivot table, let's rename the new worksheet.
To select the current worksheet name,
Double-Click the Sheet1 tab
NOTE: The new worksheet may have a number besides 1.
To rename the worksheet, type:
StoresRegionState Enter key
Now we're ready to arrange the fields and create the pivot table.
Adding fields to the pivot table
When creating a pivot table, we can place as many fields from the PivotTable Fields list as we want to any area (Filters, Columns, Rows, Values). Part of the appeal of a pivot table is that it can always be changed. The fields can be rearranged if we decide to modify the layout.
First, we will add the State field as a Row.
To place the State field in the Rows area, in the PivotTable Fields list,
Press & Drag the State field to the Rows area
The pivot table and Field List look like this:
The state abbreviations appear in rows 4 to 21 in column A. The State field is in the Rows area.
Now let's add the Region field to the Columns area.
To place the Region field in the Columns area, in the PivotTable Fields list,
Press & Drag the Region field to the Columns area
The pivot table now has the regions in columns B to E.
Our pivot table now has structure but no actual data. To complete the layout, we must specify a field to be used for calculation. We are trying to count the number of stores in each region and state. The best field to use for counting is one that contains unique values. In this example, the StoreID field is unique to each record.
Let's add StoreID to the Values area.
To place the StoreID field in the Values area, in the PivotTable Fields list,
Press & Drag the StoreID field to the Values area
The pivot table gives us an unexpected result. There are 60 stores in our Sales worksheet. How are there 193 stores in Illinois?
A look at the Values area will explain what happened.
In the Values area, it says Sum of StoreID. The Store IDs are numbers. By default, Excel will add, or sum, numbers that are placed in the Values area. This is not the calculation we want. We are trying to get a count of the Store IDs. This is an easy fix.
Modifying the value field
Because the StoreIDs are unique to each store, they provide us with a way to count the number of stores in each region and state. We will modify the Sum of StoreID field to reflect a count rather than a sum.
To open the Value Field Settings dialog box, in the Values area,
Click the Sum of StoreID field drop-down, Click Value Field Settings...
To open the PivotTable Field dialog box, in the Values area,
Right-Click Sum of StoreID, Click Field Settings
In Microsoft Excel for Windows, the Value Field Settings dialog box opens:
In Microsoft Excel for macOS, the PivotTable Field dialog box opens:
In both Excel for Windows and Excel for macOS, this dialog box provides several ways to modify the value field. We can give the field a custom name, change the type of calculation performed on the field, adjust the way the value is displayed, and format the results. We'll change the calculation and name.
To change the calculation, in the "Summarize value field by" area,
To select the current name of the value field, in the Custom Name area,
Press & Drag Count of StoreID
To remove the current name, on the keyboard, press:
To rename the value field, type:
Number of Stores
To accept the change and close the dialog box,
The pivot table currently looks like this:
Pivoting the pivot table
While the information in the pivot table is accurate, it isn't easy to read or understand. For example, we can see that there are three stores in Oregon and fourteen stores in the Pacific region but it takes time and concentration. By moving the State and Region fields to different areas of the pivot table, we can change the focus and readability of the pivot table.
To move the State field to the Columns area,
Press & Drag the State field from the Rows area to the Columns area
With the State field under the Region field, the pivot table is even more confusing. Let's move the Region field to the Rows area.
To move the Region field to the Rows area,
Press & Drag the Region field from the Columns area to the Rows area
This version of the pivot table is easier to read than the previous one, but it can still be improved. Let's move the State field under the Region field in the Rows area.
To move the State field to the Rows area,
Press & Drag the State field from the Columns area to the Rows area
This pivot table is much easier to understand, as shown in the following image:
It is now clear which states are in which regions and how many stores are in each.
Changing the pivot table layout
The pivot table is currently in the default layout, Compact. In this layout, the row fields are all in the same column with the states indented underneath the regions. Let's change the layout to Tabular.
To move to the Design tab, on the ribbon,
Click the Design tab
To change the report layout, on the left side of the ribbon,
Click , Click Show in Tabular Form
The pivot table changes slightly.
The Region and State fields are both still in the rows of the pivot table. However, the states have been moved a column to the right and are no longer directly underneath the regions. Both the Region and State fields have their own column headers.