From the course: Excel 2016: Advanced Formulas and Functions

Debugging formulas with the F9 key

From the course: Excel 2016: Advanced Formulas and Functions

Start my 1-month free trial

Debugging formulas with the F9 key

“

- Sometimes when you're working with formulas, you recognize that a result is incorrect. It's way off. Something wrong with the formula. You could edit a formula and then debug from there by using the F9 key. I'm going to zoom in on this worksheet and focus on cell D13. Double click. Quite lengthy. Now, let's say I know the data pretty well, and the answer I'm getting I know is wrong, for whatever reason I'm not sure. But, looking at the formula, I'm going to highlight a portion of it. If I knew what this was equal to it might help me understand what's going on. So I'm highlighting this. Remember, I'm in edit mode. I just double clicked here. I'll press the function key, F9. Now, maybe that tells me something, maybe it doesn't. If it's way off, I'll escape here, go check out the data, come back and reconsider what the formula is doing. Sometimes that's the result. If I'm not sure here, I might say, "Well, that didn't tell me anything, "so let's undo that, Control Z, "and maybe focus on a different part of the formula." How about this portion right here? What is that equal to? Highlight that, press F9. That gives me an answer. Once again, this could be right. It could be wrong. I know my data pretty well. I might scroll up and down, look at the other cells and say, "Well that's it. "I've got to change this part of the formula." Often you'll press Escape, just simply to get out of here. I'm going to go back and double click again. If you happen to highlight an incoherent portion of this, in other words, a portion of a formula that by itself wouldn't stand alone as a formula. For example, if I highlight this data, and maybe I forget to grab that left parenthesis out there. As I'm highlighting this, and then press F9, I get a message that's somewhat misleading and it's way off base. We simply ignore it. Just escape, maybe reconsider, highlight something that does make sense. Now maybe that whole combination, and I really did mean and now I will pick up that left parenthesis, F9. Once again, that might be helpful, it might not. If I'm not sure, once again I could consider pressing Control Z, undo, and focus on a different portion of this. So this can be really helpful. In some situations, you'll highlight different sections. For example, this, OK. And then maybe this. Maybe it's still not clear. You'll highlight maybe just this portion again. Now I didn't press Control Z this time so the thing to be careful with as you're doing this, is don't press Enter because you will have altered the formula, in this case substantially if I press Enter right now. So let's hope that as you're doing this, that by highlighting either this portion or that portion, something sort of jumps out at you and says, "Yeah, that's the part that isn't quite correct." Or, it's way off. One or the other. So, many times you'll press Escape to get out of here. If you work with functions that involve logical testing, and there's a formula here in cell R1, a long formula. As I double click, we see this. Now, I'm not going to spend a whole lot of time on this right now, but I am curious as to what this is equal to. Now what could that be equal to? It's not a value. That's either what? It's true or false. I'll press the function key, F9. That's FALSE, therefore, this entire construction is going to be false. Let me press Control Z just to make sure of that. If one of those entries there within the parentheses is false, the entire AND function here is false. Again, I'll press the function key, F9. You can see what's happening here. Then Control Z. And sometimes you'll be highlighting multiple sections here too. How about this section here. Is that true or false? Highlighting all of that and pressing F9. That also is FALSE. And so I think you can see how at different times when you are evaluating formulas that aren't quite working the way you want them to, you know the answer is wrong, the ability to edit a cell and in the process discover what each portion of it is equal to can be really helpful. Again, resist that urge to press Enter when you find a solution. Nearly always you'd want to press Escape and then reconsider the formula. So, it's a real powerful tool here in debugging formulas, using the F9 key while editing.

Contents