I’ll let you know right now this trick is not real fancy, or even best practice, but it is my favorite F9 trick so I’m sharing it. It basically changes a variable in a formula to a constant.

I’ve mentioned before the handiness of F9 when creating formulas. Highlighting part of a formula and pressing F9 evaluates just that part of the formula, converting it to its result. So if you have “=1+2+3” and you hit F9 with “2+3” selected, the formula becomes “=1 + 5.”

Before I get to the trick, here’s a longer F9 example.

Imagine you are generating emails from a list of addresses and attachments. One of the columns contains the names of the files to attach. There can be one or more attachment names per cell, separated by commas. There’s also a master list of all the attachments to be sent.

To verify whether all the attachments were included exactly one time, you can use an array formula like this in column E:

If we highlight the 2nd row of that formula, in E3 and press F9 we get the following evaluation of the FIND section of the formula (with a few more #VALUE’s).

{#VALUE!;1;#VALUE!;#VALUE!;#VALUE!;#VAL …

FIND returns the position of the substring in the searched string, or #VALUE if the string isn’t found. The above tells us that the value in D3 is found once in B2:B14.

If you don’t already use F9, you should know that Ctrl-Z will undo the evaluation and return the formula to its formulaic state. You almost always want to do that, otherwise you’ll leave the hard-coded evaluation in the formula.

But not with my favorite F9 trick.

Even though it’s not a good practice in serious spreadsheets, sometimes I just want to hard-code a value into a formula. In these situations you can use F9 to insert the hard-coded value for you, and avoid typing errors. In this example, there’s a party with a randomized list of prizes for the guests.

So, column C has your formula to randomize the prize distribution. But, for reasons only known to you, you’ve decided the grand prize of a new car must go to Bahijah, and you want to hard-code that into your formula.

You could just type IF(B2=”Bahijah“, but to avoid spelling errors you instead select B13.

You then highlight B13 and press F9 and the beginning of the formula becomes:

Below, I used a table so that the formula would change in every cell in column C, and you can see the car become Bahijah’s in one fell swoop.