Join Dennis Taylor for an in-depth discussion in this video Using the entire row/column references, part of Excel 2010: Advanced Formulas and Functions.
If you are working with large amounts of data--as in the worksheet that we see here called ColumnRef, it's in the file called Ch 1 Tips-- as we look at the data here, and it's about 700 rows worth of information, it's not uncommon to want to see totals of what's in each column--or at least certain columns. Certainly one way to find this information quickly is merely to click the column letter. And I just click column F as you can see here. At the bottom of the screen, you will see the Average salary, Count, Numerical Count, Minimum, Maximum, and Sum.
Now these don't appear here by magic. It's likely that you are seeing at least some of them--possibly all of them. If you right-click the Status Bar, you will see a section here where these various items here that are tabulating the information are displayed. If some of these don't appear very useful to you, or if you are thinking you wouldn't use them that often, just uncheck them, and they won't be there anymore. And you can, at different, times change your mind about what's important. I think the idea here might be which ones would you like to see most of the time? Fine. Now these will appear at the bottom of the screen anytime you highlight two or more cells.
Many times, you'll just ignore what's there. But there are also times when you need actual formulas in a worksheet-- summary-type information. For example, in column I, what we might like to see here--because we use this list a lot--the list will grow and shrink at different times, and whenever we are looking at the top portion of this worksheet, we want to see Total Salaries in Years, perhaps some Averages, and maybe some other stats as well. So let's set up a formula here to add up these salaries. In other words, we don't need to click on column F all the time; this is going to be here consistently.
Easy way to do this is with the AutoSum function. You'll find it on the Formulas tab in the Ribbon on the left side. Or on the Home tab, you will see it on the right side. There is AutoSum. Click it once to save yourself some typing-- even though this function typically does guess what you want to do. Here it's not doing a good job of that. We want to add up the data in column F. Now the standard way to do this is to start highlighting the cells here. And surely you could imagine situations where you are thinking, "Well I have got 40,000 rows here. This is going to take forever." Well, rather than doing this, I am going to press Escape. Start over here again.
Let's press AutoSum and simply click column F. The notation F:F refers to the entire column. Even F1 is relevant here. We don't worry about it. Enter. We have got our total. There it is. And the function as we see it here: short, quick, easy. We didn't have to refer to cells F2 down to F783, or whatever it might be. We didn't have to do any dragging. We simply clicked column F. It works beautifully. And certainly we could do this on other columns as well. Here is AutoSum. For the Years, click column E, and so on.
If we want averages, take advantage of the fact that AutoSum is accompanied by a drop arrow. We'll click it. Let's choose Average this time. We also have Max and Min out here. We'll choose Average. Once again, it's guessing wrong. Don't worry about that. We want the Average salary. Let's click column F. Same thing with Years, same thing with Performance; same general idea. Now occasionally, but very rarely--and I do use Excel a lot, and I almost never use this--but you could imagine if we can refer to an entire column, we can also refer to an entire row.
I think you can see how ridiculous it would be in this worksheet and in most worksheets, but you certainly could imagine situation where if you needed to add up a bunch of columnar data out of row 11 here, you might choose AutoSum and click row 11. It would look like that. That is a valid construction. It wouldn't make a lot of sense here, but in some situations it would. It means add up all the data in row 11, all the numerical cells. We wouldn't use that in this situation really, but it's just to point out that the construction is valid, and it will save you time here and there, but very rarely.
The focus, again, in this particular shortcut--and it saves you a ton of time-- it's just the idea that when you are tabulating information in a column, there are many times when you might as well refer to the entire column, rather than highlighting or typing in the addresses. The other distinct advantage of this-- particularly if this list grows--we don't have to rewrite any of these formulas. This means the entire column F, down over a million rows. If we take out rows, we don't have to rewrite the formula.
We don't have to make any of those adjustments. So this is by far the easiest way to set up formulas for columns. Now what if there is a total on the bottom? I am going to press Ctrl+Down Arrow here and put a total on the bottom of the Salaries. Just by clicking AutoSum twice. There we go. That's one way to do it. I'll press Ctrl+Up Arrow. We are back here. You remember this total before? It was 39,000,000; now it's 78,000,000. So for the moment obviously, this is not acceptable. But you would know in a situation like this that there is a total at the bottom.
So in these kinds of situations where you needed to have the totals at the bottom, it would be simply a question of dividing this by 2. A different scenario might play out where you had interim subtotals, perhaps at the end of each department, something like that. If you have those in place without a grand total, there too you would divide by two. If you have a grand total as well here, you'd divide by four. So you would certainly have to make some adjustments in those cases. But again, the key idea here is in formulas where you need to tabulate columnar information, many, many times using the entire column reference makes good sense.
It's fast, it's easy, and it's very clear once you get used to the notation.
- Referencing, copying, updating, and converting formulas
- Using the logical functions and creating compound logic tests
- Searching for and matching data based on specific criteria
- Reconfiguring cell data using text functions
- Calculating dates, times, and days of the week
- Analyzing mathematical and financial data
- Using the power functions, COUNTIFS, SUMIFS, and AVERAGEIFS
- Working with rounding functions
- Returning cell references