ADDRESS, ROW, and COLUMN functions

- [Voiceover] Excel has three functions related to cell location: Address, Row, and Column. And they're unusual, and you probably won't need them very often, but occasionally they can come in handy. I'm looking at this list here, I certainly could sort the list by Comp., but I want to keep it in the current order. I'm curious as to what the highest compensation amount is, so I'll simply type, for example, in cell K2, =max, meaning maximum or highest value, and rather than highlighting the cells in column H, I'll simply click column H and Enter. And by the way, if you click column H, probably you're seeing the same number down on the status bar, provided you have these entries available.

If you don't, you can right-click in in the status bar and make sure these entries right here are checked. You may not always want to keep these checked. It just depends upon what you're interested in and how you're using the status bar. Nevertheless, when you highlight data, and that includes clicking a column, you're likely to see that entry down there. And it matches up, we know what that is. But sometimes, we want to know where this exists. Where does this number exist? We're assuming that each of these entries here is unique, the compensation amounts in column H. So how can we track that down? Possibly by way of the Address function, =address( Now, cell addresses begin with column letters, and yet what we're about to put in is a row reference.

That might strike you as a little bit odd. Throughout Excel, in various functions that use row and column references, it's always row first, then column. Although when we refer to cells, it's the reverse. We are currently in cell K3. We use the column first. So here we are with the Address function. We're trying to figure out the row number, and we can do this by way of Match right now. We're going to match this entry right here, comma, with column H, that's where those compensation amounts are found, comma, and we want an exact match, so we put in zero.

So this is going to give us the row number, comma. Now to get the column number, as we're putting in column here, we actually click column H, right parenthesis and another right parenthesis, and we see that it's in cell H11. Now that happens to be on our screen. Perhaps that's a coincidence, but we can see it, and sure enough, there it is right there. There's the number. Possibly you saw it ahead of time, but this is a large list. It could have been well down the list. This tells us that it's in H11. Imagine, for the sake of argument, this were in row 28 or 37, or 300, or whatever.

At least we found that location. Now, with that information, possibly we wanted to see the name of the person, so let's essentially do this all over again, but with a slight variation. I'm going to copy all of this here, except for the equal, Control-C, Escape, go down here, type equal, paste it in. Now, if we found this bit of information here in a certain cell, what if we take the column reference and move leftward? If we're in column H, and we want to pull the data out of column A, we move one, two, three, four, five, six, seven columns to the left.

So I'm putting in a minus seven out here, and then pressing Enter, and the address that we're getting now is the address of the person. It's A11, and of course that makes sense. We can see it, and you can see the name ahead of time. It's Rick Gallegos out here. But if what if we ultimately had wanted to get the name of the person without having to scroll up and down? And remember, we're in a situation where maybe we don't want to sort, or we can't sort right now. We can use a function called Indirect, =indirect. When Indirect refers to a cell reference, it then uses that cell reference to get the data, so there's Rick Gallegos.

So pulling this all together, we could have taken this information here and simply copied it. And we'll highlight all this, press Control-C and Escape, pop it back into here, Control-V and Enter. For the moment, I'll just hide some columns so we can see this, I'll hide these columns right here, and re-display that that way. So I doubt if you'd use it quite in this way, but I think you can begin to understand how occasionally this has some uses for figuring out where information is. We could use other functions in here as well, too. For example, looking for the second highest entry here.

How do you get the second highest entry? Not with a Max function, but a function called Large, and we're looking in column H, comma two, if you want the second largest, comma three for the third largest and so on. So the second largest entry is 119,000. That's found in cell H25, that's Lisa Hunter. Remember, we're using that information that we got out of cell K1. So you can begin to see some potential other uses for this. And scrolling down there just to make sure, in row 25. Let's see down in row 25, there she is, Lisa Hunter, and that's the second highest compensation amount in our list here, 119,400, again by way of the Large function.

Now, there are also functions called Row and Column. We saw Column used in here, just initially. Another use of this potentially could be, and I'm going to get rid of this data completely. Maybe what we want to have on this worksheet is every fifth row, every seventh row, every tenth row, whatever, to be a different color. Maybe 10 would make sense here. I'll bring back all the columns, click in the upper left corner, double-click any column boundary. That's one way to do it. I'm going to select the entire worksheet. Look up here. In other situations, I might just want to highlight this data, but I'd like to highlight every tenth row, so I could either highlight the data this way first, or click in the upper left corner.

If I apply the feature this way, it will go all the way across the worksheet. So Home tab, Conditional Formatting, New Rule. Use a formula to determine which cells to format. Here's what we'd like to be able to do. We'd like to make every tenth row be a certain color, and we'll be using two functions here. The main function is Mod, m-o-d. Mod calculates the remainder in the division. You might be saying, "What does that have to do with all this?" The Mod function says I've got a number somewhere.

Well, we're going to use the Row function right now to pick up the current row. And if we put in left parenthesis, right parenthesis, it'll work for any cell, so suppose it's row seven. What the Mod function says is, "I've got a modular factor." Say it's 10. I'm going to take the row number and divide it by 10 and look at the remainder. If the remainder is zero, that means the row number is 10, 20, or 30, 40, 50. If it happens to be row seven, the remainder is seven. If it's row 17, the remainder is seven, but when it's zero, I want a special format here, and maybe I'll use bright yellow, maybe I'll use green, whatever.

Just try a color, see if that's too heavy. It might be. Click OK, click OK, and we've got yellow every tenth row. Maybe every fifth row would be better. We could easily jump back in there, click in the upper left corner, back to Conditional Formatting. We manage the rules if we want to come back and either change them, get rid of them, or in this case, edit them. Click Edit. I'll change the 10 to a five. So what's going to be happening now? For every single cell in the worksheet, we're going to look at its row number and divide it by five. If the remainder is zero, that means it's evenly divisible by five, we'll make that row yellow.

And here it is. Now similarly, we could have used the word Column. We typically don't think of columns as column numbers, but we don't actually have to put in the column anyway. So one more time going back here, I doubt if you want to put it in both. Probably wouldn't be a great idea, but let's change the rule, manage the rule. How about every other column, or every third column. Edit the rule, and I'll change Row here to be Column now, not Row, and I'll make it be every third column. So instead of comma five, comma three. And click OK and OK, and we'll see how that looks.

Probably not as good, but this is going to work all the way across the worksheet. And earlier, although I didn't show it, the same was true all the way through the worksheet in terms of rows. What this means, too, if I insert a new column, the Department column is not going to be yellow anymore, the Building column will be yellow if I insert a new column to the left here. As I right-click column B and Insert, the Building column is yellow, so it's every third column no matter what. If I delete this now, then the Building column will move into column B, it will not be yellow, Department will move back where it was, and sure enough, it will be yellow.

So some different uses here of, as we just saw, the Column function, prior to that the Row function, and also the Address function.