Chapter 4: Working with Excel Ranges and Tables
4
The header row
The header row is generally colored differently than the other rows. The names in the
header identify the columns. If you have a formula that refers to a table, the header row
will determine how the column is referred to. For example, the Days Employed column
contains a formula that refers to the Hire Date column (column D). The formula is
=NOW()-[@[Hire Date]]. If your table is longer than one screen, the header row will
replace the normal column headers in Excel when you scroll down.
See Chapter 17 to learn about special table referencing in formulas.The header also contains Filter Buttons. These drop-downs work exactly like Excel’s normal
AutoFilter feature. You can use them to sort and filter the table’s data.
The data body
The data body is one or more rows of data. By default, the rows are banded, that is, format-
ted with alternating colors. When you add new data to the table, the formatting of the
existing data is applied to the new data. For example, if a column is formatted as Text,
that column in the new row will also be formatted as Text. The same is true for conditional
formatting.
It’s not just formatting that applies to the new data. If a column contains a formula, that
formula is automatically inserted into the new row. Data validation will also be transferred.
You can make a fairly robust data entry area knowing that the table structure will apply to
new data.
One of the best features of tables is that as the data body expands, anything that refers
to the table will expand automatically. If you were to base a pivot table or a chart on your
table, the pivot table or chart would adjust as you added or deleted rows from the table.
The total row
The total row is not visible by default when you create a table. To show the total row, check
the Total Row check box on the Table Tools Design Ribbon. When you show the total row,
the text Total is placed in the first column. You can change this to another value or to a
formula.
Each cell in the total row has a drop-down arrow with a list of common functions. It’s no
accident that the list of functions resembles the arguments for the SUBTOTAL function.
When you select a function from the list, Excel inserts a SUBTOTAL formula in the cell. The
SUBTOTAL function ignores filtered cells, so the total will change if you filter the table.