Microsoft Office Tutorials and References

In Depth Information

**Working with Tables**

In addition, you can right-click a cell and use the Filter command on the shortcut menu. This

menu item leads to several additional filtering options. For example, you can filter the table to

show only rows that contain the same value as the active cell.

As you may expect, the Total row (if present) is updated to show the total for the

visible rows only.

Some of the standard spreadsheet operations work differently with a filtered table. For example,

you might choose Home

Hide Rows to hide rows. If you then

copy a range that includes those hidden rows, all the data gets copied (even the hidden rows).

When you copy data in a filtered table, though, only the visible rows are copied. This filtering

makes it very easy to copy a subset of a larger table and paste it to another area of your

worksheet. Keep in mind that the pasted data is not a table — it’s just a normal range.

➜

Cells

➜

Format

➜

Hide & Unhide

➜

Similarly, you can select and delete the visible rows in the table, and the rows hidden by filtering

will not be affected.

To remove filtering for a column, click the drop-down control in the row Header and select Clear

Filter. If you’ve filtered using multiple columns, it may be faster to remove all filters by choosing

Home

➜

Editing

➜

Sort & Filter

➜

Clear.

Working with the Total row

The Total row is an optional table element that contains formulas that summarize the information

in the columns. Normally, the Total row isn’t displayed. To display the Total row, choose Table

Tools

➜

Design

➜

Table Style Options

➜

Total Row. This command is a toggle that turns the Total

row on and off.

By default, the Total row displays the sum of the values in a column of numbers. In many cases, you’ll

want a different type of summary formula. When you select a cell in the Total row, a drop-down

arrow appears, and you can select from a number of other summary formulas (see Figure 9-10):

h
None:
No formula.

h
Average:
Displays the average of the numbers in the column.

h
Count:
Displays the number of entries in the column. (Blank cells are not counted.)

h
Count Numbers:
Displays the number of numeric values in the column. (Blank cells, text

cells, and error cells are not counted.)

h
Max:
Displays the maximum value in the column.

h
Min:
Displays the minimum value in the column.

h
Sum:
Displays the sum of the values in the column.

h
StdDev:
Displays the standard deviation of the values in the column.
Standard deviation

is a statistical measure of how “spread out” the values are.