Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Refreshing PivotTable Data.

Refreshing PivotTable Data

by Allen Wyatt
(last updated February 6, 2021)

1

Excel PivotTables provide a powerful tool you can use to analyze your data, as you have learned in other ExcelTips. Whenever you change the information in your source data table, you will need to update the PivotTable. There is no need to recreate the table, instead you simply select a cell in the PivotTable and then select the Refresh tool in the Queries & Connections group on the Data tab of the Ribbon, or click Refresh in the Data group on the PivotTable Analyze tab of the ribbon. If you are using an older version of Excel, select a cell in the PivotTable and then select Refresh Data from the Data menu, or click on the Refresh Data tool on the PivotTable toolbar.

Updating changes is simple enough, but there is probably an additional step you will want to take if you have added records to your data table. If you have added information at the end of the data table, either manually or using a data form, you will want to redefine the data range used to create the PivotTable.

To do this, select a cell in the PivotTable and click the Change Data Source tool in the Data group and finally choose Change Data Source. (If you are using an older version of Excel, display the Options tab of the ribbon to find the Change Data Source tool.) Excel switches to the worksheet containing your data table and displays the Change PivotTable Data Source dialog box. (See Figure 1.)

Figure 1. The Change PivotTable Data Source dialog box.

Make sure the cell range in the Table/Range field reflects accurately the range you want included in the PivotTable.

You should note that if you are adding rows in the middle of the PivotTable's data range, or if you delete rows, you do not need to be concerned about the cell range reflected in the Change PivotTable Data Source dialog box. Excel will make sure it is adjusted correctly. (You only need to be concerned when you add rows or columns to the end of the cell range.)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10371) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Refreshing PivotTable Data.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Working with E-mailed Documents

Ding! You've got mail. That mail has a Word document attached to it. Before you rush off and open that document, take a ...

Discover More

Running a Macro in a Number of Workbooks

Got a macro that you need to run on each of a number of workbooks? Excel provides a number of ways to go about this task, ...

Discover More

Determining the Length of a Text File

When processing plain text files in a macro, it is often helpful to know how much data the file contains. The normal way ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Error in Linked PivotTable Value

Excel allows you to link to values in other workbooks, even if those values are in PivotTables. However, Excel may ...

Discover More

Pointing PivotTables to Different Data

Changing the data source PivotTables go to can be a bit tricky. This tip explains what can happen when you re-point your ...

Discover More

Editing PivotTables without Underlying Data

If you ever try to edit a PivotTable and get an error that tells you that the "underlying data was not included," it can ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two less than 8?

2021-02-08 03:48:23

Peter

After extending my ranges for pivottables for years, I have switched to changing the source range of my pivots to a table (Insert > Table). The table automatically extends when adding records to the end, so I only have to refresh my pivottable.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.