This tip describes some ways to use formulas with a table. The example uses a simple sales summary table with three columns: Month, Projected, and Actual, as shown in Figure 160-1. I entered the data and then converted the range to a table by using the Insert➜Tables➜Table command.
Note that I didn’t define any names, but the data area of the table is named Table1 by default.
Figure 160-1: A simple table with three columns.
Working with the total row
If you want to calculate the total projected and total actual sales, you don’t even need to write a formula. Just click a button to add a row of summary formulas to the table:
1. Activate any cell in the table.
2. Select the Table Tools➜Design➜Table Style Options➜Total Row check box.
3. Activate a cell in the total row and select a summary formula from the drop-down list (see Figure 160-2). For example, to calculate the sum of the Actual column, select SUM from the drop-down list in cell D15. Excel creates this formula:
=SUBTOTAL(109,[Actual])
For the SUBTOTAL function, 109 is an enumerated argument that represents SUM. The second argument for the SUBTOTAL function is the column name, in square brackets. Using the column name within brackets is a way to create structured references within a table.
Figure 160-2: A drop-down list enables you to select a summary formula for a table column.
Note:You can toggle the total row display on and off by choosing Table Tools➜Design➜Table Style Options➜Total Row. If you turn it off, the summary options you selected are remembered when you turn it back on.
Using formulas within a table
In many cases, you want to use formulas within a table. For example, in the table shown in Figure 160-1, you might want a column that shows the difference between the actual and projected amounts for each month. As you’ll see, Excel makes this process very easy:
1. Activate cell E2 and type Difference for the column header. Excel automatically expands the table for you.
2. Move to cell E3 and type an equal sign to signal the beginning of a formula.
3. Press the left-arrow key to point to the corresponding value in the Actual column.
4. Type a minus sign and then press the left-arrow key twice to point to the corresponding value in the Projected column.
5. Press Enter to end the formula.
The formula is entered into the other cells in the column, and the Formula bar displays this formula:
=[@Actual]-[@Projected]
Figure 160-3 shows the table with the new column.
Figure 160-3: The Difference column contains a formula.
Although the formula was entered into the first row of the table, that’s not necessary. Anytime a formula is entered into an empty table column, it propagates to the other cells in the column. If you need to edit the formula, edit any formula in the column, and the change is applied to the other cells in the column.
Note: Propagating a formula to other cells in a table column is actually one of Excel’s AutoCorrect options. To turn off this feature, click the icon that appears when you enter a formula and choose Stop Automatically Creating Calculated Columns.
The preceding set of steps used the column names to create the formula. Alternatively, you can enter the formula by using standard cell references. For example, you can enter the following formula in cell E3:
=D3-C3
If you type the cell references, Excel still automatically copies the formula to other cells in the column.
Referencing data in a table
Formulas that are outside of a table can refer to data within a table by using the table name and column headers. You don’t need to create names for these items. The table itself has a name (for example, Table1), and you can refer to data within the table by using column headers.
You can, of course, use standard cell references to refer to data in a table, but using table references has a distinct advantage: The names adjust automatically if the table size changes by adding or deleting rows.
Refer to the table shown earlier, in Figure 160-1. This table was given the name Table1 when it was created. To calculate the sum of all data in the table, use this formula:
=SUM(Table1)
This formula always returns the sum of all the data, even if rows or columns are added or deleted. And, if you change the name of Table1, Excel automatically adjusts formulas that refer to that table. For example, if you rename Table1 as AnnualData (by using the Name Manager), the preceding formula changes to
=SUM(AnnualData)
Most of the time, you want to refer to a specific column in the table. The following formula returns the sum of the data in the Actual column (but ignores the total row):
=SUM(Table1[Actual])
Notice that the column name is enclosed in square brackets. Again, the formula adjusts automatically if you change the text in the column heading.
Even better, Excel provides some helpful assistance when you create a formula that refers to data within a table. Figure 160-4 shows the Formula AutoComplete feature helping to create a formula by showing a list of the elements in the table.
Figure 160-4: The Formula AutoComplete feature is useful when creating a formula that refers to data in a table.