Part II: Working with Formulas and Functions

Cross-Reference

See Chapter 27 for more information about linking workbooks. n

Tip

To create formulas that refer to cells not in the current worksheet, point to the cells rather than entering their references manually. Excel takes care of the details regarding the workbook and worksheet references. The workbook you’re referencing in your formula must be open if you’re going to use the pointing method. n

Note

If you point to a different worksheet or workbook when creating a formula, you’ll notice that Excel always inserts absolute cell references. Therefore, if you plan to copy the formula to other cells, make sure that you change the cell references to relative before you copy. n

Using Formulas in Tables

A table is a specially designated range of cells, set up with column headers. In this section, I describe how formulas work with tables.

Cross-Reference

See Chapter 5 for an introduction to the Excel table features. n

Summarizing data in a table

Figure 10.10 shows a simple table with three columns. I entered the data, and then converted the range to a table by choosing Insert Tables Table. Note that I didn’t define any names, but the table is named Table1 by default.

FIGURE 10.10

A simple table with three columns of information.

214

Chapter 10: Introducing Formulas and Functions

On the CD

This workbook is available on the companion CD-ROM. It is named table formulas.xlsx.

If you’d like to calculate the total projected and total actual sales, you don’t even need to write a formula. Simply click a button to add a row of summary formulas to the table:

1.Activate any cell in the table.

2.Place a check mark next to Table Tools Design Table Style Options Total

Row.

3.Activate a cell in the Total Row and use the drop-down list to select the type of summary formula to use (see Figure 10.11). 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 creates “structured” references within a table. (I discuss this further in the upcoming section, “Referencing data in a table.”)

FIGURE 10.11

A drop-down list enables you to select a summary formula for a table column.

215

Part II: Working with Formulas and Functions

Note

You can toggle the Total Row display via Table Tools Design Table Style Options Total Row. If you turn it off, the summary options you selected will be displayed again when you turn it back on. n

Using formulas within a table

In many cases, you’ll want to use formulas within a table to perform calculations that use other columns. For example, in the table shown in Figure 10.11, you may want a column that shows the difference between the Actual and Projected amounts. To add this formula:

1.Activate cell E2 and type Difference for the column header. Excel automatically expands the table for you to include the new column.

2.Move to cell E3 and type an equal sign to signify the beginning of a formula.

3.Press the left arrow key. Excel displays [@Actual], which is the column heading, in the Formula bar.

4.Type a minus sign and then press left arrow twice. Excel displays [@Projected] in your formula.

5.Press Enter to end the formula. Excel copies the formula to all rows in the table.

Figure 10.12 shows the table with the new column.

FIGURE 10.12

The Difference column contains a formula.

Examine the table, and you find this formula for all cells in the Difference column:

=[@Actual]-[@Projected]

216

Chapter 10: Introducing Formulas and Functions

Although the formula was entered into the first row of the table, that’s not necessary. Any time a formula is entered into an empty table column, it will automatically fill all the cells in that column. And if you need to edit the formula, Excel will automatically copy the edited formula to the other cells in the column.

Note

The “at” (@) symbol that precedes the column header represents “this row.” n

These steps use the pointing technique to create the formula. Alternatively, you could have entered the formula manually using standard cell references rather than column headers. For example, you could have entered the following formula in cell E3:

=D3-C3

If you type the cell references, Excel will still copy the formula to the other cells automatically.

One thing should be clear, however, about formulas that use the column headers instead of cell references: They are much easier to understand.

Tip

When you add a formula to a column in a table, Excel displays a SmartTag. To override the automatic column formulas, click the SmartTag and choose Stop Automatically Creating Calculated Columns. Use this option if you need different formulas for different rows within the table. n

Referencing data in a table

Excel offers some other ways to refer to data that’s contained in a table by using the table name and column headers.

Note

Remember that you don’t need to create names for tables and columns. The table itself has a range name, which is provided when you create the table (for example, Table1), and you can refer to data within the table by using the column headers — which are not range names. n

You can, of course, use standard cell references to refer to data in a table, but using the table name and column headers has a distinct advantage: The names adjust automatically if the table size changes by adding or deleting rows. In addition, formulas the use table names and column headers will adjust automatically if you change the name of the table or give a new name to a column.

Refer to the table (Table1) shown in Figure 10.11. To calculate the sum of all the data in the table, use this formula:

=SUM(Table1)

217