Excel Pivot Tables - Updating Data
You have learnt how to summarize data with a PivotTable. The data on which the PivotTable is based might be updated either periodically or on occurrence of an event. Further, you also might require to change the PivotTable Layout for different reports.
In this chapter, you will learn the different ways of updating the Layout and / or refreshing the data in a PivotTable.
Updating PivotTable Layout
You can decide whether your PivotTable is to be updated whenever you make changes to the layout or it is to be updated by a separate trigger.
As you have learnt earlier, in the PivotTable Fields task pane, on the bottom side, you will find a check box for Defer Layout Update. By default, it is unchecked, which means the PivotTable Layout gets updated as soon as you make changes in the PivotTable areas.
Check the option − Defer Layout Update.
The UPDATE button next to it will be enabled. If you make any changes to the PivotTable areas, the changes will be reflected only after you click on the UPDATE button.
Refreshing PivotTable Data
When the data of a PivotTable is changed in its source, the same can be reflected in the PivotTable by refreshing it.
- Click on the PivotTable.
- Click the ANALYZE tab on the Ribbon.
- Click Refresh in the Data group.
There are different options to refresh the data in the dropdown list −
Refresh − To get the latest data from the source connected to the active cell.
Refresh All − To get the latest data by refreshing all sources in the workbook.
Connection Properties − To set the refresh properties for the workbook connections.
Changing the Source Data of a PivotTable
You can change the range of the source data of a PivotTable. For e.g., you can expand the source data to include more number of rows of data.
However, if the source data has been changed substantially, such as having more or fewer columns, consider creating a new PivotTable.
Select Change Data Source from the dropdown list.
Change PivotTable Data Source dialog box appears and the current Data Source will be highlighted.
Select the Table or the Range you want to include in the Table/Range Box under Select a Table or Range. Click OK.
The data source for the PivotTable will be changed to the selected Table/Range of data.
Changing to External Data Source
If you want to change the data source for your PivotTable that is an external one, it might be best to create a new PivotTable. However, if the location of your external data source is changed, for example, your SQL Server database name is the same, but it has been moved to a different server, or your Access database has been moved to another network share, you can change your current data connection to reflect the same.
Click on the PivotTable.
Click the ANALYZE tab on the Ribbon.
Click Change Data Source in the Data group. The Change PivotTable Data Source dialog box appears.
Click the Choose Connection button.
The Existing Connections dialog box appears.
The Select Data Source window appears.
- Click on the New Source button.
- Go through the Data Connection Wizard Steps.
If your data source is in another Excel workbook, do the following −
- Click on the File name box.
- Select the workbook file name.
Deleting a PivotTable
You can delete a PivotTable as follows −
- Click on the PivotTable.
- Click the ANALYZE tab on the Ribbon.
- Click Select in the Actions group.
Select Entire PivotTable from the dropdown list. The entire PivotTable will be selected.
Press the Delete Key. The PivotTable will be deleted.
If the PivotTable is on a separate worksheet, you can also delete the PivotTable by deleting the entire worksheet.
Right-click on the worksheet tab and select Delete from the dropdown list.
The entire worksheet along with the PivotTable is deleted.
