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.
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.
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.