What Is Update Pivot Table In Google Sheets?
Pivot Table Update in Google Sheets is a manual method to refresh the generated Pivot Table data to reflect the changes made to the linked source dataset if the table doesn’t update automatically. The updates can be any kind of modifications, such as, new data additions or deletions in the source dataset. Users can use the Update Pivot Table In Google Sheets feature for an auto data range for single or multiple Pivot Table Update in Google Sheets, simultaneously.
For example, the following image shows a source dataset in the cell range A1:C16 and a Google Sheets Pivot Table in the cell range E1:I8 based on the source dataset. The source dataset contains students’ Physics, Chemistry, and Biology test scores.
Let us change the Chemistry test score of a student, Jean, from 90 to 92 in the source data. The requirement is to update the new value in the Pivot Table when the table doesn’t get automatically updated. Therefore, when we manually refresh the Pivot Table, it gets updated as shown below.
The modification in the source table will change the required Chemistry test score from 90 to 92 in the Pivot Table and the final total changed from 1353 to 1355.
Table of Contents
- What Is Update Pivot Table In Google Sheets?
- How To Update Pivot Table In Google Sheets?
- Update Pivot Table If Any Addition To The Existing Data Set
- Auto Data Range For Pivot Table With Google Sheets Tables
- Update Multiple Pivot Table Results Using Shortcut Keys
- Important Things To Note
- Frequently Asked Questions (FAQs)
- Download Template
- Recommended Articles
Key Takeaways
- The Pivot Table Update in Google Sheets method helps us manually refresh a Pivot Table based on the changes made to the source dataset. The changes can be value updates and data additions or deletions.
- Users can update the Pivot Table to ensure they work with the latest one without unrefreshed data.
- We can update the Pivot Table for auto data range update of a Pivot Table based on changes in the source Google Sheets Table and to refresh multiple Pivot Tables in one go.
- Google Sheets offers an option in the PivotTable Options window to update the Pivot Tables automatically, but only when we open the specific Google Sheets file.
How To Update Pivot Table In Google Sheets?
Since Google Sheets is an online application, it will 100% automatically update the Pivot Table the moment any modifications are made to the linked or the source dataset option. However, due to network issues or any other faulty scenarios, when the automatic update does not work, we use the Refresh or the “Reload the page” icon on the web-browser beside the URL, as shown in the image above. We must click the same to refresh or to Update Pivot Table in Google Sheets.
Example #1 – Update Pivot Table In Google Sheets
We will see an example of the Pivot Table Update in Google Sheets for the following dataset listing fruits, grades and order quantities.
The steps to create a Pivot Table based on the source dataset are as follows:
Step 1: Select the cell range A1:C13 – select the “Insert” tab – click the “Pivot table” option.
Step 2: The Create pivot table window appears, displaying the chosen data range in the Data range field. Select “Existing sheet” and provide cell A17 as the location to generate the Pivot table.
Clicking Create in the Create pivot table window will generate the Pivot table, as shown below.
Step 3: In the “Pivot table editor” pane, select the rows as “Fruit”, Columns as “Grade” and Values as “Order Quantity” to create the required Pivot Table in the target cell, as shown below.
Step 4: Let us now modify the data from cells C6:C9, as shown below.
The expectation is that the Pivot Table update automatically reflects the changed values. However, we must manually update the source dataset changes in the Pivot Table.
Step 5: Therefore, click the REFRESH or the “Reload this page” icon on the web-browser, beside the URL, as shown in the image below.
Once we select the Refresh option, the new values, highlighted in the source dataset, get updated in the Pivot Table.
Update Pivot Table If Any Addition To The Existing Data Set
Sometimes, we create a Pivot Table based on the given source dataset. But, due to changes in the requirements, we may append new data to the existing source dataset.
The steps to apply Update Pivot Table If Any Addition To The Existing Data Set to show the newly added data are,
- Ensure the newly added data to the existing source dataset is correct and valid.
- Click the “Edit” option on the generated Pivot Table, as shown below.
- The “Pivot table editor” pane appears on the right, then, change the data source range.
Example #2 – Update Pivot Table If Any Addition To The Existing Data Set
The following image shows a source dataset in the range A1:C11 and the Pivot Table in the range E1:L13 based on the source dataset.
The source dataset lists ten US cities, their states and population statistics.
Assume we add five more US cities and their corresponding data to the existing source dataset in the range A12:C16.
So, the aim is to update the Pivot Table to ensure it shows the data newly added to the source dataset. Then, the Refresh option will not help in the Pivot Table update data source range changes.
The steps to update the data source range and get the desired output are as follows:
Step 1: Click the “Edit” option on the generated Pivot Table, as shown below.
The “Pivot table editor” pane appears on the right with the initial data source range, A1:C11.
Step 2: Click the “Select data range” option and the “Select a data range” window appears, as shown below.
Step 3: In the above window, either manually edit the cell range as A1:C16 or select the cell range along with the newly added data and click “OK”, as shown below.
Finally, we get the following output, i.e., the updated pivot table with the newly added data, as shown below.
Please note that the Pivot Table created using the initial source dataset lists the US cities in alphabetic order. Notice that the newly added data too is sorted in the Pivot Table.
Auto Data Range For Pivot Table With Google Sheets Tables
We can create a Pivot Table based on a dataset. But when we edit or add data to the source Table, the Pivot Table does not update automatically to accommodate the changes. So, let us see how to create an Auto Data Range For Pivot Table With Google Sheets Tables.
In such scenarios, we can manually update the Pivot Table using the following steps:
- One way is by using the method we learnt in the previous section i.e., to change or update the new data range in the “Pivot table editor” pane.
- Another way is by adding the data in-between the dataset and not at the end. Let us understand this method with the following example.
Example #3 – Auto Data Range For Pivot Table With Google Sheets Tables
The data given below consists of 5 employee’s data, their respective teams and the sales generated.
The aim is to add details of five more employees to the Table and update the Pivot Table to show the newly added data. Then, the steps are as follows:
Step 1: Add the new data as shown below and manually refresh the Pivot table.
The Pivot table remains untouched as shown in the above image.
Step 2: Let us now insert the new data in-between the cells’
First, right-click cell A6 and click the “Insert 1 row above” option. A row gets added, the Pivot Table adds a row and the cell range also gets updated from A1:C6 to A1:C7.
Step 3: Now, repeat the “Insert 1 row above” step 4 more times to get the following output.
Step 4: Let us add the new data. The final updated Pivot table, with the newly added data and the new cell range A1:C11, is shown below.
Update Multiple Pivot Table Results Using Shortcut Keys
To Update Multiple Pivot Table Results Using Shortcut Keys results in a workbook simultaneously we can use the shortcut key i.e., the F5 key, i.e., the function key on the keyboard, to manually refresh the Pivot Table. Again, depending on the system’s keyboard or the kind of Laptop model, we must use combination keys, such as, “Alt+F5” or “Fn+F5,” to manually update the Pivot Table.
Example #4 – Update Multiple Pivot Table Results Using Shortcut Keys
The following image shows a source dataset in the range A1:E9 and three Pivot Tables in the same sheet based on the source dataset of a company and their revenue from January to March.
Consider that we edit the revenue values in cells C4, D7, and E6, highlighted in the below image.
Then, we will press the shortcut key “fn+F5” to update the source data changes in all the three Pivot Tables, simultaneously. We get the updated data, as shown below.
Important Things To Note
- Sometimes, the Pivot Table update does not happen automatically. We must manually refresh the Pivot Table or change the data source to update it.
- When we update a Pivot Table to include data newly added to the source dataset, the Pivot Table in Google Sheets displays the new data in a sorted order.
- If the source dataset is modified or added in-between, we do not need to change the data source range for the Pivot Table to refresh it. Instead, we can use the Refresh option to update it.
Frequently Asked Questions (FAQs)
We can create a Pivot Table in Google Sheets as follows:
Choose a dataset – select the “Insert” tab – click the “Pivot table” option, as shown below.
Once the Pivot table gets generated, we can add the data as required.
Yes, Pivot Tables update automatically. Some cases due to network issues or adding new data outside the dataset range, we must manually update it using the Refresh option or the F5 key.
We can update the Pivot Table in Google Sheets using the following methods,
Method #1 – Using The Refresh Icon
1) Open the Google Sheets application.
2) Click the Refresh icon at the top of the window.
The page will reload, updating the unrefreshed data in the Google Sheets, including the Pivot Table.
Method #2 – Using The Editor Option
1) Open the Google Sheets program.
2) Select the sheet where we must refresh the data.
3) The selected Google Sheet will open.
4) Consider entries existing outside the Pivot Table range.
5) Click on a cell for the Editor icon to appear below the table.
6) Click the Edit icon. The Pivot Table Editor window will appear on the right of the screen.
7) Locate the Data Range information in the Pivot Table Editor window.
8) Click the Select Data Range icon on the right end of the data range information.
9) A box appears where we can enter the new data range, which includes the new data.
10) Once we enter the new data range, click OK in the box.
Download Template
This article must help understand Pivot Table Update in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to Pivot Table Update In Google Sheets. Here we update pivot table using existing dataset, table, shortcut key, example, work template. You can learn more from the following articles –
Leave a Reply