How to Refresh a Pivot Table (and Why It Doesn't Update)

To refresh a pivot table, right-click anywhere inside it and choose Refresh. Pivot tables don't update by themselves when the data changes, so a quick refresh is how you pull in edited numbers. If new rows still don't show up, the problem is the source range, not the refresh. This guide covers every way to refresh, how to include new rows, and the settings that stop refreshes from messing up your layout.

Why doesn't a pivot table update by itself?#

A pivot table doesn't read your cells directly. When you build it, Excel saves a copy of the source data called the pivot cache, and the pivot calculates from that copy. Change a number in the source, and the cache still holds the old one until you refresh.

That's why a pivot can show a different total from a SUM of the same column. It is not broken; it is out of date. Formulas such as SUMIF recalculate at once, which is the main difference between the two tools.

The examples use this sales list in A1:C9. The pivot was built when the data ended at row 8, so it shows West at 3,200:

How to refresh a pivot table#

Pick whichever way is quickest for you. They all reread the source range:

Refresh All also updates queries and data connections, so it can take longer in big files. Pivots built from the same source share one cache. Refreshing one of them refreshes them all.

Why new rows don't appear after a refresh#

Refresh rereads the same range the pivot was built on. Hana's row is in row 9, but the pivot's range is A1:C8, so refreshing changes nothing. You need to make the range bigger.

Option 1: Change Data Source#

1. Click inside the pivot.
2. Go to PivotTable Analyze > Change Data Source.
3. Change the range to $A$1:$C$9 (or select the new range) and click OK.

West now shows 5,250: 1,800 + 1,400 + 2,050. You'll have to do this again the next time rows are added.

Option 2: Build the pivot on an Excel table#

This is the lasting fix. Click inside the data and press Ctrl+T (Mac: ⌘T) to make it a table. Then use the table's name, such as Table1, as the pivot's source in Change Data Source. A table grows when you type in the row just below it, so the pivot's source grows too. You still need to refresh, but you never need to fix the range again. The comparison of an Excel table vs a pivot table explains how the two work together.

How to refresh a pivot table when the file opens#

To make sure a pivot is current when someone opens the file:

1. Right-click the pivot and choose PivotTable Options.
2. Go to the Data tab.
3. Tick Refresh data when opening the file and click OK.

Excel has no setting to refresh a normal pivot every time a cell changes. Some people use a short macro for that, but macros need a macro-enabled file and aren't allowed everywhere. If the numbers must always be live, a formula summary is simpler, as shown below.

Stop a refresh from changing your layout#

A few PivotTable Options fix the most annoying side effects of refreshing. Right-click the pivot, choose PivotTable Options, and:

• Column widths jump. On Layout & Format, untick Autofit column widths on update.
• Cell formatting disappears. On Layout & Format, tick Preserve cell formatting on update. For number formats, set them in Value Field Settings > Number Format rather than on the cells.
• Deleted items stay in the filter list. On the Data tab, set Number of items to retain per field to None, then refresh.

Common refresh errors and how to fix them#

Most refresh errors point to a problem with the source data:

• "The PivotTable field name is not valid." A header in the source is blank, or the source range was deleted. Fill in every header and check the range. The full fix is in the field name is not valid error.
• "A PivotTable report cannot overlap another PivotTable report." After the refresh, the pivot would grow into another pivot. Move one of them so there's space between them.
• The source shows #REF!. The sheet or range the pivot points to was deleted. Point Change Data Source at the right range.
• Counts or totals look wrong. You may be counting a column with blanks. See how counting works in a pivot table.

When a formula is better than refreshing#

If a report must always show current numbers, use formulas instead of a pivot. In G2:

=SUMIF(A2:A9,"West",C2:C9)

This returns 5,250 the moment Hana's row is typed, with no refresh and no range to fix. SUMIF works in every version of Excel. Pivots are faster to build and rearrange; formulas are always live. If you're new to pivots, start with the beginner's guide to pivot tables.

Practise it#

Interviewers often add a row to the data and ask why the pivot didn't change. Knowing about refresh and the source range is the answer. Practise pivot tables in a live spreadsheet: build one, change how it summarizes, and check your answers instantly. Then review the pivot table interview questions employers ask most.