In this tutorial, you will learn a few quick and efficient ways to check and debug formulas in Excel. See how to use the F9 key to evaluate formula parts, how to highlight cells that reference or are referenced by a given formula, how to determine mismatched or misplaced parentheses, and more.
In the last few tutorials, we have been investigating different aspects of Excel formulas. If you've had a chance to read them, you already know how to write formulas in Excel, how to show formulas in cells, how to hide and lock formulas, and more.
Today, I'd like to share a few tips and techniques to check, evaluate and debug Excel formulas that will hopefully help you work even more efficiently with Excel.
The F2 key in Excel toggles between Edit and Enter modes. When you want to make changes to an existing formula, select the formula cell and press F2 to enter the Edit mode. Once you do this, the cursor starts flashing at the end of the closing parenthesis in the cell or formula bar (depending on whether the Allow editing directly in cells option is checked or unchecked). And now, you can make any edits in the formula:
When you are done editing, press Enter to complete the formula.
To exit the Edit mode without making any changes to the formula, press the Esc key.
By default, pressing the F2 key in Excel positions the cursor at the end of the formula in a cell. If you prefer editing formulas in the Excel formula bar, do the following:
These days, F2 is often considered an old-fashioned way to edit formulas. Two other ways to enter the Edit mode in Excel are:
Is Excel's F2 approach more efficient or does it have any advantages? Nope :) Simply some people prefer working from the keyboard most of the time while others find it more convenient to use the mouse.
Whichever editing method you choose, a visual indication of the Edit mode can be found at the bottom-left corner of the screen. As soon as you press F2, or double click the cell, or click the formula bar, the word Edit will appear right below the sheet tabs:
In Microsoft Excel, F9 key is an easy and quick way to check and debug formulas. It lets you evaluate only the selected part of the formula by replacing it with the actual values that part operates on, or with the calculated result. The following example demonstrates Excel's F9 key in action.
Supposing you have the following IF formula in your worksheet:
=IF(AVERAGE(A2:A6)>AVERAGE(B2:B6),"Good","Bad")
To evaluate each of the two Average functions included in the formula individually, do the following:
For example, if you select the first Average function, i.e. AVERAGE(A2:A6), and press F9, Excel will display its calculated value:
If you select only the cell range (A2:A6) and press F9, you will see the actual values instead of the cell references:
To exit the formula evaluation mode, press the Esc key.
The Excel F9 technique is especially useful for testing long complex formulas, such as nested formulas or array formulas, where it's difficult to understand how the formula calculates the final result because it includes a few intermediate calculations or logical tests. And this debugging method lets you narrow down an error to a particular range or function causing it.
Another 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.
All you need to do is click the Evaluate button and examine the value of the underlined formula part. The result of the most recent evaluation appears in italics.
Continue clicking the Evaluate button until each part of your formula has been tested.
To end the evaluation, click the Close button.
To start the formula evaluation from the beginning, click Restart.
If the underlined part of the formula is a reference to a cell containing another formula, click the Step In button to have that other formula displayed in the Evaluation box. To get back to the previous formula, click Step Out.
When creating sophisticated formulas in Excel, you often need to include more than one pair of parentheses to specify the order of calculations or nest a few different functions. Needless to say, it's very easy to misplace, omit, or include an extra parenthesis in such formulas.
If you miss or misplace a parenthesis and hit the Enter key attempting to complete the formula, Microsoft Excel usually displays an alert suggesting to fix the formula for you:
If you agree to the suggested correction, click Yes. If the edited formula is not what you want, click No and make the corrections manually.
To help you balance the parenthesis pairs, Excel provides three visual clues when you are typing or editing a formula:
In the following screenshot, I've crossed over the last closing parenthesis using the arrow key, and the outside parenthesis pair (black ones) got highlighted:
When you are debugging a formula in Excel, it might be helpful to view the cells referenced in it. To highlight all of the dependent cells, do the following:
In this example, I selected cell F4 and pressed Ctrl + [ . Two cells (C4 and E4) referenced in F4's formula got highlighted, and the selection moved to C4:
The previous tip demonstrated how you can highlight all the cells referenced in a certain formula. But what if you want to do the reverse and find out all the formulas that refer to a particular cell? For example, you might want to delete some irrelevant or outdated data in a worksheet, but you want to make sure the deletion won't break any of your existing formulas.
To highlight all cells with formulas that reference a given cell, select that cell, and press the Ctrl + ] shortcut.
Like in the previous example, the selection will move to the first formula on the sheet that references the cell. To move the selection to other formulas that reference that cell, press the Enter key repeatedly.
In this example, I've selected cell C4, pressed Ctrl + ] and Excel immediately highlighted the cells (E4 and F4) containing a C4 reference:
Another way to visually display cells relating to a certain formula is to use the Trace Precedents and Trace Dependents buttons that reside on the Formulas tab > Formula Auditing group.
The Trace Precedents button works similarly to the Ctrl+[ shortcut, i.e. shows which cells provide data to the selected formula cell.
The difference is that the Ctrl + [ shortcut highlights all cells referenced in a formula, while clicking the Trace Precedents button draws blue trace lines from the referenced cells to the selected formula cell, as demonstrated in the following screenshot:
To get precedents lines to appear, you can also use the Alt+T U T shortcut.
The Trace Dependents button works similarly to the Ctrl + ] shortcut. It shows which cells are dependent on the active cell, i.e. which cells contain formulas referencing a given cell.
In the following screenshot, cell D2 is selected, and the blue trace lines point to the formulas that contain D2 references:
Another way to display dependents line is clicking the Alt+T U D shortcut.
When you are working with a large data set, you may want to keep an eye on the most important formulas in your workbook and see how their calculated values change when you edit the source data. Excel's Watch Window was created just for this purpose.
The Watch Window displays the cell properties, such as the workbook and worksheet names, cell or range name if any, cell address, value, and formula, in a separate window. In this way, you can always see the most important data at a glance, even when you are switching between different workbooks!
To display the Watch Window and add cells to monitor, perform the following steps:
Watch Window notes:
To delete a certain cell(s) from the Watch Window, select the cell you want to remove, and click the Delete Watch button:
Like any other toolbar, Excel's Watch Window can be moved or docked to the top, bottom, left, or right side of the screen. To do this, just drag the Watch Window using the mouse to the location you want.
For example, if you dock the Watch Window to the bottom, it will always show up just below your sheet tabs, and let you comfortably inspect the key formulas without having to repeatedly scroll up and down to the formula cells.
And finally, I'd like to share a couple more tips that might prove helpful for evaluating and debugging your Excel formulas.
Formula debugging tips:
This is how to evaluate and debug formulas in Excel. If you know more efficient ways, please do share your debugging tips in comments. I thank you for reading and hope to see you on our blog next week!
14 responses to "How to edit, evaluate and debug formulas in Excel"
Hello Svetlana,
Hope you are doing well.
Is there a formula to get only the year from a full date, e.g. we have the date 01-Feb-16 and we need to get the year "2016" in the cell next to date.
Warm Regards,
Suliman
Hi Suliman,
Sure, you can use the YEAR function for this. The formula can be as simple as =YEAR(A1) where A1 is a full date.
Hi Svetlana,
Thank you very much.
how do i Attach a macro to the button so that the Price is sorted in ascending order each time the button is clicked.
I want the table (on the Price worksheet) used as a lookup table using a named range called Pricetab. When a Code is entered on the invoice, the Description must automatically be copied from the table into the Description column.
Dear Karen/Kevin,
Computer problem. Please help me if you can. Maybe along the lines of locked cells or something of the sort.
Thank you for any suggestion that may help, no
matter how far out they may seem.
1. In M/soft Excel work sheet I am using 15 sets of columns. I have formatted the columns in sets of 3, e.g. A,B,C then D,E.F ad infinitum.
2. The formula works well on the 1st twelve sets, but will not be accepted on the 13th set, but from there to the end it works again.
3. Can you tell me what is likely to have happened, or what I should be looking for?
4. The only other thing I could do is to manually work out the answers and type them in – 313 times!
Hello Svetlana,
I have a cell with the formule: "=[@AantalPerEenheid]*[@Kolom1]"
I can find the source of the reference @AantalPerEenheid by using the "Formula" > "Trace precendent", in which it highlights and i can find it.
The field @Kolom1 appears to be a function which uses multiple cells.
I also cannot find @Kolom1 in the list on Forumula > "managing names"
Any suggestions how i can find this magic "@Kolom1", so i can have a look at it's logica?
I solved it myselve by performing the following actions:
- Select all
- Layout >> visible = true
- Layout >> row height = 15
- Layout >> row width = 8.43
The field was really small and invisble!
Pls solve this
Designation B. Pay Gr.Pay Total DA @136%
Junior Asstt. 6560 1900 8460 11506
Junior Asstt. 6560 1900 8460 11506
Total 1 13120 3800 16920 23011
Designation B. Pay Gr.Pay Total DA @136%
Junior Asstt. 6560 1900 8460 11506
Junior Asstt. 6560 1900 8460 11506
Total 1 13120 3800 16920 23011
Pls solve this DA 136% result 23012(total). But excel sum formula shows 23011.
How to accurate with excel formula.
Thanks a lot!
Did you miss one for the keyboard lovers? Ctrl + A to jump from the formula edit within the cell into the formula bar (only works if you have a formula, not with a value)
Hi Jo,
Yep, I missed that. Just added this tip to the article. Thank you!
Thanks a ton Svetlana for such a detailed article on debugging.
In Selection of Multiple Cells how to make debug the formula?
Eg : A1:C2000 in this range we made formula data it has linked to "G" and "H" Column as per below, assume below thousands of Transaction are there,,,,
Formula Result
Row 1 =G1+H1 1,000 (Assume G1=500, H1=500)
Row 2 =G2+H2 1,500
Row 3 =G3+H3 2,600
Next i will select all data and i have to make Debug all cell at a time,but result should be like =500+500 (Row 1)