Excel Pivot Tables - Updating Data


Advertisements


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.

Updating

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.

Defer Layout Update

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.
Refreshing

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.

  • Click on the PivotTable. PIVOTTABLE TOOLS appear on the Ribbon.

  • Click the ANALYZE tab.

  • Click Change Data Source in the Data group.

Data Group

Select Change Data Source from the dropdown list.

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

Change Data Source

Select the Table or the Range you want to include in the Table/Range Box under Select a Table or Range. Click OK.

Range

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.

Changing

The Existing Connections dialog box appears.

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

  • Click the Browse for More button.

Browse

The Select Data Source window appears.

  • Click on the New Source button.
  • Go through the Data Connection Wizard Steps.
Source Button

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

  • Click on the File name box.
  • Select the workbook file name.
Workbook

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.
Deleting

Select Entire PivotTable from the dropdown list. The entire PivotTable will be selected.

Entire PivotTable

Press the Delete Key. The PivotTable will be deleted.

Delete Key

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.

Select Delete

The entire worksheet along with the PivotTable is deleted.



Advertisements
E-Books Store