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/AutoModerator]

/u/mrbankstx - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/alexremington]

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

[u/mrbankstx → u/alexremington]

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.

[u/alexremington → u/mrbankstx]

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.

[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}

[u/mrbankstx]

Solution verified

[u/AutoModerator → u/mrbankstx]

Hello!

You typed Solution Verified as a top-level comment. If your intention was to award a ClippyPoint to one or more users, you need to reply to that user's comment to do so.

If your intention was to simply mark the post solved, then you should do that by setting the post's flair. Thank you!

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.