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.
Copying the practice workbook
If you are beginning this course at this point, you will need to copy the practice workbook to your Google Drive.
To begin copying the practice workbook in a new browser window,
The file begins to open in a new window of the web browser. Before the document will open and can be edited, we need to make a copy of it.
Move to the new browser window.
To copy the workbook file, in the browser window,
Click
A copy of the file is now open and, by default, is saved in your Google Drive. The filename now has the words "Copy of" at the beginning.
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.
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, confirm that the active cell is within the data we want to use for the pivot table.
To move to the Sales worksheet, if necessary, at the bottom of the Google window,
Click the Sales worksheet tab
To select a cell in the correct range, in the Sales worksheet,
Click a cell in the range A1:F61
To begin creating the pivot table, on the menu bar,
Click Insert, Click Pivot table
The Create pivot table dialog box opens:
This dialog box shows the range of cells that will be used to create the pivot table, A1:F61 and give 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,
Click
A new worksheet opens:
This worksheet gives us space to place the fields for our pivot table. We see the layout on the left of the new sheet and the Pivot table editor on the right. The areas of the pivot table, Rows, Columns, Values, and Filters are in the editor. Each field (column header) from our table appears in a list on the far right. The pivot table is built by placing fields in the different areas.
Before we build the pivot table, let's rename the worksheet.
To select the current worksheet name, at the bottom of the worksheet,
Double-Click the new worksheet tab
To rename the worksheet, type:
StoresRegionState Enter key
Now we are 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 pivot table editor as we want in any area (Rows, Columns, Values, and Filters). 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. This can be done in a couple of different ways. We can press and drag the field under the Rows area. Or we can use the Add button.
To add the State field, to the right of the Rows heading in the pivot table editor,
Click , Click State
The pivot table and pivot table editor look like this:
The states are listed in column A. In the pivot table editor, the State field is in the Rows area. The State field is in ascending order.
Now let's add the Region field to the Columns area.
To add the Region field to the Columns, in the pivot table editor,
Press & Drag the Region field below the Columns heading
The pivot table now has the regions in columns B to D. The Grand Total will appear in column 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 the StoreID field to the Values area.
To add the StoreID field, to the right of the Values heading,
Click , Click StoreID
The pivot table gives 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.
By default, the StoreID field has been summarized by SUM. Because the StoreIDs are numbers, Google Sheets assumes we want to add them. This isn't the calculation we want. We are trying to count the StoreIDs. 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 way the StoreID field is summarized to reflect a count rather than a sum.
There are many built-in ways to summarize data in a pivot table. These options are in the drop-down menu in the Values area.
To see the Summarize options, in the StoreID field under the Values heading,
Click
To change the way the StoreIDs are summarized, in the drop-down menu,
Click COUNT
The pivot table now shows us how many stores are in each region and state. Before moving on, let's change one of the headings in the pivot table.
To select the value field, in the pivot table,
Click in cell A1
To change the name of the value field, type:
Number of Stores
To widen column A, in the column headers,
Double-Click
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, in the pivot table editor,
Press & Drag the State field from the Rows area to the Columns area, underneath the Region field
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, in the pivot table editor,
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, in the pivot table editor,
Press & Drag the State field from the Columns area to the Rows area, underneath the Region field
Now the pivot table is easier to understand.
It is clear which states are in which regions and how many stores are in each.
, 
, 
