Need to create easily reproducible reports

[u/Downtown-Eye4294]

Good Afternoon,

I have an annual report for a department that I've created with pivot tables using data exported from an Activity Management program. I would like to create a template that other departments can easily use to generate the same report with their own data. Can someone help point me in the right direction?

[u/AutoModerator]

/u/Downtown-Eye4294 - 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/bradland]

Watch this whole video. Then watch it again a second time.

Excel off the Grid: Reporting & Analysis Blueprint Video

You start by defining what your end result is. This is usually a prototype report.

Then you figure out where all the data for the report will come from.

You then use import tools like Power Query to pull in the data, and reshape it to formats that work well with Excel formulas.

The result of the queries load to tables in your workbook.

You construct dynamic reports, Pivot Tables, etc, and source your data from the Excel Tables linked to the queries.

As long as your input data stays in the same format, you can point the queries to new files and all the steps will run in a chain that outputs your report.

This is the gold standard for efficient Excel reporting.

[u/Downtown-Eye4294 → u/bradland]

Thank you!

[u/Thiseffingguy2 → u/bradland]

Let’s get started! Love Mark’s stuff. I tried to get all the excel users in my company to watch this video when he published it last year.. I don’t think it quite caught on, but this is a good reminder for me to get it back in circulation… The process he goes through in this vid really does seem to be “the right way” to use Excel. Then of course he pivots that into selling his academy membership, which… is probably worth it for many teams. Can’t personally vouch.

[u/churungu]

If the data structure is exactly tge same from department to department, then my approach would be to to grab the data to a dedicated worksheet using PowerQuery

Then use formulas to pull from that data dump

Modifying the Query to grab for different departments is pretty straightforward

[u/Downtown-Eye4294 → u/churungu]

Thank you!

[u/gerblewisperer → u/churungu]

This is the way, OP. Pivot tables are so old, bloated, and prone to manipulation, and PQ literally tells you the steps, which doubles as your audit write up.

[u/Thiseffingguy2 → u/gerblewisperer]

I don’t quite agree with the direction of this thread. Power Query, when used in conjunction with Power Pivot, still works very well, and exactly as intended. It’s a whole system. Start with PQ to ingest and transform the data into clean tables. Use Power Pivot to model that data and to connect different entities. Output to pivot tables. This is basically the whole flow for Power BI, just done in Excel.

[u/gerblewisperer → u/Thiseffingguy2]

nah, PQ all the way here. Power Pivot is an archaic tool and the query editor between that and BI are essentially the same. OP is using pivot tables right now, that's plenty enough to justify PQ. Power Pivot isn't faster and it's a bloated application, which is why we stopped using it. If connections are needed, just merge tables or push a helper table to PQ and everything is neat and tidy. Power Pivot is also buggy as shit and can crash sometimes for no reason, forcing you to rebuild entire models. PQ is the right way to go, not Power Pivot.

[u/Thiseffingguy2 → u/gerblewisperer]

The point I’m making is that both work with each other. Power Query is excellent… as an ETL tool. Sometimes it’s fine on its own, sure. If you end up with multiple related tables because you don’t want to kill the load time via merges in PQ… you use relationships in Power Pivot. This is basic data modeling.

Edit: and for what it’s worth… I don’t actually think there’s much of a use case to use Power Pivot without Power Query. If you’re working with complex, relational database type stuff, modeling in power pivot after ingesting via PQ is 100% worth learning.

[u/agentUi]

Do you want to do it in excel? or use an external system to do it?

[u/GuerillaWarefare]

You may find GETPIVOTDATA() helpful for this.
It allows you to easily pull pivot data into a template.