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