If you look in the active cell or the formula bar, Excel will suggest the formula =sum (A5:C5). Confirm the formula by pressing the Enter key. How, did AutoSum determine what cells needed to be calculated? It will first determine if there are any numbers above the active cell (where the cursor is located). In this case, the cell D4 is empty. AutoSum will then try to determine if there is a number in the cell located to the left of the active cell. In the case of this exercise, it will find the number 3 in the cell C5. It will continue doing this process until in encounters an empty cell or the border of the worksheet.
Press the Esc (Escape) key.
Do not confirm the formula. If you have done so, delete the formula in the D5 cell.
Place the cursor in the E5 cell.
Press the Autosum button.
Autosum will now suggest =sum(E1:E4). Why did it select that range of cells? It knows that most users place an empty row or column between the numbers and the totals. That is why AutoSum continues to look for cells with numbers until it finds one above or to the left of the active cell. It will always look towards top first, before looking to the left.
Press the Enter key to confirm the formula.
The total of these numbers is 24.
The Autosum trap
As practical as this function is, you must be pay attention to the range of cells that AutoSum will suggest. It may not be what you want.
Delete the content of the B5 cell.
Place the cursor in the D5 cell.
Press the Autosum button.
AutoSum will now suggest the formula =sum(C5). Why is it not suggesting a range of cells from A5 to C5? AutoSum stop as soon as there is an empty cell (like the B5 cell) or one with text. That is why AutoSum is asking you to confirm you the sum of only one cell. It will suggest a wrong range of cells if you forget to put in a number.
Always check the range of cells suggested by AutoSum. If you disagree, all you have to do is select the range you need by using the mouse or the cursors and the Shift key.
Press the Esc (Escape) key.
Apply AutoSum to many rows and columns
You can also apply many AutoSum at the same time. It's faster to apply them to many rows and columns.
Select the range of cells from E8 to E10.
Press the Autosum button.
AutoSum will immediately do the sum of the rows to the left of the selected cells. Since you selected many cells in a single column, AutoSum believes you want to add the cells of each row. The reverse happens if you select a range of cells on the same row. AutoSum will try to add the cells above each selected cells.
Select the range of cell from A12 to C12.
Press the Autosum button.
You now know everything there is to know about this very powerful option. Use it wisely. |