See any report – data comes first and then the grand total (or other summary calculations). What does the viewer of the report want to look at first? The Grand Total. Should that not be shown in the beginning? Obviously yes.
Resist that urge of putting summary after the data. Put it in the beginning.
Data should be tabular – headings followed by data. Convert it to an Excel table first. Select data – Insert – Table. Now you see Table Tools – Design menu on top. In that give the data a name (default name will be Table1, Table2, etc.). Let us say the name is mydata. Put a few blank rows BEFORE the data and add a formula on top to see the summary. That’s it!
2 Responses
I love that, and often do just what you suggest, even when the main viewer of the report is me! With a twist: Insert 3 blank rows above the header. In top row is an AGGREGATE formula to sum (or count, etc) all data rows; in 2nd row is an AGGREGATE formula to sum or count only the filtered data rows; 3rd row is blank. If I’m sharing it I’ll add simple labels: ‘Unfiltered’ (row 1), ‘Filtered’ (row 2). For quick help on AGGREGATE see https://support.office.com/en-sg/article/AGGREGATE-function-991c9adc-88dc-4ab9-9fbf-04346623d79d. Thanks for your blog, Nitin.
Hi Jon. Thanks for the feedback and more ideas. Yes, AGGREGATE is a great function to use.
The only reason I used the Table syntax is because I wanted to highlight this feature of tables – which is almost completely unknown! The disadvantage of this feature is that I cannot drag this formula.
In short, we should use AGGREGATE or Table Syntax depending upon the context.