AutoSum

Excel has an AutoSum feature that can save us lots of time when we are working with data tables. Let’s see a practical example. We have a monthly store sales table, like this:

AutoSum1

Now we want to put monthly total sales and a stores year to date total sales. For the monthly sales total, we could put a formula =SUM(B2:D2) on cell E2 and copy down. For the stores year to date total sales, we could put, on cell B7, a formula =SUM(B2:B7) and copy to the right. But all of this can be done using Excel’s AutoSum feature. This feature is so popular that Microsoft putted in two different locations of our menu bar. It can be found on the Home tab, on the Editing group (right side of the menu bar):

AutoSum2

and it can be found on the Formulas tab, on the Function Library:

AutoSum3

To use this feature on our table, and put all of the totals mentioned before, on one single step, we need to select the range of cells with the values and, here’s the trick, we need to select one more empty row of cells (for the stores year to date total sales) and one more empty column of cells (for the monthly total sales). In this example, we need to select the B2:E7 range of cells. After that, let’s click on one of the AutoSum buttons mentioned before. The result will be like this:

AutoSum4

Now we have all of the totals that we wanted. To make this operation even easier to do, instead of clicking on one of the menu buttons, we can use the shortcut keys Alt+= (press Alt key and then the Shift and the key with the = symbol). The result will be exactly the same.

This AutoSum feature can be use to put totals in other ways. For example, if we select only the range of cells with the values (no empty rows or columns), in this case if we select cells B2:D6, and we click on the AutoSum button, we will get only the column totals, placed beneath the first available row below our data, in this case, on row 7. The result will be like this:

AutoSum5

This AutoSum button can be used, instead of sum’s, for other functions like average, count number, etc. You can check the available options clicking on the little arrow on the lower part of the AutoSum button:

AutoSum6

As you can see, the feature is very useful, and very easy to use. It will certainly save you lots of time, when working with data tables.

0 comentários: