Microsoft Office Tutorials and References
In Depth Information
was introduced, you had to create formulas that added, subtracted, multiplied, and
divided the results of the RAND function, which are always decimal values between 0
and 1, to create your data.
Summarizing Worksheets with Hidden and Filtered Rows
The ability to analyze the data that’s most vital to your current needs is important, but
there are some limitations to how you can summarize your filtered data by using functions
such as SUM and AVERAGE . One limitation is that any formulas you create that include the
SUM and AVERAGE functions don’t change their calculations if some of the rows used in
the formula are hidden by the filter.
Excel provides two ways to summarize just the visible cells in a filtered data list. The first
method is to use AutoCalculate. To use AutoCalculate, you select the cells you want to
summarize. When you do, Excel displays the average of values in the cells, the sum of the
values in the cells, and the number of visible cells (the count) in the selection. You’ll find
the display on the status bar at the lower edge of the Excel window.
When you use AutoCalculate, you aren’t limited to finding the sum, average, and count
of the selected cells. To display the other functions you can use, right-click the status bar
and select the function you want from the shortcut menu. If a check mark appears next
to a function’s name, that function’s result appears on the status bar. Clicking a checked
function name removes that function from the status bar.
AutoCalculate is great for finding a quick total or average for filtered cells, but it doesn’t
make the result available in the worksheet. Formulas such as =SUM(C3:C26) always
consider every cell in the range, regardless of whether you hide a cell’s row by
rightclicking the row’s header and then clicking Hide, so you need to create a formula by
using either the SUBTOTAL function or the AGGREGATE function (which is new in Excel
2010) to summarize just those values that are visible in your worksheet. The SUBTOTAL
function enables you to summarize every value in a range or summarize only those
values in rows you haven’t manually hidden. The SUBTOTAL function has this syntax:
SUBTOTAL(function_num, ref1, ref2, ...) . The function_num argument holds the number
of the operation you want to use to summarize your data. (The operation numbers are
summarized in a table later in this section.) The ref1 , ref2 , and further arguments
represent up to 29 ranges to include in the calculation.
As an example, assume you have a worksheet where you hid rows 20-26 manually. In this
case, the formula =SUBTOTAL(9, C3:C26, E3:E26, G3:G26) would find the sum of all values
in the ranges C3:C26, E3:E26, and G3:G26, regardless of whether that range contained