To create a Pivot Table, perform the following steps:
- Click on a cell that is part of your data set.
- Select Insert (tab) -> Tables (group) -> PivotTable.
- In the Create PivotTable dialog box, notice that the selected range is hard-coded to a set number of rows and columns.
The potential problem is that if we add new rows of data to the table, the Pivot Table will not see beyond the originally defined row number; in this case, row 108.
Making the Table Dynamic
To give the Pivot Table the ability to “see” new rows of data, we need to convert the original table to a proper Excel Table. By doing this, the Pivot Table will look at the field (column) for data, not a set number of rows.
Cancel the Create PivotTable dialog box and let’s back up a couple of steps.
- (UPDATED) – Convert the table to an Excel Table by pressing CTRL-T and click OK in the Create Table dialog box.
- (UPDATED) – Give the table a proper name by selecting Table Design (tab) -> Properties (group) -> and edit the default name (most likely “Table1” to something more meaningful, like “TSales”.
NOTE: This last step is optional, but it will help you keep track of tables more easily.
Now let’s get back to creating the Pivot Table.
- Select Table Design -> Tools (group) -> Summarize With PivotTable.
Notice how our range selection is no longer fixed to a set number of rows and columns. The selection is looking at the table named “TSales”.
- In the Create PivotTable dialog box, click OK.
We are presented with an empty shell of a Pivot Table (left) along with a drag-n-drop interface called the Field List for populating the report (right).
The Field List interface can be customized by selecting the gear button.
You can arrange the drag-n-drop zones as well as sort and search your field names.
The Field List presents a list of the headers from the source data.
NOTE: The cursor MUST be within the bounds of the Pivot Table to display the associated Ribbon features. You may also hide/display the Field List by selecting PivotTable Analyze -> Show -> Field List.
To begin answering our questions about customers:
- Click and hold the Company Name entry in the Field List and drag it inside the Rows
- Click and hold the Sales USD entry in the Field List and drag it inside the Values
We are presented with the following report.
The default aggregation method is to SUM the sales. If you would rather COUNT or AVERAGE (among other things), right-click on any sales value in the report and select “Summarize Values by…” and click the desired aggregation method.