Search code examples
exceltextfilterexcel-2007subtotal

When grouping by Subtotal, remove rows without word "Total" in it

I have Excel 2007, which means I only have a Filter button and not arrow next to the filter to filter by text. I have grouped totals e.g.:

A  600
A Total 600
B 100
B 200
B Total 300

Is there any way to filter this in order to only get?:

A Total 600
B Total 300
Solution

  • If you have the Filter button (in the Ribbon) you should only need to select say A Total, and click on the button to see a dropdown that includes Text Filters. Move to the right at that level and you should see another drop down that includes Custom Filter.... Select that and in a new window you should be able to select does not contain and Total. This should hide the components of the subtotals.

    However, when Subtotalling grouping is normally automatic. One or more numbers should appear under the Name Box and clicking on these should expand or collapse the level of detail shown for the Subtotalling.

    If none of the above works you might perhaps want to consider reinstalling Excel.

    If you don't just want to hide the detail but to remove it you would need to select your data, Copy, Paste Special, Values over the top of it (to preserve the values calculated by the Subtotals) before deleting the detail. The detail may be selected by reversing the selection mentioned for Filter above.