Join Dennis Taylor for an in-depth discussion in this video ADDRESS, ROW, and COLUMN functions, part of Excel Tips Weekly.
- [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.
Author
Updated
3/2/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 32m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: ADDRESS, ROW, and COLUMN functions