Skip to content
ArumSoft Learning
KO
    PivotTableExcel

    Why a PivotTable doesn't update (and how to fix it)

    Works in: Excel for Microsoft 365, Excel 2016 and later, Excel for Mac and Excel for the web · Updated

    Quick answerRight-click inside the PivotTable and choose Refresh. To update every PivotTable in the workbook, use Data > Refresh All (Ctrl+Alt+F5 on Windows).

    Causes and fixes

    1. The PivotTable hasn't been refreshed
    2. New rows are outside the source range
    3. It should refresh by itself when the file opens
    4. Deleted items still show in the filter list
    5. Amounts show as Count instead of Sum

    1The PivotTable hasn't been refreshed

    A PivotTable works from a saved copy of the data. Changing the source cells doesn't change it until you refresh.

    Fix

    Right-click the PivotTable and choose Refresh, or press Alt+F5 on Windows.

    2New rows are outside the source range

    If the PivotTable was made from A1:D200 and you add rows 201 and below, refreshing still reads only A1:D200.

    How to recognise it

    Click the PivotTable, then PivotTable Analyze > Change Data Source. The range ends before your last row.

    Fix

    Turn the source into an Excel table: click in the data and press Ctrl+T. Then use Change Data Source and type the table name, such as Table1. From then on, new rows are included when you refresh.

    3It should refresh by itself when the file opens

    Fix

    Right-click the PivotTable, choose PivotTable Options, open the Data tab and tick Refresh data when opening the file.

    4Deleted items still show in the filter list

    A PivotTable remembers old values, such as a product you've renamed, and keeps them in its filter lists.

    Fix

    In PivotTable Options > Data, set Number of items to retain per field to None, click OK and refresh.

    5Amounts show as Count instead of Sum

    If any amount in the column is text or blank, Excel puts the field in as Count of instead of Sum of.

    Fix

    Convert text numbers with Convert to Number, then right-click a value and choose Summarize Values By > Sum.

    Still stuck?

    • PivotTables built from the same data usually share one copy of it, so refreshing one updates the others too.
    • If the source is in another workbook, that file must be available when you refresh.
    • Column widths jump on every refresh? Untick Autofit column widths on update in PivotTable Options > Layout & Format.