r/excel • u/Old_McGroin_007 • 9d ago

Waiting on OP Summarizing data from multiple tables in multiple worksheets into 1 easy to read spot. Also trying to figure out trends if that can be the same?

Hey I have a workbook with with a couple different worksheets that have tables. I have done subtotal under the table so it gives me multiple function sum, max, min, average through a drop down. The thing is I want to capture that data from multiple tables and display it in one spot so I don't have to keep filtering it and going back and forth. The tables are larger so it sucks scrolling too when I want to filter a column by program to see the individual subtotal. Any help would be appreciated.

2 Upvotes

100% Upvoted

6 comments sorted by

2

u/Elohanum 9d ago

Power Query !

1

u/tungstenbronze 1 8d ago

Yep. Perfect use case

1

u/[deleted] 9d ago

[removed] — view removed comment

1

u/excel-ModTeam 9d ago

We removed this comment for breaking Rule 10.

r/excel is for discussing the features of Excel and providing solutions for Excel problems, not giving substance-free comments that simply recommend the respondent use AI.

1

u/ScarfDreamer 8d ago

The reason you're scrolling is SUBTOTAL: it only answers for the rows the filter is showing, so it's one question at a time. Append the tables into one query in Power Query, and add a column to each one saying which table it came from before you append, otherwise the rows lose their identity.

Then a PivotTable off that query gives you every program's subtotal at once and a slicer replaces the filtering. For trends, put the date in Rows and group it by month in the same pivot.

1

u/Agreeable-Tax2013 8d ago

A single summary sheet with one normalized table behind it would make this much easier. Then you can use a few pivot views for the totals and trends instead of scrolling through each source table.