Sunday, August 25, 2013

Excel and the two most powerful uses of F4

The F4 key can contribute to greater productivity in Excel. This post will examine two quick ways which this key can be used to help users work faster in Excel

Toggle through cell referencing: F4 makes it faster

When writing a formula where absolute or other cell referencing is required the user does not need to type dollar signs around the cell references. Instead F4 can be used to toggle through referencing options.

Take the below spreadsheet for example:


In the above example the user would be trying to calculate the average selling price of produce given a desired gross margin percentage. The formula used above is correct, however it cannot be copied to the cells directly underneath as it is now.

=D6*(1+D2)

The above formula needs to use an absolute reference (dollar signs) around D2, then it can be copied down.

=D6*(1+$D$2)

The user could type them manually but it is often easier to hit the F4 key to add the absolute reference.

While typing the formula use F4 to set cell references, the user would hit F4 immediately after typing D2 and before the parenthesis.

Hit F4 once for absolute reference ($D$2)

Twice will fix the row (D$2)

Thrice will fix the column ($D2)

And four times will reset the referencing to default (D2)

If the formula has already been typed out, left click the reference and hit F4 to toggle.

It doesn't seem like a big time saver but if you’re writing a lot of formulas it will be.

Repeat the last action with F4

Often times users repeat tasks continuously, some situations can call for the use of F4.

If the below spreadsheet had a lot of comment cells that we wanted erased we can do it in no time using F4.


The way some users would go through a worksheet and delete comments would be to right click each commented cell and go to the delete comment option.


Of course all the cells could be highlighted and comments deleted at the same time, but what if some needed to be kept and for the sake of this example pretend I didn't tell you that.

When the user completes this action once, they can go to other cells that they want to remove comments on and hit F4. (Note that F4 is just repeating the last task carried out in Excel) This method can work well if a worksheet has to be formatted manually.