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.