Hello, Excellers. Welcome back to another #Excel #FormulaFriday blog post in my 2021 Excel series. This blog post in response to a question asked by a subscriber. They wanted to ‘see’ the different parts of their Excel formula. Or in their words how to understand how each part of the formula is calculated. So, this is exactly the same as debugging an excel formula. There is a great way to do this in Excel.
It’s simple, by using the F9 key.
Breaking A Formula Down.
Let’s take a look at an example to work through this process.
I have a data set that lists some Monthly sales above. I also have a bonus table detailing the threshold at which a monthly bonus is applicable as well as the percentage earned.
Ok, let’s jump right in and see if the monthly sales meet the bonus threshold. It is easier to debug an Excel formula in the Formula Bar at the top of the window. So, that is where I will begin. So, click in the formula bar to start. Excel is now in Edit Mode. This status is displayed at the bottom left of the window.
Once you are in Edit Mode, use the tip of the cursor to or function tooltip to select the part of the formula you want to debug or evaluate. So, when we click anywhere inside this example IF function, there are three arguments listed in the tooltip. Just simply click to select each argument in the formula.
- Click into a cell that contains your formula. In this example, I have chosen cell D3. You are now in Edit Mode.
- F9 can now be used on any part of the formula to check the calculation.
I can look at two parts of the formula here. See the result of selecting the parts of the formula to evaluate and hitting F9 in the screencast below.
Hints For Evaluating A Formula With F9.
You MUST hit ESCAPE (Esc) after evaluating the parts of the formula. This will force Excel out of Edit Mode and not commit to any of the change made to the formula.
You MUST select the whole expression or function. If you don’t you will get an error.
I find this a really great way to methodically work through a formula and see what the results are of each part of it. Give it a go!.
So, if you want more Excel and VBA tips then sign up for my Monthly Newsletter. I share 3 Tips on the first Wednesday of the month. You will receive my free Ebook.