Microsoft Office Tutorials and References

In Depth Information

**Summarizing worksheets by using hidden and filtered rows**

The
RANDBETWEEN
function generates a random whole number within a defined range.

For example, the formula
=RANDBETWEEN(1,100)
would generate a random integer value

from 1 to 100, inclusive. The
RANDBETWEEN
function is very useful for creating sample

data collections for presentations. Before the
RANDBETWEEN
function 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 by using 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.

5

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 the 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 selected 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 right-clicking

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 to summarize just those values that are

visible in your worksheet. With the
SUBTOTAL
function, you can 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