Microsoft Office Tutorials and References

In Depth Information

**Tip 75: Using Formulas with a Table**

Using Formulas with a Table

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 75-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 75-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 command and check the

Total Row check box.

3.
Activate a cell in the Total row and select a summary formula from the drop-down list (see

Figure 75-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.