Excel: How to filter a table with a total row?

Question

I've a very large table, and of course I need filtering.

also, my users ask for a total row at the end. But if I give the total row, this very last row will be sorted and/or filtered as a normal row.

Is there a way to have filtering AND total row at the end? I need to exclude the last row from be filtered and sorted.

Solution 1

Add a blank row after the main table. If necessary for looks, you can then hide it, but Excel will recognize the separation.

Solution 2

Find the complete steps to solve the last row total.

1. Type in the entries.
2. Select all the data and create a table from the "Insert" tab.
3. Now click on any cell and select "Design" tab.
4. In the "Design" tab select/check the "Total Row" option.
5. Now a Total line/row will be created with sum of last column by default.
   http://www.excel-university.com/wp-content/uploads/20140605-a.gif
6. Select the desired column in that Total row and select sum from the dropdown.
   http://www.excel-university.com/wp-content/uploads/20140605-b.gif

Solution 3

Say we start with:

Then in B8 we enter:

=SUBTOTAL(9,B2:B7)

This is like SUM(), but will only add the visible values. Then we set the filter, but only down to row#7:

Now if we perform filtering, the sum row will always be visible:

Solution 4

Put the total at the top of the column rather than the bottom. I have the same issue and this works well for me.

View more solutions

Comments

[CharlieRB]

Is the data actually formatted as a table in Excel? How did you add the total row? Did you use the "Total Row" feature in the Table Style Options? What have you attempted to resolve this?

[DavidPostill]

Please explain how this solves the filter problem.

[realtebo]

Your solution doesn't works welll because if the user remove the filters and then readd it, the row number 8, with the formulas, will be included within the others rows

[Gary's Student]

@realtebo You are correct............the same issue occurs with un-filtered data as well.

[Malachi]

get rid of the previous total row and create it in the built in Total row, then it will not be included in the filtering. this solution will work if you format your data as a table through the built in formatting of Excel.

[LudvigH]

I agree this is the way to do it, but please add why one should do it like this.