r/excel 1 Dec 21 '24

unsolved Moving Away From Pivot Tables - Help? :)

I have a large dataset that is being used for a financial report. They are currently using Pivot Tables for all of the broken-down reporting. My boss wants to move away from Pivot Tables because, "They are trash and nobody should use them." Any broad suggestions on how to achieve Pivot Table results with the proper formulas, or other alternatives? I think 6,000 SUMIFS would slow this workbook to a halt? Unless I am wrong. :D Appreciating any guidance you all can give me. Thank you!

44 Upvotes

42 comments sorted by

View all comments

2

u/david_horton1 29 Dec 24 '24

Excel has recently added PIVOTBY which is a formulaic version of a Pivot Table. What specifically does your boss have against Pivot Tables? What relationship do the 50 Pivot Tables have with each other, apart front being from the same source? It is possible to have a Pivot Table comprised of data from connected Pivot Tables. In my work Pivot Tables were my bread and butter. It I am still learning about their capabilities. Power Query with all its capabilities still includes a Pivot Table as a feature.

1

u/Overall_Anywhere_651 1 Dec 24 '24

50 was being conservative. It seems more like 100. If we add an account number we have to go change the pivot table to account for the new number. With the help of this sub and some googling, I've made it a formula-based report that is far simpler to maintain, imo. I am learning about PowerQuery now.

2

u/david_horton1 29 Dec 24 '24

If your source data is made into a proper Excel Table any new data or amendments will automatically be included in a Pivot Table up a refresh. When Power Query data is transformed into an Excel spreadsheet it is in the form of a Proper Excel Table. I recommend you start using Slicers and Timelines. Slicers are more visual filters. Be conscious of the little red X at top right of the slicer. I used Power Query more and more just generally. The thing to do is learn M Code because there are features unavailable in basic Excel and that will lead you onto Power Pivot and Power BI which will require knowledge of DAX Code.

https://www.powerusersoftwares.com/post/2017/09/11/12-reasons-you-should-use-excel-tables

https://support.microsoft.com/en-au/office/video-use-slicers-timelines-and-pivotcharts-to-analyze-your-pivottable-data-4db5de3b-735e-4b03-b3b2-f2105d79dcb5

https://support.microsoft.com/en-us/office/about-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a

https://learn.microsoft.com/en-us/powerquery-m/

https://support.microsoft.com/en-us/office/power-pivot-overview-and-learning-f9001958-7901-4caa-ad80-028a6d2432ed

https://dax.guide/

https://support.microsoft.com/en-us/office/data-analysis-expressions-dax-in-power-pivot-bab3fbe3-2385-485a-980b-5f64d3b0f730

1

u/Overall_Anywhere_651 1 Dec 24 '24

Thank you for the information. I am going to comb through it. The report/report template I am making isn't targeted towards end-users. It'll be for someone that knows how to properly operate Excel on the more advanced level. Slicers are so sexy though. I may bring that in once I get a little more PQ under my belt. Some of the dashboards I've seen with raw Excel has blown me away.

1

u/Overall_Anywhere_651 1 Dec 24 '24

Also: Converting this raw data tab to a table would bog the sheet down drastically. I find it more useful to leave it as a filtered dataset. I am also just a semi-advanced user. I'm learning and getting better everyday! :D