Debugging a formula is a lot easier if you can see what a part of the formula is equivalent to. While editing a cell, you can highlight a portion of a formula and press the F9 key to see what that part of the formula is equal to; you can undo that display and proceed with another part of the formula.
- [Voiceover] When you work with certain formulas,…sometimes you recognize that a result is just dead wrong.…It's way off.…On this worksheet called debug values, I've zoomed in.…We're looking at cell D13.…I know that answer is wrong.…I'm not sure why it's about half of what it should be,…so I'm going to edit this.…Now, I can do this in the formula bar…or, maybe a little better, right on the cell itself.…Because I've zoomed in so much, I'll double click the cell,…and do the editing in here.…Now, what I'd like to be able to do…is to highlight portions of this…to see what a portion of it is equal to.…
If I highlight this calculation right here…and find out what it's equal to,…maybe that will help me figure out what's going on…or it might not.…I'm not sure, but I've got it highlighted.…I'll press the function key F9,…and that's as if to say, throw away the formula…and show me the result for just the highlighted portion…of the formula.…So, function key F9 brings me this.…Now, maybe that tells me immediately,…oh, yeah, I see what's going on.…
Author
Dennis Taylor
Released
5/26/2016- Understanding how the hierarchy of operators affects formula results
- Knowing when to use absolute vs. relative references
- Using Formula Builder for unfamiliar functions
- Displaying a worksheet's formulas and highlighting formula cells
- Converting formulas to values
- Creating 3D formulas to gather data from multiple sheets
- Creating and expanding nested IF functions; using AND, OR, and NOT with IF
- Looking up information with VLOOKUP, HLOOKUP, MATCH, and INDEX
- Analyzing data with the statistical functions: MEDIAN, MODE, etc.
- Using the power functions: COUNTIF, SUMIF, COUNTIFS, AVERAGEIFS, and more
- Calculating financial data such as payments
- Performing basic math
- Calculating dates and times
- Editing text with functions
- Using array formulas and functions
- Referencing data in other cells and files
- Extracting information from Excel worksheets
Skill Level Intermediate
Duration
Views
-
Introduction
-
Welcome50s
-
Exercise files25s
-
-
1. Formula and Function Tools
-
2. Formula and Function Tips and Shortcuts
-
3. IF and Related Functions
-
4. Lookup and Reference Functions
-
Use MATCH and INDEX together4m 40s
-
5. Statistical Functions
-
6. Power Functions
-
7. Selected Financial Functions
-
8. Math Functions
-
9. Date and Time Functions
-
10. Text Functions
-
11. Array Formulas and Functions
-
12. Reference Functions
-
13. Information Functions
-
Conclusion
-
Next steps25s
-
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Simplify debugging formulas with the F9 key