Microsoft Office Tutorials and References

In Depth Information

**Working with Tables**

Although I entered the formula into the first data row of the table, that’s not necessary. Any time

you enter a formula into any cell in an empty table column, it will automatically fill all the cells in

that column. And if you need to edit the formula, edit the copy in any row, and Excel

automatically copies the edited formula to the other cells in the column.

The preceding steps use the pointing technique to create the formula. Alternatively, you can

enter the formula manually using standard cell references. For example, you can enter the

following formula in cell E3:

=D3–C3

If you type the formulas using cell references, Excel still copies the formula to the other cells

automatically: It just doesn’t use the column headings.

When Excel inserts a calculated column formula, it also displays a Smart Tag, with some

options, one of which is Stop Automatically Creating Calculated Columns. Select this

option if you prefer to do your own copying within a column.

Referencing data in a table

The preceding section describes how to create a column of formulas within a table. What about

formulas outside of a table that refer to data inside of a table? You can take advantage of the

structured table referencing that uses the table name, column headers, and other table elements.

You no longer 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 the structured

table referencing has a distinct advantage: The names adjust automatically if the table size

changes by adding or deleting rows.

Refer to Figure 9-13, which shows a simple table that contains regional sales information. Excel

named this table Table2 when it was created; it was the second table in the workbook. To

calculate the sum of all the values in the table, use this formula:

=SUM(Table2)

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 the table, Excel adjusts all formulas that refer to that table

automatically. For example, if you rename Table1 to be Q1Data, the preceding formula changes to

=SUM(Q1Data)