To refresh a pivot table in Excel, simply right-click on the table, select “Refresh” from the dropdown menu, or use the “Refresh” button in the PivotTable Tools options. This will update the table with any changes made to the source data. Now, let’s dive into the step-by-step tutorial to ensure you’ve got all the details down pat.
Step by Step Tutorial: How to Refresh a Pivot Table in Excel
Refreshing a pivot table in Excel is like giving it a quick shower to make sure it’s up to date with the latest information. Let’s walk through the steps to ensure your pivot table is reflecting the most current data.
Step 1: Select the Pivot Table
Click anywhere inside the pivot table that you want to refresh.
This step is crucial because it tells Excel exactly which pivot table you want to update. If you’ve got more than one pivot table in your workbook, make sure you’re clicking on the right one.
Step 2: Refresh the Pivot Table
Right-click and select “Refresh” from the dropdown menu, or go to the “PivotTable Analyze” tab and click on the “Refresh” button.
This step is like hitting the reset button on your pivot table. It tells Excel to pull in the latest data from your source sheet and update the table accordingly.
After you complete these steps, your pivot table will display the most current data from your source sheet. It’s like magic – with just a couple of clicks, you’ve got an updated table!
Keyboard Shortcuts and Refresh All
- Alt + F5 refreshes the selected PivotTable.
- Ctrl + Alt + F5 refreshes every PivotTable and data connection in the workbook.
- Data > Refresh All does the same from the ribbon, which is handy when several PivotTables share the same source.
Microsoft covers every refresh option, including external data sources, in its guide to refreshing PivotTable data.
Include New Rows in the PivotTable
Refreshing only reads the original source range. If you added rows below it, they won’t appear. Two fixes:
- Change the data source: click inside the PivotTable, go to PivotTable Analyze > Change Data Source, select the expanded range and click OK. Then refresh.
- Use an Excel table as the source: click your data, press Ctrl + T, then point the PivotTable at the table name. Tables grow automatically, so every future refresh picks up new rows.
For more on editing the structure, see how to edit a pivot table in Excel.
Refresh Automatically When the File Opens
- Right-click anywhere in the PivotTable and choose PivotTable Options.
- Open the Data tab.
- Check Refresh data when opening the file and click OK.
For PivotTables based on external data, go to Data > Queries & Connections, right-click the connection, choose Properties, and set Refresh every to a number of minutes.
Stop Column Widths from Changing on Refresh
If your carefully sized columns snap back after every refresh, open PivotTable Options, go to Layout & Format, and uncheck Autofit column widths on update. Keep Preserve cell formatting on update checked to hold onto your formatting.
Troubleshooting Refresh Problems
- “A PivotTable report cannot overlap another PivotTable report.” The refreshed table needs more room. Move one PivotTable or insert blank rows or columns between them.
- “The PivotTable field name is not valid.” A column in the source data has no header. Add headings to every column.
- Old items still appear in filters. In PivotTable Options > Data, set Number of items to retain per field to None and refresh.
- Refresh is greyed out. The sheet is protected or you’re editing a cell. Unprotect the sheet and press Esc.
- Numbers don’t add up. Some source values are stored as text. Convert them to numbers, then refresh.
Tips: How to Ensure Your Pivot Table Stays Fresh
Keeping your pivot table updated doesn’t have to be a chore. Here are some tips to make it a breeze:
- Make sure your source data range includes all the data you want to analyze.
- If you add new data to your source sheet, expand the data range in your pivot table settings.
- Use the “Refresh All” button if you have multiple pivot tables linked to the same data source.
- Set up your pivot table to refresh automatically every time you open the workbook.
- Check for any errors in your source data that might prevent the pivot table from refreshing properly.
Refresh updates the selected PivotTable. If your workbook contains several PivotTables or external data connections, use Data > Refresh All when you need to update everything together. If new rows are outside the PivotTable’s current source range, refreshing alone may not include them; update the data source or use an Excel Table so the source can expand more reliably.
Frequently Asked Questions
What is a pivot table?
A pivot table is a powerful tool in Excel that allows you to summarize and analyze large amounts of data quickly and easily.
Why won’t my pivot table refresh?
There could be several reasons, like if your source data range doesn’t include all the data or there are errors in your source data.
Can I refresh all pivot tables at once?
Yes, use the “Refresh All” button under the “Data” tab to update all pivot tables in the workbook.
How do I change the data source for a pivot table?
Right-click on the pivot table, select “PivotTable Options,” go to the “Data” tab, and click “Change Data Source.”
Can I make my pivot table refresh automatically?
Yes. Right-click the PivotTable, choose PivotTable Options, open the Data tab and check Refresh data when opening the file. Newer Microsoft 365 builds are also rolling out an Auto Refresh option that updates PivotTables as soon as local source data changes.
Related: how to make a pivot table in Excel.
Related: find a pivot table in Excel.
Related: edit a pivot table in Excel.
Related: How to Reduce the Size of an Excel File.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.