F4 Repeats or $

Learn Excel from MrExcel podcast, episode 2018 - F4 for Redo and Absolute References!

I'll be podcasting this entire book, click the “i” on the top-right hand corner to get to the playlist!

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Well F4 Is a great key, it's useful when we're putting in $ in our formulas, but when you're not in Edit mode, F4 has this whole other secret life, F4 is Redo! Alright, so here's an example, I have spreadsheet set up, where I have to delete 4 columns, and in each one I could go to that cell B1 and do Alt+E D C Enter, alright, so that's 4 keystrokes. But here's the thing, I just did Alt+E D C, and now I need to do that exact same command sequence on another cell. So I press the right arrow, all I have to do is press F4, F4 is Redo, now Ctrl+Y is also Redo, but let me tell you, it's a lot easier to get F4 than Ctrl+Y. Alright, right arrow, F4, right arrow, F4, to delete the rows, boy yeah, you could just sort and get all the blank rows to the bottom, but at this point Alt+E D R Enter, down arrow, F4, down arrow, F4, down arrow, F4. Just toggle back and forth, and it's a quick way to go, there's so many different things that F4 will work on. Alright, so if you have to do the same thing over and over and over again, think about F4, great, great tip.

Alright, now here's the much more common thing that F4 is used for, let's say we have to build a little formula here of merchandise*tax, so = left arrow *F1, alright, that formula works perfectly. But when I copy that formula down and when we go look at the formula, you'll see that the B2 changed to B5, that's exactly what we wanted to happen. But that F4 needed to always point to F1, alright, we don't want that to change. So back up here I'll press F2 to put in Edit mode, and then, as since the cell pointer’s right next to the F1, I press F4, and it puts both $ in. Now, when we copy down and fill without formatting, we get the correct formula all the way down.

Now this is called an absolute reference, the $F$1, we're freezing the F and we're freezing the 1, let me show you when you have $ to use with a rectangular range. so =VLOOKUP, I'm going to use the arrow keys first, so I press the left arrow to point to Cherry, comma, right, right, Ctrl+Shift+Down arrow, Ctrl+Shift+Right arrow. OK, so right now I'm next to the E9, and I want you- see what happens when I press the F4, I get $ throughout, it added 4 $ signs, $D$2:$E$9, that’s awesome. The most common way that people would build this formula would be using the mouse, so they click on Cherry, comma, click on that range, the flashing insertion point’s right next to the F9, I press F4 and I get all the $, that's beautiful! Here's the one where it doesn't work, =VLOOKUP, there's some people who can type a lot better than I could type, they must have had typing class in high school. And they will type D2:E9, flashing insertion point is right next to the 9? No! When I press the F4, it only puts the $ in there.

Alright, so I guess if you're a good typer, you’re typing the $ as you go, I don't know why it doesn't work in this one case. OR let's say we forget the $ entirely, alright, so you build the formula, you press forget to press F4 right here, copy the formula down, you see that you start getting more and more #N/A, you're like “Oh, I have to put the $ in!” So press F2, OK. Now, to put all the $ in here, you can either select the entire 5 characters, and press F4 like that. OR you can simply select the colon, when you select the : and press F4, it will put the $ in both sides, Ctrl+Enter, and the VLOOKUP is fixed, alright.

Alright, what if we need one of those expanding ranges and dollar signs too down to N2? These are tough to build, I can't seem to find a way to build it with the F4, and it's really weird, the only thing I can do is N2:N2 and then press F4. But this is the wrong way to build this reference, well, it looks like it's going to work there, but when I copy the reference down, it's really bizarre. I'm going to Show Formulas mode here with Ctrl+`, and we're violating some sort of rule that the $ jumps from the 2 here back to the beginning point, and we get the $. And I guess that's correct, but everyone who looks at this is going to think you're insane, alright? So, if you have to build an expanding range, just bite the bullet and type the darn thing, N2:N2 like that, and copy it down to create a formula that's going to reliably copy, and not have the $ all screwed up.

This is insane, who would have thought that a simple little podcast about F4 would go on this long, but there's another kind of reference where we have a single $ in. For example, if you want to lock it down to just the column or just the row, so this formula is going to use all 3 types of references. It's the Base formula, press F4 there to put both $ in, times whatever is up in row 1, and here I need to lock it down to the row. So a single $ before the 1, that's F4 twice, times A3, and here I need a single $ before the A, press F4 3 times, alright? This beautiful formula has an absolute reference with both $, a mixed reference where we're freezing just the row, mixed reference where we're freezing just the column. To lock down just the rows F4 twice, to lock down just the columns F4 3 times, create this formula, copy it throughout, and it will point to the right cells all the way through, back to row 1, back to column A, and that one is frozen all together. Alright, this tip #23 is just one of the 40 tips in this book, click that “i” on the top-right hand corner, you can buy the entire book!

Episode recap: The mighty F4 key is popular when building formula references, but it's also great for repeating the last action. Whatever you have to repeat over and over and over again, you can do the command once, select the next cell, and press F4 to repeat. F4 is also great for absolute references, a formula reference changes as you copy the formula, but sometimes you need one part of the formula to stay fixed, so you press F4 after pointing at it. When you're building a rectangular range, if you use the mouse or the arrow keys, F4 is going to work perfectly. But if you're typing the formula, watch out, F4 will only freeze the second part of the reference, the bottom-right corner. If you forget to type F4, go back into Edit mode with F2, select the whole range or just the colon, when you have an expanding range, it's tough to use F4. While you're editing, if you need to toggle from Edit mode to Enter mode press F2.

There are really 4 kinds of absolute reference with two $ locks, both the row and the column, this one, $A3 locks just the column, C$2 locks just the row, and then A1 is the relative reference where nothing is locked. You get this one, press F4 once, this one F4 twice, this one F4 3 times, this one, well, don't press F4 at all or press F4 4 times, and it'll toggle back, alright. So we go point to a cell, press F4 once, locking both, F4 a second time lock the row, 3rd time column, 4th time nothing, and you can keep going back through and toggling through the whole thing.

Alright, there you have it, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!

Hey, it's an outtake, none of this is in the book, here's an amazing way, double-spaced data. So I put in the number 1, hold down the Ctrl key and drag down, hat puts in the numbers1-10. And I hold down the Ctrl key and make it copy that whole thing, now I have the numbers 1-10 twice. I put a heading up here “Sort”, Data, A-Z, BAM, I've now double-spaced that data. And to put those stupid columns back in, Alt I C to insert a column, and then F4 F4 F4, to make them narrow, Alt O C W 1 to make it narrow, and then F4 F4 F4!