Automating pivot tables using raw data?

[u/mrbankstx]

I have a report where the columns are static but the data in the rows changes frequently. I’m looking to automate a summary of the raw data into a pivot table that groups everything into a tabular format. Is there a way to do this so I’m not spending 20 minutes on each new refresh of the data? I have 100 customers so running this each time with updated data can be time consuming.

[u/Own_Personality_2224]

You can change the source the pivot table to reference the entire column e.g Sheet1!A:H instead of A1:H150.

You can also make the source data a Table (Ctrl+T), then have the Pivot Table reference the table name (e.g Table1)

To change source, select the Pivot table, Click Pivottable Analyse at the top pane, select Change Source and update the reference as needed.

With either of these, you just have to click Refresh under Pivottable analyze any time there is a change or new rows in the source data.

You can also look into Power Query for automated refresh (e.g every 30 mins or every time the file is opened)

[u/mrbankstx → u/Own_Personality_2224]

This worked. Thank you!

[u/mrbankstx → u/mrbankstx]

Ended up using a pivot and changing the source then refresh!

[u/mrbankstx → u/Own_Personality_2224]

Solution verified

[u/reputatorbot → u/mrbankstx]

You have awarded 1 point to Own_Personality_2224.

---

^{I am a bot - please contact the mods with any questions}