This content requires Flash

To view this content, JavaScript must be enabled, and you need the latest version of the Adobe Flash Player.

Download the free Flash Player now!

You use File Open and we will choose the AutoSum Tricks. Now, I can hear what you are saying. You are saying, "AutoSum? That should not be in Advance Excel." But bear with me and I will bet you will learn a few things. Now, Excel includes the AutoSum icon twice. It is once on the home ribbon out on the right hand side and it is also on the formulas ribbon as a great, big, huge icon. Let us use that one for now. To use the AutoSum button, you select one cell immediately below a column of numbers and hit the AutoSum button. So I will select cell B14 and hit AutoSum and Excel proposes a formula; sum B2 to B13. If that is the right formula, I choose Enter or Ctrl Enter to accept the formula.

AutoSum works with the column of numbers, it also works going across the row. So if I go to cell F2, select the cell and hit the AutoSum button, Excel will figure out that I am trying to sum those columns. Now an interesting here is that AutoSum works in cell F2, it also works in cell F3. However, now in cell F4 we have a quandary. Excel does not know if I want to total the four cells immediately to the left or the two cells immediately above. Excel is so fond of totaling down that when I hit the AutoSum button here it does the wrong thing. It totals the two numbers above. When that happens, when Excel proposes the wrong range, you need to take the mouse and highlight the correct range. And choose that, you are good to go.

Well, that is the way that most people know how to use AutoSum. I need to talk about a few gotches; things you have to watch out for. If we scroll down so we can see that next dataset. In this case, instead of quarters; Q1, Q2, Q3, Q4, I have years; 2004, 2005, 2006, 2007. Well, to Excel, those numeric years look just like the numeric data. For example, in cell B19, if I select cell B31 and hit the AutoSum button, I can either use the AutoSum button or the shortcut key which is Alt Equals, now Excel proposes a formula that is completely wrong. It is proposing a formula that includes the year heading. This could be a big problem if you are not paying attention and you just accept the AutoSum so you should be aware of when Excel is not going to get the AutoSum right and correct it using the mouse. In this case, I am going to select from B19 down to B30. Good enough.

Let us scroll down again. I am going to show you a great trick where we can enter all of our AutoSum formulas at once. Rather than selecting a single cell, I am going to select all four cells underneath my total area. Hit the AutoSum button and instead of proposing a formula, Excel automatically enters all four AutoSums for me. Now you will notice here that I have Q1, Q2, Q3, Q4 as headings instead of numbers so I knew that it was going to work. Pretty amazing is not it?

Let me show you something even more amazing. I am going to hit Ctrl Z to undo. I have no clue who was brave enough to try this the first time or why they even thought it would work. But watch this trick. I am going to select all of my numeric cells plus one extra column, plus one extra row. Now, you would think that hitting the AutoSum button here would completely destroy all of my numbers. But Microsoft Excel is smarter than that; it adds the AutoSums all along the right hand side and all along the bottom. Amazing! I think that it is pretty cool that Microsoft thought far enough ahead to program that in.

Alright! Let us scroll down again. You know that we have been using AutoSum to add sums. But what if we need to create an average; the average revenue report? If I select this range, you will notice that the AutoSum button is not just an AutoSum button, but there is a dropdown underneath the AutoSum button. Let us see what is under the dropdown. In addition to sum, we can AutoAverage, AutoCount, AutoMax or AutoMin. I am going to choose average from the dropdown and Excel will create average formulas instead of sum formulas.

Okay. Now, I have one more AutoSum trick for you, but we have to go on to the next file. I am going to use File Close to close this file. And then use File Open to open the

You use File Open and we will choose the AutoSum Tricks. Now, I can hear what you are saying. You are saying, "AutoSum? That should not be in Advance Excel." But bear with me and I will bet you will learn a few things. Now, Excel includes the... click to read more