Excel Advanced Cell Locking

Cell locking, or cell anchoring, is covered extensively in our NYC Excel classes. For those outside New York, find and compare the best Excel classes near you or live online Excel classes.

Advanced Cell Locking

Create powerful formulas by locking either the column or the row or both. This lesson is going to start with a little bit of remedial education about locking cells.

By default, when you're working with cells in Excel, they're relative. What that means is in a formula, if you move down a row, then the row number changes, and the address of the cell changes by one or more rows, depending on how far you move. If you move across, what changes is the column letter, the row stays exactly the same because you're in the same row, you're just changing columns.

Relative Reference

So in the formula, the address of the cell that relates to a specific column moves along as you autofill either to the right or to the left.

Now, I'll do a quick overview of what this looks like. I'll simply type 1,2 here. I'm going to copy those values over to the right. Then I'm going to autofill this down. And let's just stick with 1 to 10.

Now, let me just make all these the same size. Now I'm going to add 1+1. I'll press ENTER. You get 2. Now I'll autofill down, and we get the appropriate results. As I start to step down in the formula, you'll see that the row number is changing. That's the only thing that's changing as I move down. Everything is moving in unison. That's relative reference.

Now I'm going to take those same values, and I'm going to transpose them here. Now my addition is going to be 1+1 vertically. Now I'll copy the formula over to the right, and if I take a look at each individual cell, I'll see that what's changing is the column letter. That's the only thing that's changing, and that's appropriate because I do want to move over column by column to pick up the other values.

So that's relative reference.

Absolute Reference

Let's take a look at absolute reference. It's something we learned in Level 1. I want to first come up with the total. I'll use the keyboard shortcut ALT =, press ENTER, and now I have the total.

I want to figure out the percentage of each of the expenses as it relates to the total. The formula's going to be equal to the individual expense divided by that total. I'll press ENTER. So far, so good.

I'll use autofill, and I'll get error messages. If I take a look at what's going on when I go down one cell, it's what happened in that calculation. Everything is moving down one row.

So I need to clear this, and then go to the original formula and lock the total. The total should always be the same. I should not move further down. So I'll press F4, and I'll press ENTER. The total is the one value that's always going to remain the same, and I'll be able to get the appropriate percentage of the total if I take a look at the cells.

Now I'll see putting dollar signs on that cell definitely did make a difference.

Mixed Referencing

Now, something we don't go over in Level 1 is what happens when you press F4 more than once. I'll press F4 and dollar sign is just in front of the row number. F4 again, dollar sign is just in front of the column letter. I'll press F4 again, no dollar signs. If you continue to press F4, you'll cycle through the four different ways you can lock or unlock a cell reference or multiple cells in terms of the range.

All right. Those other variations are called mixed referencing. Why? Because you may want a cell reference to change when you move across. So A changes to B, but you don't want the row number to change when you move down. You want the position of that cell to stay the same when you move down, but it should change when you move left and right. And then you have the opposite. You want the value of the cell to change when you move up or down, but you don't want it to change when you move across. There are certain situations where you might want that to be the case.

Exercise 1

Let's move down and take a look at this exercise. I need to be able to figure out the percentage of the revenue, and I'm always going to need to divide the cell directly above with the revenue. The revenue is located on Row 47.

Let me start by taking the cell that's directly above and dividing it by the value in Row 47. I'll press ENTER. All use the shift key and select cells over to the right, and I'll use Control R to copy the formula over to the right. That's all well and good.

I want to save time by copying that row and then pasting it in Row 52, and then in Row 54, and then in Row 56. To my eyes, I think I am simplifying things because I don't have to rewrite the formula. I'm just copying what worked in one row and pasting it into another.

If I take a look at the values, or the cell references that are being used to get me the result, I'll notice I'm no longer on the revenue. I'll go down. Press F2. Still not on the revenue. It seems like every time I move down, everything moves symmetrically down. So I want to be able to have the revenue always stay the same.

Now, if I press F2, and then press F4 to lock C47, when I copy that formula over to the right I'm going to see my percentages change. Now, I'm letting you take a look at it right now. Press Control R.

So what is going on, what happened here? Let me press F2. Oh, if you lock the column and the row, well, then the column is never going to shift to the appropriate year, and so that's not going to work. I want the column to have the flexibility to shift, so I should not have a dollar sign in front of the column. But I do want a dollar sign in front of the row because it's always gonna be Row 47. So this is a mixed reference formula.

I'll press ENTER. Now I'll copy this over to the right, Control R, and now I'll attempt copying the entire row and pasting it over into Row 52, and then Row 54, and then Row 56. I get different results.

Let me double-check to see if these are good results. I'll press F2. That is the revenue for 2019, and that's what it should be in that formula. Let me check here. That is also the revenue for 2023. Let me check at the bottom row, F2. Yes, that is the revenue.

So in this case, by locking the row, I can use the revenue multiple times in a cell reference and not have to worry about that value shifting, but I haven't locked it in such a way that it prevents me from picking up the years to the right or to the left.

Exercise 2

Let's take a look at another example. I want to be able to use a VLOOKUP formula where I could write the formula once and then be able to reuse it.

Now this is set up a specific way. We're using the number directly above the header in this table to pull in the column index number. I'm not writing it inside the VLOOKUP. If I autofill to the right, that's not what I want. And if I autofill down, that's also not what I want. Let me take a look and see what's going on inside.

What I can see is that I am no longer selecting the order ID, so that's a problem. Let me take a look at what's happening when I go directly underneath Gorgonzola Telino. I'll press F2.

Now I did select the correct order ID, but the problem here is that I moved from Column 4 down to product name, and product name is not a number, and that should not go into the column index number. I should have actually stayed at Row 63, which contains the column index number.

Now I'll use mixed referencing. Now the table is locked and that's appropriate. But what I would like to do is make sure that the column doesn't move. The row has the flexibility to move, because when I move down, I want to be able to select 13 to 95.

So I'll press F4 three times. That will simply put a dollar sign right before Column B. Next I'll go to C63. Now I want the row to stay the same, so that's the only thing I want to lock. I don't want to lock the column or else I won't be able to pick up the column index number for OrderDate. So I'll press F4 once, twice, and that's enough. I'll press ENTER.

Now, if I autofill to the right. I get the correct answer for OrderDate, and when I autofill down, I'm able to pick up the appropriate values here. So that's an example of a mixed reference formula where you're locking both a column and a row.

Exercise 3

For our last example, let's pretend we're teachers and we're looking to create a multiplication table for our students. So what I'm going to do is multiply this value. Here's a rule of thumb that you can go by. If there's a value that you're trying to decide whether or not you should lock the column or the row, if that value is in a column, then I recommend locking the column.

So I'll press F4 once, twice, three times. Then I'm going to multiply that by the value in the row, the cell directly above. If you're trying to decide whether or not you should lock the column or the row, if that value is in a row, then you're going to lock the row. I'll press F4 once, twice.

When I press ENTER, I have my result. Now let me autofill to the right and see what results we get. That's looking good. And let's autofill down. And now I have my multiplication table.

If I take a look at any individual cell, I will be able to see that the row did not move and the column did not move because I locked the appropriate one for this formula.

Recap

So that's how you can use mixed referencing to create multiplication tables, but also create formulas that you don't have to rewrite. You only need to write them once, and then you can autofill them up or down and always get the right answer.