Microsoft Excel logo on a green background

Using a pivot table, you can analyze large amounts of data easily. But as we all know, data can and often does change. To make sure that your data is current, here’s how to refresh a pivot table in Excel.

Whether the data in your pivot table comes from an external source or the same workbook, you can update it manually or automatically. You can also adjust a setting so that the formatting doesn’t change when you update the table.

Refresh a Pivot Table Manually

If you would prefer to update your pivot table manually when needed, start by selecting the table.

Go to the PivotTable Analyze tab that appears. Click the Refresh drop-down arrow in the Data section of the ribbon.

To refresh the selected pivot table, choose “Refresh.” To refresh all pivot tables in your workbook, choose “Refresh All.”

Refresh or Refresh All for a pivot table

Advertisement

Alternatively, you can right-click the pivot table and choose “Refresh” in the shortcut menu.

Refresh in the shortcut menu

If the update takes a bit of time, you can select Refresh > Refresh Status to see the progress. To cancel, choose Refresh > Cancel Refresh.

Refresh a Pivot Table Automatically

Maybe you’d rather that your pivot table update each time you open the workbook. This is a good way to always have refreshed data and saves you from remembering to manually update the table.

RELATED: What are Pivot Tables in Google Sheets, and How Do I Use Them

Select the pivot table and go to the PivotTable Analyze tab. On the left side, use the PivotTable drop-down arrow and click Options > Options.

Open the pivot table Options

In the PivotTable Options window, select the Data tab. Then, check the box for Refresh Data When Opening the File. Click “OK.”

Refresh when opening the file

Prevent Formatting Changes Upon Update

Sometimes the data that gets updated is wider than your column width or longer than your row height. If you want to retain the formatting for your columns and rows when you refresh a pivot table, it’s a simple setting.

RELATED: How to Set Row Height and Column Width in Excel Using the Keyboard

Select the pivot table and go to the PivotTable Analyze tab. On the left side, use the PivotTable drop-down arrow and then click Options > Options.

Advertisement

In the PivotTable Options window, select the Layout & Format tab. Then, check the box for Preserve Cell Formatting on Update. Optionally, you can check the box directly above for Autofit Column Widths on Update. Click “OK.”

Preserve pivot table formatting

Keep your data current and up to date by refreshing your pivot table in Excel, either manually or automatically.

RELATED: How to Use Pivot Tables to Analyze Excel Data

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio »