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 earper, 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 cpck 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.

Cpck on the PivotTable.

Cpck the ANALYZE tab on the Ribbon.

Cpck Refresh in the Data group.

There are different options to refresh the data in the dropdown pst −

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.

Cpck on the PivotTable. PIVOTTABLE TOOLS appear on the Ribbon.

Cpck the ANALYZE tab.

Cpck Change Data Source in the Data group.

Select Change Data Source from the dropdown pst.

Change PivotTable Data Source dialog box appears and the current Data Source will be highpghted.

Select the Table or the Range you want to include in the Table/Range Box under Select a Table or Range. Cpck 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.

Cpck on the PivotTable.

Cpck the ANALYZE tab on the Ribbon.

Cpck Change Data Source in the Data group. The Change PivotTable Data Source dialog box appears.

Cpck the Choose Connection button.

The Existing Connections dialog box appears.

Select All Connections in the Show box. All the Connections in your Workbook will be displayed.

Cpck the Browse for More button.

The Select Data Source window appears.

Cpck on the New Source button.

Go through the Data Connection Wizard Steps.

If your data source is in another Excel workbook, do the following −

Cpck on the File name box.

Select the workbook file name.

Deleting a PivotTable

You can delete a PivotTable as follows −

Cpck on the PivotTable.

Cpck the ANALYZE tab on the Ribbon.

Cpck Select in the Actions group.

Select Entire PivotTable from the dropdown pst. 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-cpck on the worksheet tab and select Delete from the dropdown pst.

The entire worksheet along with the PivotTable is deleted.