Pivot Table- Auto Refresh



🔁 STEP-BY-STEP PROCESS FLOW FOR PIVOT TABLE USAGE

✅ 1. Convert Data into a Table (Structured Format)

➤ Why?

So that the pivot table source automatically expands when new data is added—no need to manually update the range.

➤ How?

  1. Select your raw data range.
  2. Press Ctrl + T or go to Insert > Table.
  3. Ensure "My table has headers" is checked.
  4. Click OK.

Your data is now in a structured Excel Table (e.g., Table1), which auto-expands.

📊 2. Create the Pivot Table

  1. Click anywhere in your Excel Table.
  2. Go to Insert > PivotTable.
  3. In the dialog box:
    • Choose "Select a table or range" – it will auto-fill like Table1.
    • Choose where to place the Pivot Table (new sheet or existing).
  4. Click OK.

Now build your Pivot Table as usual by dragging fields into:

  • Rows
  • Columns
  • Values
  • Filters

🧠 3. Add VBA to Auto-Refresh Pivot Table

➤ Why?

To auto-update the Pivot Table(s) when the workbook opens or when data changes.

➤ How to add VBA?

  1. Press Alt + F11 to open the VBA Editor.
  2. In the left pane, double-click ThisWorkbook under "VBAProject (Your Workbook)".
  3. Paste the following code:

vba: 

Private Sub Workbook_Open()

    ThisWorkbook.RefreshAll

End Sub

➤ Optional: Auto-refresh when you save the file too

vba

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    ThisWorkbook.RefreshAll

End Sub

  1. Press Ctrl + S and save the file as Macro-Enabled Workbook (.xlsm).

📂 4. Use “Show Report Filter Pages” Feature

➤ Why?

To quickly create separate Pivot Table reports for each value in a Filter field.

➤ How?

  1. Click on any Pivot Table.
  2. Add a field to the Filters area (e.g., “Region” or “Department”).
  3. Go to PivotTable Analyze > Options > Show Report Filter Pages.
  4. Select the field (e.g., "Region") and click OK.

Excel will now generate one worksheet per filter item, each with a filtered Pivot Table view.

💡 Summary Table:

Task

Tool/Step

Benefit

Convert to Table

Ctrl + T

Auto-updating data source

Pivot from Table

Insert > PivotTable

Dynamic source

Auto-refresh

VBA: ThisWorkbook.RefreshAll

Saves time

Multiple Reports

Show Report Filter Pages

Easy breakdown by category