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.
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
Sort & Filter
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
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.