Explanation After you create a table, you can use structured references in formulas that refer to data in the table. Structured references are references that use table names or column headings (or both) instead of cell or range addresses. When you use this feature, Excel automatically names table columns based on their headings (the values in the top row) and then uses those names in formulas, as shown in Exhibit 5-9. Structured references have a couple of advantages:
Structured references can make formulas easier to understand. For example,
“Unit price” is more meaningful than “A2,” and “Qty in stock” is more meaningful than “B2.”
Structured references adjust automatically, so you don’t need to rewrite formulas if you add rows or columns to, or delete rows or columns from, a table.
Exhibit 5-9: A formula using structured references
To use a structured reference when entering a formula, type “[” to begin a column reference. An AutoComplete list appears, showing the names of the table columns you can choose from. You can also type a formula with an A1-style cell reference; Excel won’t replace it with a column name, so you can still create traditional formulas if you prefer.
When you complete the formula by pressing Enter, Excel’s default AutoCorrect behavior fills (copies) the formula to all other cells in the column. If the formula refers to a column name, it will be the same in each cell. So, not only are formulas with structured references easier to read, but they are also less prone to errors. Because each cell can contain the exact same formula, it’s more difficult to accidentally change one.
The Total row
A table can include one special row at the bottom that is designated as the Total row.
Each of its cells can hold a function to summarize the data in the column above.
However, instead of using SUM, AVERAGE, or a similar function, formulas in the Total row are created by default with the SUBTOTAL function. This function, depending on the arguments, can act like SUM, AVERAGE, or other functions.
The reason for this is that the SUBTOTAL function will display the correct value if you apply a filter to one or more columns. For example, if you apply a filter that hides all but two rows, as shown in Exhibit 5-10, the SUBTOTAL function in the Total row will sum just those values, not all values (including the hidden ones).
Unfiltered column Filtered column
SUBTOTAL sums only visible values
SUBTOTAL sums all values
Exhibit 5-10: The SUBTOTAL function in the Total row operates only on visible cells Excel creates a Total row automatically if you select a cell directly below the table and use the AutoSum button to create a formula, or if you check Total Row in the Table Style Options group on the Design tab.
Do it!
C-4: Using structured references
Here’s how Here’s why
1 In D7, type =[ To begin creating a formula with structured references. An AutoComplete list appears, showing the table’s column names.
In the list, double-click Unit Price
To add a column reference to the formula.
Type ] To complete the reference to the column name.
2 Type * To enter the multiplication operator.
Complete the formula as shown
To calculate the unit price multiplied by quantity in stock. This formula is easier to read than one using cell or range addresses.
3 Press e To complete the formula. When you use structured references, AutoCorrect adds a formula for each cell in the column.
Observe the formula in D8 The formula in this cell, and in each cell in the column, is the same as the one you entered. All of these formulas refer to the column name, not to individual cells.
Click the Design tab (Under Table Tools on the Ribbon.) If necessary.
4 In the Table Style Options group, check Total Row
To add a Total row at the bottom of the table.
Select D20 and observe the
formula in it The formula =SUBTOTAL(109,[Total
value]) calculates the sum of the visible cells in the column.
You’ll experiment with other SUBTOTAL types.
5 From the drop-down list for D20, select Average, and then observe the formula
The cell displays the average value. The first argument of the function changes from 109 (which represented Sum) to 101 (which represents Average).
From the drop-down list for D20, select Sum
To reset the formula to calculate the sum.
6 Select C20
Click the Formulas tab
Click AutoSum (Click the button, not its drop-down arrow.) Excel creates a SUBTOTAL function to sum the column.
You’ll now filter the table to test how the SUBTOTAL functions in the Total row work.
7 Click the AutoFilter arrow next to Product
To display the sorting and filtering criteria.
You’ll display only spices with names beginning with the letter C.
8 Choose Text Filters, Begins With…
To open the Custom AutoFilter dialog box. In the upper-left list, the first comparison operator,
“begins with,” is selected.
In the upper-right box, enter C
Click OK To close the dialog box and filter the table so that only spice names beginning with C appear.
The formulas in C20 and D20 sum only the visible values.
9 Select D20
Calculate the average of the displayed values
Click the arrow next to the cell and select Average.
10 Calculate the count of Product items in the displayed list
(Select A20, click the arrow next to the cell, and select Count.) There are six items.
From the Product column’s AutoFilter menu, choose Clear Filter from “Product”
To display all values. The count in A20 changes to 13.
11 Update the workbook