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:

Select the data range.

Click on Insert, and Table.

Click My Table has Headers.

Click on insert tab, and choose Pivot Table.

Click ok.

Choose the fields on the right.

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

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

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.

ActivePivotTable.Refresh

End Sub

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

See also  Modifying a calculated field in a pivot table

Related posts:

How to Use Trig Functions in Excel? How to do the win loss data analysis using Quick Analysis? Counting unique values in pivot table