While you are creating complex
formulas, it is often required to pause for a minute and see what is to come as a
result of that formula. When you refer Sheet2!A1:A3 in a SUM function, It is often
good idea to see what these cells A1:A3 contains and what is the result that
SUM function fetches. Relevance of this becomes more when you build complex and
sensitive formulas. Let’s see what Excel has to provide regarding this.
We have formula debugging tool in
Excel that you can use to attain just the same. To simplify our example, I am
going to use a simple SUM formula and show you what I mean.
Let’s assume I entered values 1,
2, 3 in cell A1, A2 and A3 respectively. I need to fetch sum of A1:A3 in cell
A4.
We enter a formula in A4 which is
“=SUM(A1:A3)”, after entering this formula, instead of pressing Enter, pause for a minute and notice
the formula bar. Is it showing the formula you just entered? Click on the formula
bar and select A1:A3 part.
Now press function key F9 and see
what happens.
Saw the magic? Reference A1:A3
actually turned into the values that reference contains. If you want to show
the entire formula result, you can press F9 without selecting any part of the
formula.
Doing this action actually
replaces the existing values with result. So, once you press F9 and are satisfied
that displayed result is what you are expecting, press Ctrl+Z to undo your last
action and restore the cell references. Otherwise, you can just press Enter if you don’t want formula anymore
but only the result.
If you have made any error in cell
reference or formula construction is incorrect, Excel throws up an error alert
and you have to rectify the formula.
I deliberately gave incorrect
reference as shown below and see the error.
This way you can use F9 in Formula bar as a calculator.
No comments:
Post a Comment