4

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.

1
  • 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?
    – CharlieRB
    Jun 11, 2015 at 15:10

6 Answers 6

9

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

4

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
4
  • 3
    Please explain how this solves the filter problem.
    – DavidPostill
    Jun 11, 2015 at 18:39
  • What happen if user remove and then reapply the filter? The total row will be included as a normal row. So this cannot works.
    – realtebo
    Jun 12, 2015 at 7:42
  • 1
    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.
    – Malachi
    Nov 1, 2016 at 14:52
  • I agree this is the way to do it, but please add why one should do it like this.
    – LudvigH
    Mar 16, 2017 at 9:42
1

Say we start with:

enter image description here

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:

enter image description here

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

enter image description here

2
  • 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
    – realtebo
    Jun 12, 2015 at 7:42
  • 1
    @realtebo You are correct............the same issue occurs with un-filtered data as well. Jun 12, 2015 at 10:32
1

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

0

Redefine the limits of the table. Clear all the filters. At the bottom right is a blue caret. That defines the limits of the pivot table. Drag it above the Total and Sum rows to exclude them.

Filtered table with sums at the bottom

0

Redefine the Table so excel recognizes it - Remove filters; Select the table till above the Subtotal row; Quick Analysis placeholder (or Ctrl+Q) ---> Table. Select the default (there is only one) representation of Table - alternate rows in blue.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .