Mixed Cell References in Excel Formulas

Advanced cell locking. Create powerful formulas by locking either the column or the row or both. So 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. And 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. 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 one, two 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 one to ten.

Now let me just make all these the same size. Now I'm going to add one plus one. I'll press ENTER.

You get two. 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 one plus one vertically. Now I'll copy the formula over to the right.

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. That's appropriate because I do want to move over column by column to pick up the other values.

Okay, so that's relative reference. Let's take a look at absolute reference. It's something we learned in level one.

I want to first come up with the total. I'll use the keyboard shortcut ALT equal, 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 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 and 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. Now something we don't go over in level one is what happens when you press F4 more than once.

I'll press F4 and the 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, so 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.

And there are certain situations where you might want that to be the case. 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. So let me start by taking the cell that's directly above and dividing it by the value in row 47. I'll press ENTER.

I'll 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. But 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 going to 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 then now I'll attempt copying the entire row and pasting it over to 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. 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-Tolino. I'll press F2. Now I did select the correct order ID, but the problem here is that I moved from column number four 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 13295. So I'll press F4 three times. One, two, three.

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 order date. So I'll press F4 once, twice and that's enough. I'll press ENTER.

Now if I auto fill to the right, I get the correct answer for order date and when I auto fill 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. 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 and 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. Now 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 auto fill to the right and see what results we get. That's looking good and let's auto fill 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. 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 auto fill them up or down and always get the right answer.