Subtotals after each group

New Contributor

Hi all, 


I am new to this community and I haven't really worked with excel in terms of creating reports. I have been using Power BI but i am not able to recreate the below. 


I have the below report in Business Objects and would like to replicate this in excel. In yellow is what i would like to show. So the grouping is based on W litres and then the collection litres are summed. Is this possible to do in excel?GcZ0T.jpg

6 Replies


Click anywhere in the table.

On the Data tab of the ribbon, in the Outline group, click Subtotal.

Select W_LITRES in the 'At each change in:' dropdown.

Tick the check box for COLLECTION_LITRES in the 'Add totals to;' dropdown, and clear the check boxes for columns you don't want to total.

Click OK.

@Hans Vogelaar thanks for that! Is there anyway to have the subtotals on the same line? I want to have max of two columns then a sum of another column, but it is creating two separate lines?


Can you explain in more detail what you want to do, perhaps attach a sample workbook?

@Hans Vogelaar attached is what i have as an example. In Yellow, this is what the subtotals is producing, in Green is what i would like it to show. 

best response confirmed by YasirAman (New Contributor)


You can get subtotals on one line by creating them in one go, then altering the formulas.

But it won't work for the DATETIME_CUST column since it contains text values that Excel doesn't recognize as valid dates and times. It will return 0 as maximum. You'd have to change the values to real date/time values for the SUBTOTAL formulas to work.

See the attached version.