Excel Training: Sum Visible Cells Only in Excel 2007 and 2010

A question that often arises when using AutoFilter with Excel is… how can I sum the visible data only? The solution to this question may not always be apparent but all is not lost as there are many Excel training courses in London that deal with how this can be achieved.

There are 2 options for you to do this.

Option 1

Format the data as a table. To do this go to the Home ribbon and from the Styles group click on ‘Format as Table’. Select the table style you require, then, add the total row from the ‘Table Style Options’ group in the Options ribbon. Now, whenever you filter the data the resulting totals will only be shown for the visible cells.

Excel Training Format Data as a Table

Excel Training London Sub-Totals for Tables


Option 2

2) Should you not wish to work with the data formatted as a table then, apply a filter first, add the totals you require in the row of blank cells below the visible cells. Use the AutoSum  tool for this purpose which will create SUBTOTAL functions calculating the visible cells only. When you clear the filter the totals now shown will be for all the data. Adding a new filter will show the totals for the data that is visible.

The 2nd option can also be used in Excel versions before 2007.

Excel Training London Data Sub-Totals


Since there are so many different things that one can do in Excel, it is extremely important to take advantage of the Excel training courses available in London which cover the most useful tools and functions.