Excel formulas are extremely powerful and useful. They can also be cryptic. What exactly does a formula such as =TODAY()–D2 actually mean? Without looking at the worksheet, it’s impossible to tell. But what if the formula looked like =TODAY()–[Date of Birth] instead? Just by looking at the formula, you can tell that it calculates the current age in days.
One of the exciting table features introduced in Excel 2007 was the ability to create meaningful formulas in a simple, unambiguous way. One of the most common uses of a formula in a table is to perform a calculation that looks only at values from other rows of the same table. This type of formula is extraordinarily easy.
For example, using traditional formulas to calculate the (approximate) age in years of each staff member, you would use a formula such as the one in cell G2 in Figure 22-22.
After you change the list to a table, you can edit the formula, and Excel adjusts the reference for you. Just select a cell with the formula, select the cell in the formula with the traditional single-cell reference, and then click the cell you are referring to (in this case, cell D2). Excel automatically replaces the cell address with the name of the table column, as you can see in Figure 22-23.
When you enter the formula in the cell, Excel automatically copies the formula to the entire Age column, replacing all the existing formulas.
Note
Excel automatically replaces all the formulas only if they are all equivalent. If you created an exception formula somewhere in the list, Excel puts the formula into only the one cell, but it adds an AutoCorrect icon that asks whether you want to replace all the formulas in the column with the new formula.
You might wonder about a few characteristics of the formula. First, why did Excel put brackets around the name of the column so it shows up as [Date of Birth]? Well, Excel needs something around the name because otherwise the spaces in the name could make it hard to tell where the name ends. Simple parentheses wouldn’t work well because they are used in formulas to show calculation order. (Brackets are what Microsoft Access 2010 uses to identify column names, and it works pretty well in that application, too. )
Figure 22-23. By turning the list into a table and editing the formula, we can turn the formula in G2 into something easier to read.
Second, how does the formula know which row to use? In the original formula in cell G2, the reference was D2. When you copy the formula to cell G3, the reference changes to D3. The reference may be a little cryptic, but it’s pretty clear the column and the row to which it refers. The reference [Date of Birth] tells which column, but how does Excel know which row? Notice in the traditional reference that the row number in the reference (for example, B2) is always the same as the row number of the cell that contains the formula (for example, G2). The people on the Excel team noticed that in many, many formulas—and especially in formulas in tables—the cell references point to other cells in the same row. So they created a rule. If the formula needs a single value (and the minus sign for subtraction needs a single value) and you give it a whole column as the reference, it picks just the value from the current row. This is called implicit intersection because the formula implicitly uses the current row to intersect the range reference in the formula to get a single cell. (Excel can also implicitly intersect the current column with a horizontal range if necessary, but tables don’t have horizontally named rows.)
Sometimes you need to refer to a row that’s not the current row. For example, a common calculation in a table involves comparing the current row to the total row. If your table includes sales by regions, you may want to show the percent each region is of the total. Or, because our Staff table example has the age calculated—with the average age in the total row—what if you want to know the age of each staff member compared to the overall average? Excel has a special way to represent the total row in a table formula.
First, type a new heading so Excel creates a new column. Type something like Versus Avg. After the new, formatted column appears in the table, select any cell in the column. Type an equal sign to start the formula, and click the Age column for the current row. The formula will say =[Age], showing that it recognizes the Age column. Then type a minus sign (–), and click the cell for the Age column in the total row. (As a keyboard shortcut, press the Left Arrow key to get to the Age column, and then press Ctrl+Down Arrow to jump to the bottom of the table—the total row.) The formula now says =[Age]–TStaff[[#Total],[Age]]. When you press Enter, Excel inserts the formula into all the cells of the column. You can now apply a number format to the column. The result looks like the worksheet in Figure 22-24.
The syntax is a little bit scary, but once you get the hang of it, it’s not too bad. The first part, =[Age], should be easy. That’s just implicitly intersecting the Age column with the current row. The second part is a little trickier. In the reference TStaff[[#Total],[Age]], TStaff is an arbitrary name assigned to the table. (See Naming a Table on page 733.) [#Total] is a special structured-reference tag that Excel automatically applies to the total row of the table. You can’t use the [#Total] tag without first giving the name of the table. (Excel wouldn’t know which table you meant if you omitted this). So, the full reference has three parts: the name of the table (followed by square brackets), the special structured-reference tag [#Total], and then the column name [Age]. The [#Total] tag and the table column name are separated by a comma.
A structured reference is harder to write than it is to read. Fortunately, Excel automatically creates the structured-reference formula for you as you create new formulas that reference elements of a table.
Implicit intersection lets you use column names in table formulas. It’s a great feature and makes formulas much easier to understand and to write. The only time implicit intersection can be a problem is when you use a function that can accept a whole range, but you want it to apply to only the current row. Implicit intersection kicks in only when the formula requires a single value—which is typical of arithmetic operations. But functions such as SUM can work just fine with a whole range. Suppose, for example, that you want to sum the scores for different parts of a test—but only for the current student. One way to do this is to select each cell separately and use the plus (+) sign to add them. In this way, implicit intersection will work.
But if you have a lot of columns, using a range is easier. Fortunately, Excel has a special structured-reference tag, @, that explicitly limits the range to the current row. And Excel automatically adds that special tag when you create a formula that uses a function such as SUM over multiple columns of the current row. Figure 22-25 shows the formula Excel generates when you create a sum of multiple columns in the current row.
The formula =SUM(SATScores[@[Critical Reading]:[Math]]) includes the standard SUM function. The reference follows the same pattern that is used for referring to the total row. There’s the name of the table, followed by brackets. Within the brackets is the special structured-reference tag @. Finally, there is the part that specifies the column. In this case, you’re specifying a range of columns, so the formula uses the range (:) operator between two table column names.
In addition to the @ tag, whose explicit meaning changes depending on the cell containing the formula, Excel has four structured-reference tags for other parts of the table. Table 22-1 lists the tags along with their meanings.
These names are all row oriented. #Totals and #Headers reference single rows, but #Data and #All include all rows. If you combine any of these with a column name, you get the intersection. If you combine multiple row-oriented names, you get the union. For example, [#Headers],[#Data] results in everything except the totals.
Table 22-2 provides some examples of formulas that use the COUNTA function with structured-reference tags. Because each formula returns the total number of cells in a range, you can see how the different tags interact with one another. (All formulas reference a table named Staff.)
Table 22-2. Some Formulas That Combine Structured-Reference Tags
|
Formula |
Description |
|---|---|
|
=COUNTA(Staff) |
Number of data cells in the table body |
|
=COUNTA(Staff[Salary]) |
Number of data cells in the Salary column |
|
Number of cells in the header row | |
|
Number of cells in the total row | |
|
Number of data cells in the table (same as referencing the table name alone) | |
|
Same as referencing the column name alone | |
|
Number of cells in the entire table, including the header and total row | |
|
=COUNTA(Staff[[#All],[Salary]]) |
Number of cells in one column, including the header and total row |
|
=COUNTA(Staff[[#Headers],[#Data]]) |
Number of cells in the table, including headers but excluding the total row |
|
=COUNTA(Staff[[#Headers],[#Data],[Salary]]) |
Number of cells in one column, including headers but excluding the total row |
You can use the pointer to generate almost all of these combinations while constructing a formula. To select the #Data portion of the table, rest the pointer on the top-left corner of the table until it changes to a black arrow that points down and to the right, and then click once. To select the #All portion of the table, rest the pointer on the same place, but double-click. To select a column’s #Data area, rest the pointer on the top of the column until it turns into a black arrow that points down, and then click once. To select the #All area for the column, rest the pointer on the same place, but double-click. To select the #Header and #Data combination, you have to manually edit the formula.
When you get used to the brackets, structured references are much more meaningful than simple cell addresses. And if you change the size of a table, the structured names automatically adjust. If you change the label in a column heading, any formulas that reference that column are automatically adjusted.