Creating a pivot table

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.

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. 

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.

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. 

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.

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.

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.

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. 

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. 

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. 

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.

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. 

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. 

Before moving on, let's save the workbook.