F4 Repeats or $
September 04, 2017 - by Bill Jelen
Today, the secret double-life of the F4 key. Yes, F4 adds the $ to a formula to make it absolute. But the F4 key has a whole other set of skills when you are not in Formula Edit mode.
The mighty F4 key should be in your Excel arsenal for two completely different reasons:
- Use F4 to add dollar signs in formula references to make them absolute, mixed, or relative.
- When you are not editing a formula, use F4 to repeat the last command.
Making a Reference Absolute
In the following figure, the tax in C2 is B2 times F1.
But when you copy this formula down, none of the sales tax calculations are working. As you copy the formula down the column, the B2 reference automatically changes to B3, B4, and so on. That is what you want. But unfortunately, the reference to the sales tax in F1 is changing as well. That is not what you want.
The solution? Edit the original formula and press F4. Two dollar signs are added to the final element of the formula. The $F$1 says that no matter where you copy this formula, that part of the formula always needs to point to F1. This is called an absolute reference. Pressing F4 while the insertion point is touching the F1 reference is a fast way to add both dollar signs.
There are other times when you need only part of the reference to be locked. In the following example, you need to multiply H2 by A3 by C1. The H1 will always point to H1, so you need both dollar signs in $H$1. The A3 will always point back to column A, so you need $A3. The C1 will always point to row 1, so you need C$1.
To enter the above formula, you would press F4 once after clicking on H1, three times after clicking on A3, and twice after clicking on C1. What if you screw up and press F4 too many times? Keep pressing F4: It will toggle back to relative then absolute, then row absolute, then column absolute.
The result? A single formula that can be copied to C3:F12.
Repeating the Last Command
Keyboard shortcuts are great. Alt + E, D, C Enter will delete a column. But even if you are really fast at doing Alt + E, D, C Enter, it can be a pain to do a lot of these in a row.
After deleting column B, press the Right arrow key to move to the next column that needs to be deleted. Instead of doing Alt + E, D, C Enter again, simply press F4. This beautiful command repeats the last command that you invoked.
To delete the remaining columns, keep pressing Right arrow then F4.
Next you need to delete a row. Alt + E, D, R Enter will delete the row.
To keep deleting rows, press the Down arrow followed by F4 until all the blank rows are gone.
The F4 command works for a surprising number of commands. Perhaps you just built a custom format to display numbers in thousands: #,##0,K. If you see a few more cells that need the same format, select the cells and press F4.
There are a few annoying commands that do not work with F4. For example, going into Field Settings in a Pivot Table and changing the number format and calculation is one that would be nice to repeat. But it does not work.
Thanks to Myles Arnott, Glen Feechan, Shelley Fishel, Colin Legg, and Nathan Zelany for suggesting this feature.
- The mighty F4 key is popular when building formula references
- But it is also great for repeating the last action
- If you have to do a similar command 10 times, do it the first time
- select the next cell and press F4
- Alt + E D C Enter deletes an entire column
- Alt + E D R Enter deletes an entire row
- Right-drag the fill handle to get Fill without Formatting
- F4 is likely more famous for creating absolute references
- By default, a formula reference changes as you copy the formula
- If you need one portion of a formula reference to stay fixed, press F4 after pointing at it
- For rectangular ranges, F4 works after mouse or arrow keys, but not when typing
- After the fact, select whole range or just the colon
- It seems difficult to use F4 to create an expanding range
- F2 to toggle from edit mode to enter mode
- $F$1 will lock both row 1 and column F and is called an absolute reference
- $A3 locks only column A and is a mixed reference. Press F4 three times to get this.
- C$2 locks only row 2 and is a mixed reference. Press F4 twice to get this.
- A1 is a relative reference where nothing is locked
- Outtake: Fast way to double space data in Excel
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!
Download the sample file here: Podcast2018.xlsx
Title Photo: DizzyRoseblade / Pixabay