From the course: Excel 2016: Advanced Formulas and Functions

Using entire row and column references

From the course: Excel 2016: Advanced Formulas and Functions

Start my 1-month free trial

Using entire row and column references

“

- If you're about to tabulate information in rows, you can possibly in some situations use entire row references. In this worksheet called Row References I'm about to put a total in cell B16, to add up all the data for the Boston and New York rows. We might be adding other items out here in columns N and O and P. But for the moment, we're only thinking of this data right here, and the data right below it. And we can refer to those cells, of course, in a formula. But why not refer to the entire row? It's going to make setting up the formula easier. And it's easier to read. Equal Sum. You can also press Alt-Equal to get that started. Ignore what Excel is suggesting. I'm about to drag across rows 3 and 4, just the row numbers. And the notation might look a little bit strange. Three colon four means, as you would guess, all the data in rows three and four. Enter. We have an answer. If you're not sure about that, you can highlight the cells. And then look in the Status bar at the bottom of the screen, and confirm that that total is correct. Now the only caveat here is, just make sure that you that you don't have any other data off to the right of the existing cells that we're currently seeing. If we do add more items out here, in the sales numbers, those numbers will automatically fall into place, and our formula will automatically pick them up, because it's an entire row reference. Now, for Midwest, the two cities involved are Chicago and Cincinnati. But they're not adjacent to one another. So here, after pressing Alt-Equal, put in Auto Sum. Let's click row 6 for Chicago, then a comma, then click row 11 for Cincinnati. And that looks a little bit strange too. But it does mean, in a similar way that we saw in the previous example, let's add up all the data from row 6 and all the data from row 11. It certainly looks different than the other example. But we're doing the same kind of thing. Two different rows. They just happen not to be adjacent to one another. And here too, we have an answer. And if we needed to verify that, highlight the Chicago numbers, and then, using the Control key, highlight the Cincinnati numbers. There we are. And we see that same total in the lower right-hand corner. 34,434, that we see in cell B17. And one more here. Auto Sum again. Alt-Equal. And the southern cities would be Atlanta, Row 5, click there, comma, then Houston in row 7, comma, Dallas in row 10. There too, that same unusual look, but we get the job done in the same kind of way. Probably more common are going to be column references. And when you use column references, again, we're thinking about the entire worksheet here. So before proceeding with any formulas that might use entire column references, know your data well enough to say, for example, that if you're going to tabulate Total Salaries, you want to make sure there's nothing else in column G except salaries. No interim totals, no grand total on the bottom. I'll put these out in column L. Once again, Alt-Equal to get started with Auto Sum. Ignore the prompt. Simply click column G. And there we go. Enter. That too has the distinct advantage of, if this list grows or shrinks, makes no difference, this formula does not need to be altered. We will still have our totals correctly. If we're putting in other functions, we can certainly do that too. Average, left parenthesis, click column G. That's all we need to do. Enter. Median. Similarly. Just like an average. Slightly different in meaning, but similar. There we are. So we can easily use column references. And we can use these with VLOOKUPs and other kinds of functions as well too. In all cases, you must know your data well enough to know that when you are using the entire column reference, like here, column G, there's nothing there but the numbers. The text, of course, in G1 is there, but it gets ignored. But any other interim values need to be gotten rid of. If you had only a total on the bottom, and nothing else, you could divide this by two. But the idea here of this facilitating growth of the rows wouldn't exactly apply. Nevertheless, a more efficient way to write certain formulas. Using entire column references. And then the earlier, although less likely to be used example, row references.

Contents