What you will learn:
to use AutoSum to sum a column
to use AutoSum to sum a row
to change range references in a formula
budget.xls at Budget sheet from resource
- Select the cell B14, which is blank except for the green fill.
We will pretend that this is a good place for some new totals.
- Click the AutoSum button.
Excel guesses that you want to add the numbers above the selected cell up to the next blank
cell, B6. It surrounds the range B7:B13 with a blinking dashed
The formula is displayed both in the Formula bar and in the cell. It will overlap other cells if
necessary while you are working.
All formulas must begin with
=, the equals sign.
While the blinking dashed border surrounds the cells, clicking or dragging will
change the formula, using the cell references of the cells you clicked or dragged. It can really foul up your calculations to click without careful thought. Pressing
ESC will remove this blinking border and cancel your actions.
Press ENTER. The sum of the range is displayed in cell
B14 instead of the formula.
The cell below (B15) is selected instead of B14.
The selection moves down one
cell whenever you use ENTER to register data in a cell.
AutoSum: Drag to change range
If Excel guesses incorrectly, you can change the cell references.
- Select cell B14 again and click
the AutoSum button.
- Drag from B10 to B13 and release the mouse button. The cell references in the formula change.
Notice the popup tip that shows while you are dragging. It says that you have selected a region that is 4 rows high and 1 column wide.
- Press ESC to cancel the AutoSum action without accepting the formula.
DELETE to leave cell B14 blank again.
AutoSum works for rows also.
- Select cell N7. In the cell you see a number, but in the
Formula Bar you see the formula that was used to calculate the number,
- Press DELETE to remove this formula.
- Click the AutoSum button. Excel guesses that you want to add the values in the cells to the left, with range reference
B7:M7. You will
probably have to use the horizontal scroll bar to see how far to the left the blinking border extends.
Click the green check mark on the Formula bar. The sum is entered, but cell N7 remains selected. If you had pressed
ENTER, the selection would have moved to the cell below.
AutoSum: Type to change range
- Select cell N7 again and click the AutoSum button. The formula is shown in the Formula bar.
- Click in the Formula bar. You can now change the formula yourself.
- Edit the formula to read =SUM(B7:M7,C9:J9, D11:L12)
This makes no sense in this case, but we are only practicing.
the checkmark button at the left of the Formula Bar. Your formula is accepted
and the selection remains in cell N7.
- Click in the Formula bar again. Colors!
Each range in the formula has a different color. A matching color border surrounds the cells themselves. This can really help you see what is happening.
Only when you are editing the cell contents will you see these colored references and borders.
- Press ENTER to accept this odd formula. The value shows in the cell
and your selection moves down one cell.
- Click on the Undo button twice to return the formula to the original