How to create a dynamic pivot table and refresh automatically

To create a dynamic Pivot table that refreshes automatically, you need to start with data that is in a table format. The table must have headers, and the data must be in a consistent format.

Creating a dynamic pivot table

Creating a dynamic Pivot table that refresh automatically begins with this kind of data:

data table

Select the data range.
select data

Click on Insert, and Table.

insert table

Click My Table has Headers.

my table has headers

Click on insert tab, and choose Pivot Table.

insert pivot table

Click ok.

create pivot table

Choose the fields on the right.

choose fields

Click on any part of the Power Pivot, analyze, and Options under Pivot Table Name.

analyze options

Choose Data, and Check on Refresh Data When Opening the File.

refresh pivot table

The Pivot table will now refresh automatically whenever you open the file.

Refreshing a Pivot Table When the Data Changes

There are a few ways to refresh a Pivot table when the data changes.

  • Manually refresh: Click on the Analyze tab and then click on the Refresh button. You can also press Alt+F5.
  • Automatically refresh: Set the Pivot table to refresh automatically when the file is opened or when the data changes. To do this, right-click anywhere in the Pivot table and select Options. In the PivotTable Options dialog box, click on the Data tab and check the Refresh data when opening the file and Refresh data when data changes boxes.
  • Use a macro: You can also use a macro to refresh the Pivot table automatically. To do this, record a macro that runs the Refresh command. Then, assign the macro to a shortcut key or a button.
Sub RefreshPivotTable()

'This macro refreshes the active Pivot table.


End Sub

Whenever you run the code, the Pivot table will be refreshed.