Microsoft Office Tutorials and References

In Depth Information

**AutoSum Tricks**

AutoSum Tricks

Just about every Excel user knows about the AutoSum button. This command is so popular that

it’s available in two Ribbon locations: in the Home

Editing group and in the Formulas

Function

➜

➜

Library group.

Just activate a cell and click the button, and Excel analyzes the data surrounding the active cell

and proposes a SUM formula. If the proposed range is correct, click the AutoSum button again

(or press Enter), and the formula is inserted. If you change your mind, press Esc.

If Excel incorrectly guesses the range to be summed, just select the correct range to be summed

and press Enter. It’s easy and painless.

Following are some additional tricks related to AutoSum:

h
The AutoSum button can insert other types of formulas. Notice the little arrow on the

right side of that button? Click it, and you see four other functions: AVERAGE, COUNT,

MAX, and MIN. Click one of those items, and the appropriate formula is proposed. You

also see a More Functions item, which simply displays the Insert Function dialog box —

the same one that appears when you choose Formulas

Function Library

Insert

➜

➜

Function (or click the fx button to the left of the formula bar).

h
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.

h
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 73-1,

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.

h
You can also access AutoSum using your keyboard. Pressing Alt+= has exactly the same

effect as clicking the AutoSum button.

h
If you’re working with a table (created by using Insert

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.

Tables

➜

➜

h
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.

h
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.