Replace a formula with the value or final result – Super Easily

By
Hasaan Fazal
-

Formula and Excel is a bond not meant to break. Although formula do help us get the values we want. But often you find that it will be code that you just get have the result itself what the formula computes. That means you want to hard code the cell with the result and replace the formula altogether. Reason might be that you don’t want the value of that cell to change any more or some other reasons. We do have a cheeky trick for that and its just a click of a button.

Hold the gold. Forget the ocean

Consider you have a formula in a cell. Get into editing mode by double clicking the cell or pressing F2 or clicking in the formula bar after selecting the cell or whatever mean you have. Once in editing mode press F9 and bang! your formula will be replaced by the value or result it calculates.

f9 formula replace

Hold on! I want to keep the gold and Ocean too!

If your formula consists of multiple parts then you can fixate parts of formula you desire by converting them in computed (final) figures within formula and the rest of formula will remain untouched.

For example an employee’s basic salary is 50,000 and he earns an “excel” allowance @ 15% of basic salary and also earns 10% commission on total sales for the week. Now the formula used contains many parts. If you like to use only the value of that part you can do it by selecting that part and pressing F9. Just like that!

f9 in formula

Summary

  • Get into editing mode:
  • If you want the final result of the formula then simply press F9 and formula will be replaced with its ultimate result
  • If your formula consists of different parts and you want only part of the formula to be replaced then select that part and press F9

5 COMMENTS

  1. Thanks for the tips and tricks.

  2. Thanks for your immense support on Excel proficiency. Can you, as usual and kindly, provide for us your followers an exposure on how to incorporate date into excel models? For example, how do I do it when I need to have forecast estimates update linearly or exponentially with a date sequence? Thanks.

  3. What can I do to stop these hourly advertisements from you?

  4. Thanks for your profound kindness of sharing these great Excel knowledge far and near.

  5. Thanks for your valuable tips.

Comments are closed.