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
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
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
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.
Search JabSto ::

Custom Search