How to use mixed cell references

- [Presenter] When you write formulas in Excel many times you're using what are called absolute references and if you've never had the need for an absolute address or a mixed address you might not be familiar with the term. In this worksheet we're gonna put in a New Salary in column I. Let me zoom in on this. I'll hold down on Control, use the mouse wheel. Everybody is going to get the 3.1% increase. I need a formula here and so I2 for the first person then I'll copy it down the column and in this formula I will be using cell K1.

But I wanna be able to write the formula so that I can use K1 repeatedly here and if you've never seen this you wouldn't have any instinctive idea as to what to do at first. Start with the formula here. You can write this in any number of different ways. Equal H2 times this amount and it's in K1. Now even before we finish the formula if you've worked with formulas I think you would know that if we're going to be copying this down the column that formula as we see it right now is going to reach for the next cell downward, H3 times K2 and then H4 times K3 and so on and so on.

We want this K1 reference to stay the same. We want it to be absolute not mixed but absolute. Now, we can type in dollar signs, putting a dollar sign in front of the K in front of the one but we can also press the function key F4. And I do stress you would not know, if never having been allured to this concept, what that really means. I've heard people mistakenly assume that means dollar sign because for example we're dealing with salaries. It has nothing to do with the concept of currency. It simply means if this formula is copied, this reference will not change.

In a strict sense, the dollar sign is actually in two places and it really means the following. It means when we copy this formula the dollar sign in front of the K means don't change the column reference and the dollar sign in front of the one means don't change the row reference. But if we're going to be copying this down the column Excel will always keep this letter K as we copy this down the column because we're always two columns removed from column K. So we don't really need this dollar sign but on the other hand it doesn't hurt.

So you could take it out, you could delete it, press the Delete key but you can also press the function key F4 repeatedly and by doing this you see different variations. So, not to confuse the issue exactly, in the example here we certainly could be using the absolute address, nothing wrong with that but we could also be using this address. So for the quick fix you would just press F4, that looks good, and let's complete the formula example here with this and to make this slightly faster I will not press Enter but I will press Control + Enter.

That simply keeps the active cell from moving down into the next cell. Keeps the active cell in place, Control + Enter. And we'll double-click to copy this down the column and just to make sure it went all the way down press Control + period, that takes us down to there. Is that the bottom? If you weren't sure scroll a little bit more. Control + Period takes us back up top. If we had more time we'd talk about rounding. That probably isn't exactly round. Keep in mind if we do click column I and increase the decimals you'll see that's the real value and eventually we would clean that up with the Round function.

But let's move to the next worksheet here and here's a more complex example. I've got some items over in column A. This goes down to about row 89. So we've got about 85 entries here and we want to distribute these items to our different warehouses in the East, Midwest, South, Mountain, and Pacific. These numbers here add up to 100%, you either do the math in your head or you'll probably see it in the status bar at the bottom of the screen. Now just looking at cell C4 only for the moment, let's zoom in.

If we were writing the formula for this cell only it's equal and we're gonna multiply those two cells. It makes no different which comes first so we could be typing for example or clicking on C3 times B4 and that's all we need to do. But as soon as we start to copy this down the column or copy it across the row we will have problems. What this formula really says, expressed based on location, is multiply the cell above by the cell to its left and where do we wanna do that? Really in this cell only.

Just for the right for example we wanna multiply the cell above by the cell that's two cells to the left and so on. But thinking how we might write these individually, by no means do we wanna do that because we'd have over 400 formulas here. We got about 85 rows, we got five across the top there. It's well over 400. We don't want to write 400 formulas. No matter how we would write any individual formula we need to get the percentages out of row 3. So we need a dollar sign in front of the row 3 reference here and no matter how we write these formulas the quantities are coming out of column B so we want a dollar sign in front of the B and you can certainly type the dollar sign if you want but it might be easier simply clicking on either side of the asterisk there, it makes no difference where we start.

I'm going to press the function key F4 repeatedly. Notice how it cycles through four variations. We want the dollar sign in front of the three but not in front of the C for the percent reference. As we click on the other side, and by the way you can click in front of the B, between the B and the four, you can click after it or you can highlight it. Any of those, makes no difference which. Then as we press the function key F4 here we wanna make sure that the dollar sign is in front of the B but not in front of the four.

In other words, all quantities come out of column B. So whatever follows the dollar sign is what we want to be absolute. We want to make sure that every formula we're gonna be copying into this range gets the quantity out of column B, gets the percent out of row three. I'll press Control + Enter here, drag that fill handle across here, and then double-click the lower right-hand corner or click and drag downward and we've got a bunch of formulas and this should make sense. Click on one at random here and what is this one doing? It's getting the appropriate percent and quantity and recognizing that because of this mixed address all formulas here refer to row three and all formulas refer to column B.

Now it would get a little bit trickier here but not too much if these quantities were not so round. So let me just change a few of these. What if this is 1,034 and then down here it's 9,001. I think in real life situations you wouldn't always have perfectly round numbers the way we've seen in the initial example and like this. So, are we seeing the correct results here? Not necessarily, I'll highlight just a few of them and press the comma button.

This is in the number group on the home tab and make those columns wider also drag across here, double-click a boundary and we'll see some entries here. Now we might even have decimal numbers beyond this but what are we seeing here? You really can't have 1/5 of a washcloth and because based on the items we're seeing here we wouldn't want whole numbers. So making a change again in the initial formula, we could just start with this one, use the Round function in the example here. We wanna make sure that our calculation, no matter what it is, is rounded to the nearest whole number.

So the Round function, here's our calculation, comma zero, round it to zero decimal places. Complete that entry. I'll drag across and we'll see those numbers turn into whole numbers. The display will still show two decimals for the moment but we see what's happening there and while we're at it let's go back to that number group and decrease the decimals twice and then double-click to copy this down the column. And that's what we need to do in the example.

But the main thrust of this tip is this idea that from time to time you will need to use what's called a mixed reference, in other words a portion of an address is absolute the other potion isn't. In this case it's the row, in this case it's the column. So we've seen how relatively simple it is to set these up. It's a little bit complex looking at first and those dollar signs tend to make formulas look a little bit strange until you get used to these. But there's no question this is a necessary tool to work with Excel efficiently.