Excel - Exercise on AutoSum

Introduction
Before we start
Autosum
The Autoexcel2007autosumtrapsum trap
Autosum with many rows and columns

Introduction

Without a doubt, the most often used command for a beginner is the Autosum command. After all, we all need to know the sum of rows or columns. This function was first introduced in Excel 3.0. It revolutionize how people create models. Soon after, the competition (Lotus 1-2-3 and Quattro Pro) had no choice but to create the equivalent command for their product.

Before we start

To do the following exercise, you can use the file demos-excel2007-1.xlsx on the demostrations files web page.

Select the AutoSum worksheet.

Autosum

Select the AutoSum worksheet.

Excel 2007 : Modèle used pour the exercices de somme automatique

If you unable to find worksheet, enter the number in the appropriate cells, see table above.

Place the pointer in the D5 cell.
Press the Autosum button.

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.

Excel 2007 : Plusieurs somme automatique en même temps

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.

Excel 2007 : Dernière screen avec la commande somme automatique

You now know everything there is to know about this very powerful option. Use it wisely.

You like what you read? Share it with your friends.

This site is hosted by 1&1.com