The following step-by-step example shows how to update the range for a pivot table in Excel.
Step 1: Enter the Data
First, let’s enter the following data that contains information about points and assists for basketball players on various teams:
Step 2: Convert Range to Table
Next, we will convert the range for the original dataset into a table by highlighting the range A1:C11, then clicking the Insert tab along the top ribbon, then clicking the Table icon:
In the new window that appears, choose A1:C11 as the range for your table and check the box next to My table has headers:
Once you click OK, the original range will automatically be converted into a table:
Step 3: Create the Pivot Table
To create a pivot table, highlight the cell range A1:C11, then click the Insert tab along the top ribbon and then click the PivotTable icon:
In the new window that appears, type Table1 (or whatever your table happens to be called) in the Table/Range box and choose to place the pivot table in cell E1 of the existing worksheet:
Once you click OK, a new PivotTable Fields panel will appear on the right side of the screen.
Drag the Team field to the Rows box, then drag the Points and Assists fields both to the Values box:
The pivot table will automatically be populated with the following values:
Step 4: Update the Pivot Table Range
Now suppose we add one new row to the original table that includes information about a player on the “Celtics” team:
Note that once we press Enter, this new row will automatically be included in the table.
However, the pivot table will not automatically update.
In order to update the values in the pivot table, we must first click on any cell in the pivot table to make it active.
Then, click the PivotTable Analyze tab along the top ribbon, then click the Refresh button in the Data group:
The pivot table will automatically update to include the values for this new player on the “Celtics” team that we added to the original table:
Now each time you modify the data in the original table, you simply need to click the Refresh button to update the values in the pivot table.
The following tutorials explain how to perform other common operations in Excel: