Evaluate part of a formula directly in Excel's formula bar

Here’s an Excel productivity tip to help with debugging complicated formulas by calculating part of it directly in the formula bar.

If you have a complex formula within a cell, you can immediately evaluate part of it by selecting the part (dragging across it with the mouse or by using arrow keys), then press F9.

For example, if you have:


then you select the 3+2/5 part:


and press F9 it becomes:


So Excel has evaluated the 3+2/5 part of the formula and replaced it with the result of 3.4.

This is useful for simplifying formulae, and also for debugging when something isn’t working as you’d expect.

Note that if you press enter (or click on another cell), it will save the formula with the evaluated number in the cell, rather than the original one. To avoid saving it, press Escape when you’re done editing.

So for the above example, enter hardcodes the formula =cos(3.4) in the cell, while pressing escape returns it to =cos(3+2/5).



The latest posts sent straight to your inbox.