{"id":1169,"date":"2023-10-26T22:53:55","date_gmt":"2023-10-26T14:53:55","guid":{"rendered":"https:\/\/www.excelchamp.net\/?p=1169"},"modified":"2024-01-07T14:23:17","modified_gmt":"2024-01-07T06:23:17","slug":"how-to-refresh-pivot-table-in-excel","status":"publish","type":"post","link":"https:\/\/www.excelchamp.net\/how-to-refresh-pivot-table-in-excel\/","title":{"rendered":"How to Refresh Pivot Table in Excel Manually or Automatically"},"content":{"rendered":"

If your pivot table is created in Microsoft 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010 or Excel 2007<\/strong>, you can refresh it quickly and easily<\/strong> with just a few clicks, or even automatically.<\/p><\/blockquote>\n

Excel Pivot table Refresh is extremely easy. There are multiple ways to refresh a pivot table in any version of Microsoft Excel.<\/strong> And they can be used without writing any VBA code<\/u> too. Today I’ll show you 4 ways of how to refresh pivot table.<\/strong><\/p>\n

\"Excel
A Pivot Table is Created<\/figcaption><\/figure>\n

Why do you need to refresh the pivot table?<\/h2>\n

When you create a pivot table in Excel, Microsoft reads the entire data into its memory in a Pivot cache. This pivot cache is then used to build a summarized version of the report from this compressed source data. The pivot cache is actually stored internally within the Excel file and is read into memory whenever you open that Excel file.<\/p>\n

Unfortunately, this pivot cache is not automatically refreshed every time you edit the original data.<\/p>\n

Key Reasons Why You may need to Refresh a Pivot Table in Excel<\/h3>\n
    \n
  1. A refresh of the pivot table is necessary when the underlying data changes,<\/strong><\/li>\n
  2. A source data value is corrected or updated at a subsequent time,<\/li>\n
  3. Additional Rows are added in the raw pivot table data source,<\/strong><\/li>\n
  4. Existing Rows are deleted in the original data used as a source for the Pivot Table, or<\/li>\n
  5. When the day, week, month or quarter changes, new data arriving from the sources (SAP, ERP, upstream data sources) changes.<\/strong><\/li>\n<\/ol>\n

    So when you create a pivot table to analyze data<\/a>, and then after a few days or weeks, the data has changed, you do not need to re-create the Pivot Table. You can simply refresh it.<\/p>\n

    As you can see, there are several instances when data changes, and we need to refresh the Pivot Table<\/strong>. Any or all of these use cases make it necessary to refresh the pivot table – manually or automatically<\/strong>.<\/p>\n

    The key way to do this pivot table refresh is to prod Excel into refreshing the pivot table manually, or automatically, depending on your choice. The options to do this in many ways is listed in this article, in detail.<\/p><\/blockquote>\n

    First of all, you must create a Pivot Table in any version of Excel. Then you can summarize data, group it, and analyze it your way. Actually, there are more than a dozen things you can do with Excel pivots<\/a>, that will truly transform your data.<\/strong><\/p>\n

    As mentioned, all of these versions are supported: Microsoft 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010 and Excel 2007.<\/em> You can refresh the pivot table in any of these Excel versions.<\/strong><\/p>\n

    4 Methods: How to Refresh a Pivot Table in Excel<\/h2>\n

    Method 1 to Refresh Pivot Table Manually: Existing Data Values change, but there is no change in the number of rows of data:<\/h3>\n

    In this method to manually refresh the pivot table<\/strong> in Excel, you can make any changes to the data first.<\/p>\n

    Then go to the Pivot Table. Right-click<\/strong><\/em> inside the Pivot, and choose the Refresh<\/strong><\/em> option from the right-clicked menu. (See screenshot below for the Refresh option in the Pivot Table Right click menu).<\/p>\n

    The Pivot Table is refreshed immediately.<\/strong> Use this easy way every time the source data changes. To use this method to refresh a pivot table manually, your cursor must be inside the pivot table. It can be anywhere inside there.<\/p>\n

    You can also click on the Refresh All<\/strong> button in the Excel Ribbon, on the PivotTable Analyze<\/strong> menu.<\/p>\n

    \"Refresh
    Refresh Pivot Table Using Right Click inside the pivot table<\/figcaption><\/figure>\n

    Method 2 to Refresh Pivot Table Manually when New Data Arrives: There is a change in the size of the data in Excel:<\/h3>\n

    New rows or columns are added to the data set, and this new data is not reflected inside the pivot table. In this case, we need to redefine the data range source used for the pivot table, to include the latest data rows & columns. There are a couple of ways to do this.<\/p>\n

    a. If your pivot table is based on data from a range of cells, like cells A1:G100, you need to expand this range to include additional rows and columns.<\/h4>\n

    The easiest way is to go to Pivot Table Analyze<\/strong> Tab in the Ribbon and choose the Change Data Source option button<\/strong>. The current source data range is highlighted. Go ahead and reselect the new data range, and click the OK<\/strong> button in the dialog box.<\/p>\n

    \"Change
    Change Data Source<\/strong><\/em> in the Excel Pivot Table.<\/figcaption><\/figure>\n

    Once the data source is updated to reflect the newly added data, the Pivot table is refreshed automatically too. If you wish, you can click on the Refresh<\/strong> button on the Pivot Table Analyze<\/strong> tab again at any time.<\/p><\/blockquote>\n

    \"Pivot
    Pivot Table Refresh under Analyze Tab<\/figcaption><\/figure>\n

    Once this is done, then you can click on the Refresh All<\/strong><\/em> button, and the pivot table\u00a0 is refreshed from the newly selected rows\/columns, and updated in the pivot. The pivot table has been refreshed instantly.<\/p>\n

    b. If your pivot table is based on a Table, like Pricing_Table, Table1, and has been converted into a Table, the job is much easier.<\/h4>\n

    c. All you need to do is to click on the<\/strong> Refresh button under the PivotTable Analyze Tab, and the pivot table is refreshed automatically.<\/h4>\n

    Using a Table as a data source for the Pivot Table is a good practice you should use more often. It uses the entire Excel table when creating a new pivot table, and any extra rows added or removed later on will automatically be considered as part of the Pivot Table. Thus, whenever you add new rows or remove rows from the underlying table, and then click the Refresh button, it will automatically refresh a pivot table.<\/p>\n

    Method 3 to Automatically Refresh a Pivot Table upon Opening the File: Simyly Set the Pivot Table to Automatically Refresh every time the Excel file opens:<\/h3>\n

    This is the best option because you do not have to remember to refresh the pivot table at all. The pivot table is refreshed automatically every time the Excel file is opened.<\/p>\n

    \"Go
    Go To Analyze Tab, and select the Pivot Table Options<\/figcaption><\/figure>\n

    To refresh the pivot table automatically<\/strong> upon opening the Excel file, go to the Pivot Table Analyze<\/strong> tab, and look for Pivot Table Options button.<\/strong><\/p>\n

    \"Refresh
    Refresh Pivot Table Automatically upon opening the Excel File<\/figcaption><\/figure>\n

    When the above popup opens, go to the Data<\/strong> tab, and then select the third check box “Refresh data when opening the file”<\/strong>. (See Screenshot above).<\/p>\n

    This will ensure that every time you open the Excel file, the Pivot table will refresh automatically.<\/p>\n

    But be careful, if there are large amounts of data, or uses an external data source, the data is stored externally in a shared drive, the initial opening refresh may take some time. During this time, it may seem that Excel is not responding.<\/p>\n

    So just be patient for a few moments, and once the refresh is complete, you are good to begin your analysis of the latest, refreshed data.<\/p><\/blockquote>\n

    Also, at the time of running this automatic refresh, you must have access to the data source.<\/strong> This means that the person opening the file must have access to the shared file path, and have the associated permissions to open the source file<\/strong> to read, update and consume it.<\/p>\n

    That’s it. We have successfully refreshed our pivot table in Excel. If you had created any custom calculation in the Pivot table<\/a>, that custom calculation will be automatically updated too.<\/p>\n

    With these methods, you can refresh a pivot table manually or automatically.<\/p>\n

    Keyboard Shortcuts to Refresh the Pivot Tables in Office 365, Excel 2019 & All Other Excel Versions<\/h2>\n

    Once you are inside a pivot table, you can click ALT + F5 keys together to force a manual refresh of the pivot table using this keyboard shortcut.<\/p>\n

    If you want to refresh all pivot tables using keyboard shortcuts, you can click Control+Alt+F5 keys together. This will refresh all pivot tables on the Excel file. This is a good option if you have multiple pivots, and do not want to refresh them manually, one by one.<\/p>\n

    How To Refresh a Pivot Table in Earlier Versions of Excel<\/h2>\n

    If you are using an earlier or outdated version of Excel, you can still refresh the pivot table by using the step outlined above, with some minor changes to the menu or options inside the Pivot Table.<\/p>\n

    How to Refresh Pivot Table in Excel 2016:<\/h2>\n
      \n
    1. Right-Click inside the Pivot Table<\/em><\/strong> & Choose Refresh<\/strong><\/em>.<\/li>\n
    2. Redefine the range of the source data<\/strong><\/em> by clicking on the Change Data Source<\/strong><\/em> button.<\/li>\n
    3. Use a Table as a source of data.<\/strong><\/em> That way, when the data increases, the Table will expand, and all you need to do is to click\u00a0 Refresh on the Analyze Tab.<\/strong><\/li>\n
    4. Automatically Refresh a Pivot Table upon opening<\/em><\/strong> by setting the Pivot Table Options under the Analyze Tab.<\/li>\n<\/ol>\n

      How To Refresh Pivot table in Excel 2013:<\/h2>\n
        \n
      1. First, make the Pivot Table.<\/li>\n
      2. Right-Click inside any single Pivot Table & Choose Refresh<\/strong>.<\/li>\n
      3. Redefine the range of the source data by clicking on the Change Data Source<\/strong> button. This is available under Pivot Table Options or the Pivot Table Analyze tab. Different versions patches may show you a different option.<\/li>\n
      4. Use a Table<\/strong> as a source of data<\/strong>. That way, when the number of rows increases, the Table will expand, and all you need to do is to click\u00a0 Refresh on the Analyze Tab in Excel 2013.<\/strong><\/li>\n
      5. Automatically Refresh a Pivot Table upon opening by setting the Pivot Table Options under the Analyze Tab.<\/li>\n<\/ol>\n

        How To Refresh Pivot Table in Excel 2010:<\/h2>\n