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 July 2, 2020)


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


Nudging a Graphic

Want to get a graphic to just the right position on a worksheet? Sometimes the easiest way is to use the arrow keys on ...

Discover More

Calculating an Age On a Given Date

Start putting dates in a worksheet (especially birthdates), and sooner or later you will need to calculate an age based ...

Discover More

Printing Close to the Edge

Word allows you to specify all sorts of paper sizes and margins for your documents. If your margins result in trying to ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Counting Ports of Call with a PivotTable

A PivotTable is a great way to aggregate and analyze data. Sometimes, though, it can be difficult to figure out how to ...

Discover More

Weighted Averages in a PivotTable

PivotTables are used to boil down huge data sets into something you can more easily understand. They are very good simple ...

Discover More

Maintaining Formatting when Refreshing PivotTables

When you refresh the data in a PivotTable, Excel can play havoc with whatever formatting you applied. Here's how to ...

Discover More

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

View most recent newsletter.


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}] 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 three minus 1?

2015-10-07 11:19:16

freddy lemmens

To mr. Barouh (and others) Working with data as a table has 9 advantages compared to working with columns. Not only in pivots! I wrote a book about this in Dutch (my mother tongue). It is not only about speed but also database consistency, conditional formatting, filters and sorting. I work as a freelance Excel Expert consultant/trainer and it is my experience that this is the way to go. Also with the new Excel Power B.I. tools, tables work 100% and easy to use. I asked Microsoft and - for what I do - they recommend Access. I can translate the 9 advantage into English if you like. Glad to help!

2015-10-07 07:23:20


to freddy lemmens:
I never noticed any slow down due to inclusion of large sets of enpty cells into analyzed data ranges. Does your statement that the number of empty cells in the range makes difference is based on some testing or may be on recommendations from Microsoft? Or this is just general assumption?

2015-10-07 05:18:59

freddy lemmens

I do not agree with mr. Barouh. Selecting the colums A:J selects 10 columns x 1M rows = 10M cells. This slows down and takes a lot of Excel memory. This is not the way to do it. Please consult YouTube on how-to use tables in Excel. The problem that mr. Lauer has, will also be solved using tables, not columns.

2015-10-06 13:46:22

Tom Lauer

I added some additional data to my source, and my range was still large enough to find it. However, upon refreshing, the data does not appear as new column as expected. Why is that? I assigned a number for sorting purposes, and can see the number showing up in the drop down list. I have it checked, but the column won't show up. Any ideas?

2015-08-24 02:10:38


I noticed that in some cases it's enough to update one Pivot table in the file to get all other Pivot tables be updated as well. And sometimes update includes only this Pivot table

What defines this behavior? The difference is if the pivot table was created from the scratch or was copypasted from another one?

2015-08-24 02:06:56


and even if you prefer to deal with data ranges, not tables, it's highly recomended to define the cell range for Pivot table as set of columns - e.g. $A:$J instead of $A1:$J8000. Then new rows will be added to pivot table automatically (of course this approach requires that column headers are in row 1 and occupies only one row)

2015-08-23 06:52:23

Ricardo Ribas

Mr. Lemmens is right. Excel 2003 introduced the "List" command which transformed the data range to a table.
The Pivot Tables read the LIST without regard to the number of rows it contained. Is is always the LIST.
The next versions of Excel renamed this as TABLE and the command "Insert Table" appeared. It was the old List, but with more attributes: it gives a name to the data range (Table1, Table2...), treats each column as a FIELD (for example: you total the "Quantity" not the range E2:E2000, and as you insert more rows, Quantity is bigger automatically), and as said, Pivot Tables link to the "Table", regardless of size.

You can also transform a data range into a table as Mr. Lemmens says.

2015-08-22 08:03:42

freddy lemmens

this is not needed when the datarange was 'formatted as a table' (Home > (Styles) Format as table) before the pivot was created! After adding line to the data, Excel 'sees' the table and not the range $a$1 to $x$x. This is a very nice feature in the latest Excel versions.

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

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.