Lesson 8: Debug Excel formulas using F9



The pledge, the turn, the prestige: F9 works like magic

Have you seen "The Prestige"?

Are you really referring to a movie in an online Excel course?

Yes, I am. We can't be too serious all the time.

Of course I saw it. I'm a fan, great movie. By the way, did you know that Christopher Nolan is one of my favorite directors?

Mine as well. Do you remember what the movie is about?

For sure! It's about two rival magicians obsessed with creating the best stage illusion. It's structured around the three elements of a magic act: the pledge, the turn and the prestige.

Well, you'll see that we can draw parallels between these three parts and using the F9 function to debug formulas.

Now you're making me curious.


Part 1: The pledge


The first part of a magic act is called "The pledge". The magician shows you something ordinary: a deck of cards, a bird or a man.


In this case, I'll show you an Excel formula. Although it's a complicated one, There's nothing strange about it:


Well, this looks like an everyday formula...

It's not about the formula itself. For sure, it's not the easiest one, but you might encounter situations in your day to day activities where the formulas you're building are not all straightforward.

You don't hear me objecting.

Glad to hear that.


Part 2: The turn


The second act is called "The turn". The magician takes the ordinary something and makes it do something extraordinary.


The problem with my formula is that it generates an error in cell C9.

Good luck for finding it.

This is the extraordinary part, have look at my formula now:


Hey, where are the "INDEX" and "IFERROR" functions?

As in every good magic act, something disappears...


Part 3: The prestige


Making something disappear isn't enough; you have to bring it back. That's why every magic trick has a third act, the hardest part, the part we call "The prestige".


Now, if this was a real magic trick, you'd like to be fooled. In this case however, this would be silly there you won't be able to reproduce what I just did, which makes this class useless. So, I'm going to do what a true magician never would do, being revealing the secret.

Debugging a complicated formula can be a real burden, and this is when the F9 function key enters the scene. It works like this:

In the formula bar, select any part of the whole formula:


In this case I just selected IFERROR(INDEX(Emp_Table;B9;4);"")=""

You've to make sure the portion of the formula you select works on its own. If you forget to select a bracket or a quote, the evaluation might not work as Excel won't know how to interpret that part of the formula.

Now press F9 and you'll see this:


Wow, your selection changed to TRUE instead of displaying the formula.

Indeed, which means that for this particular cell, Excel evaluates the selection as being TRUE. By pressing F9, Excel replaces your selection with the actual values that part operates on, or with the calculated result.

Now, all you need to do is going through the formula and check which part is generating an error instead of displaying "Sylvia Blend". After a while, you'll find that evaluating "INDEX(Emp_Table;B9;4)" is resulting in "#REF" and is our culprit.


You now quickly realize that your INDEX function is referring to a cell outside your table range.

Be aware of this: if at this step, before modifying your formula you press the Enter key, the result will overwrite this part of your formula and you will lose the formula selected. Pressing ESC instead will undo all evaluations Excel made and keep the formula intact.

Aha, as in the prestige: making something disappear isn't enough; you have to bring it back.

Exactly! So, you now correct your formula and decide to add an ISERROR function to take care of this:


Use the F9 key whenever you need to understand or debug a formula. It will save you the trouble of breaking the formula down into separate formulas on the worksheet. This debugging method lets you narrow down an error to a particular range or function causing it.

The download link with the file used in this course can be found here.


Good to know, and to remember...

  • You may want to expand the formula bar to give yourself more room to work;

  • Pay attention to your selection before you press F9, make sure you understand what is being evaluated. Luckily, the function tooltip makes it much easier to select things precisely;

  • Work from the inside out: evaluate small parts of the formula and grow bigger. This will help to find the bug faster;

  • Press ESC to exit without making any changes to the formula;

  • Another, but less flexible, way to evaluate formulas in Excel is the "Evaluate Formula" option that resides on the Formulas tab, in the Formula Auditing group. As soon as you click this button, the Evaluate Formula dialog box will pop up, where you can inspect each part of your formula in the order the formula is calculated:

We appreciate your feedback!


About Cintellis

Let us surprise you with how much more you can achieve by using Excel and VBA based products.

Are you looking for an on the level, reliable and trustworthy company that will surprise you with how much more you can achieve by using Excel and VBA based products? Learn more about our philosophy, products, services or contact us for more info.



You are not established in Belgium? That's not an issue: did you know that Cintellis provides remote services to customers located all over the world?



About Numis

Flexible, cheap and very short implementation time, discover Numis, our Excel based Cash Management and Management Accounting Software and experience a totally new way to analyse and report on your cash positions.

At Numis, we help you to get more out of your financial data than you thought possible. From enhanced reporting functionalities and detailed analysis to providing enterprise budgeting and forecasting solutions, we will help you to achieve your business objectives. When using Numis, you can share your analysis and strategy with your business partners, suppliers or employees as needed.

Numis is a product from Cintellis bvba & FS&P bvba.

CONTACT Us

You can contact us by phone, mail or by filling out the form below.

Corporate address:
Alsembergsesteenweg 588
1653 Dworp
Belgium

Phone Numbers:
+32 (0)471 65 19 66

E-Mail: info@cintellis.com