Microsoft Office Tutorials and References

In Depth Information

**Tip 28: Learning Some AutoSum Tricks**

Following are some additional tricks related to AutoSum:

➤ If you need to enter a similar SUM formula into a range of cells, select the entire range before

you click the AutoSum button. In this case, Excel inserts the functions for you without asking

you — one formula in each of the selected cells.

➤ To sum both across and down a table of numbers, select the range of numbers plus an

additional column to the right and an additional row at the bottom. Click the AutoSum button,

and Excel inserts the formulas that add the rows and the columns. In Figure 28-2, the range

to be summed is D4:G15, so I selected an additional row and column: D4:H16. Clicking the

AutoSum buttons puts formulas in row 16 and column H.

➤ If you’re working with a table (created by choosing Insert
➜
Tables
➜
Table), using the AutoSum

button after selecting the row below the table inserts a Total row for the table and creates

formulas that use the SUBTOTAL function rather than the SUM function. The SUBTOTAL function

sums only the visible cells in the table, which is useful if you filter the data.

➤ Unless you applied a different number format to the cell that will hold the SUM formula,

AutoSum applies the same number format as the first cell in the range to be summed.

➤ To create a SUM formula that uses only
some
of the values in a column, select the cells to be

summed and then click the AutoSum button. Excel inserts the SUM formula in the first empty

cell below the selected range. The selected range must be a contiguous group of cells — a

multiple selection isn’t allowed.

Figure 28-2:
Using AutoSum to insert SUM formulas for rows and columns.