MrExcel Publishing
Your One Stop for Excel Tips & Solutions

F4 Repeats or $


September 04, 2017 - by Bill Jelen

F4 Repeats or $

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.
Illustration: Bob D'Amico
Illustration: Bob D'Amico

Making a Reference Absolute

In the following figure, the tax in C2 is B2 times F1.

Sample Data Set with Tax Formula
Sample Data Set with Tax Formula

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.

Cell Reference Change on Copy
Cell Reference Change on Copy

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.

Absolute References
Absolute References

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.

Keep Pressing F4
Keep Pressing F4

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.

The Result
The Result

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.

Delete Column
Delete Column

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.

Repeat Last Command
Repeat Last Command

To delete the remaining columns, keep pressing Right arrow then F4.

Delete Remaining Columns
Delete Remaining Columns

Next you need to delete a row. Alt + E, D, R Enter will delete the row.

Delete Row
Delete Row

To keep deleting rows, press the Down arrow followed by F4 until all the blank rows are gone.

Keep Deleting Rows
Keep Deleting Rows

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.

Watch Video

  • 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

Auto-Generated Transcript

  • 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 dollar dollar signs 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 setup where I have to
  • delete one two three four columns and in
  • each one I could go to that cell b1 and
  • do alt e d c enter alright so that's
  • four keystrokes but here's the thing I
  • just did all etc' 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 control Y is also redo but let me
  • tell you it's a lot easier to get f4
  • then control 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 all e-d are 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 times tax so equal
  • left arrow times f1 alright that formula
  • works perfectly but when I copy that
  • formula down and when we go look at the
  • form of you you'll see that the b2
  • changed to be five that's exactly what
  • we wanted to have happen but that f4 it
  • 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
  • pointers right next to the f1 I press f4
  • and it puts both dollar signs 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 dollar sign F
  • dollars that I wanted we're freezing the
  • F and we're freezing the one let me show
  • you when you have dollar signs to use
  • with a rectangular range so equal
  • vlookup I'm gonna use the arrow keys
  • first so I press the left arrow to point
  • to cherry comma right right control
  • shift down arrow control shift right
  • arrow
  • okay so right now I'm next to the e9 and
  • I want you see what happens when I press
  • the f4 I get dollar signs throughout it
  • added for dollar signs dollar sign D
  • dollar sign two dollar sign dollar sign
  • nine
  • 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 points right next to the f9 I
  • press f4 and I get all the dollar signs
  • that's beautiful here's the one where it
  • doesn't work equal 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 only puts the dollar signs in there
  • alright so I guess if you're a good type
  • of your typing the dollar signs as you
  • go I don't know why it doesn't work in
  • this one case or let's say we forget the
  • dollar signs entirely parent so you
  • build the formula you press forget to
  • press that 4 right here copy the formula
  • down you see that your start getting
  • more and more na you're like oh I have
  • to put the dollar sign in so press f2 ok
  • now to put all the dollar signs in here
  • you can either select the entire 5
  • characters and press f4 like that or or
  • you can simply select the colon when you
  • select the colon and press f4 it will
  • put the dollar signs in
  • both sides control-enter and the vlookup
  • is fixed all right all right what if we
  • need one of those expanding ranges and
  • dollar sign to down to end to these
  • these are tough to build I 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 end to : and 2 and then press
  • f4 but this is the wrong way to build
  • this reference well it looks like it's
  • gonna work there when I copy the
  • reference down it's really bizarre I'm
  • going to show formulas mode here with
  • control Grove accent and we're violating
  • some sort of rule that the dollar sign
  • jumps from the to here back to the
  • beginning point and we get the dollar
  • sign and and I guess that's correct but
  • everyone who looks at this is gonna
  • think you're insane
  • alright so so if you have to build an
  • expanding range just just like the
  • bullet and type the the darn thing n2 2
  • n2 like that and copy it down to create
  • a formula that's going to reliably copy
  • dot have the dollar signs 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 dollar sign in for example if
  • you want to lock it down to just the
  • column or just the row so this form is
  • gonna use all three types of references
  • it's the base formula press that for
  • there to put both dollar signs in times
  • whatever is up in Row 1 and here I need
  • to lock it down to the row so a single
  • dollar sign before the one that's f4
  • twice 1 2 x f4 x 83 and here I need a
  • single dollar sign before the a press f4
  • one two three times alright this
  • beautiful formula has an absolute
  • reference with both dollar signs a mixed
  • reference where we're freezing just the
  • row mixed reference where we're freezing
  • just the column to lock down just throws
  • f4 twice lockdown just the columns f4
  • three 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
  • number one
  • three 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 F Force also
  • great for absolute references a form of
  • reference changed 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 have
  • use the mouse or the arrow keys f4 is
  • gonna work perfectly but if you're
  • typing the formula watch out f4 will
  • only freeze the second part of the
  • reference to the bottom right corner if
  • you forget to type f4 go back into edit
  • mode with f2 I 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 four kinds of
  • references annapolis absolute reference
  • with two dollar science locks both the
  • row and the column this one dollar sign
  • a three locks just the column C dollar
  • sign two locks just the row and then a
  • one is the relative reference where
  • nothing is locked you get this one press
  • f4 once this one f4 twice this one f4
  • three times this one well don't press f4
  • or press f4 four times and it'll toggle
  • back alright so we go point to a cell
  • press that for once locking both at for
  • a second time lock the row third time
  • column fourth 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 net cast from MrExcel hey it's
  • now take none of this is in the book
  • here's an amazing way double-spaced data
  • so I put it in the number one hold down
  • the ctrl key and drag down that puts it
  • in the numbers one through ten and I
  • hold down the ctrl key and make it copy
  • that whole thing now I have the numbers
  • 1 through 10 twice put it heading up
  • here sort data a to Z BAM I'm now
  • double-spaced that data and put those
  • stupid columns back in alt I see to
  • insert a column
  • and then f4 f4 f4 to make them narrow
  • alt OCW 1 to make it narrow and then f4
  • f4 f4

Download File

Download the sample file here: Podcast2018.xlsx

Title Photo: DizzyRoseblade / Pixabay