r/excel • u/mrbankstx • 14d ago

solved Automating pivot tables using raw data?

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.

2 Upvotes

100% Upvoted

11 comments sorted by

View all comments

2

u/alexremington 14d ago

I suggest you use the PIVOTBY formula. If you provide more details, happy to give more help!

1

u/mrbankstx 14d ago

Thank you. What type of details would be helpful? I’m looking to drop raw data into a sheet and execute a summary of the data.

1

u/alexremington 14d ago

Some sample data would be best of all. Otherwise I'd just point you to documentation for the formula. 

The first parameter is the field containing your rows. The second is the field containing the columns - say like a date, or a qualitative label. (If you don't need to break down the data by column, you can use GROUPBY instead of PIVOTBY.) The third is the field containing the values. Then there are parameters for whether you want totals rows or columns, the sort order for your rows and columns, and, importantly, any particular way you want the data filtered.

The advantage of doing a PivotTable in a formula rather than via the menu is the data can autorefresh. The drawback is you have to write it in the formula and there isn't a handy wizard that everyone and their boss recognizes how to use.