Technology Advisor

[#1]

I recently had a coworker ask me an Excel question, and I thought I’d share the answer here, because it’s a pretty useful tip.

The question was, “If I’m using Autofilters, how can I have my calculated totals include only filtered values?” Well, it’s easy.

First, if you’re not familiar with Excel Autofilters, this is a feature that makes it easy to automatically add column filters to an Excel worksheet. Just select Data; Filter; Autofilter. In Excel 2007, select Sort & Filter; Filter from the Editing group. This will add arrows to your column headers that allow you to filter by individual items within the column or by custom ranges. If you were to add a standard calculated total to the worksheet, the value would not change based on what your filters display.

If you’re adding a total to the same worksheet as your data, its best to insert a line above your header row and place it there. That way, your filter won’t hide it.

Use the formula =SUBTOTAL(function_num,from:to) to add a calculated total. Here’s an example: =SUBTOTAL(9,A3:A50)

The function number (in this example, the 9) refers to the type of calculation being made. “9” is sum; “1” is average, “2” is count (see Excel Help for the full list of 11).

Now, when you use any filter in your worksheet, your calculation will include only those items displayed.

I hope this is useful to you. Do you have a good Excel tip? Share it below.

Mike Savory

Peachtree Product Manager

Message Edited by MikeS on 08-29-2008 02:07 PM