Flawless Formulas in Excel: 4 Essential Tips
The beauty of Excel is its simplicity: If you enter your data correctly, it works. However, it can be ugly, especially when it returns a mess of formula errors, which sometimes are as understandable as Sanskrit.
We have gathered some tips that will help you root out the potential problems in formulas. If you can identify the issue quickly, then we’ve done our job. As for helping you fix it, that’s for another post.
A circular reference sends Excel into an endless loop where it will never stop calculating the cell. Excel goes around and around, never stopping to give us a final number…
1. Formula Evaluation Tool (Excel 2000, 2002, 2003, 2007)
by David McQueary
If you’ve ever created a formula, you no doubt have come across a dreaded #N/A, #DIV/0 or other type of error. This can be frustrating, especially if the formula you entered is long and complex. Sometimes it is not easy to see what is causing the malfunction, and trying to read through the formula to spot the offender is not always a fruitful effort. Excel offers a Formula Evaluation tool, which assesses a formula step by step, showing each calculation and enabling you to view exactly where the error occurs. Here’s how:
Excel 2000, 2002, 2003:
1. Click Tools and move your mouse over Formula Auditing.
2. Select “Evaluate Formula.”
3. Click the Evaluate button in the bottom left corner to evaluate the formula step by step.
Excel 2007:
1. Click Formulas and select “Evaluate Formula” from the “Formula Auditing” group.
2. Click the Evaluate button in the bottom left corner to evaluate the formula step by step.
***
2. The F9 Key (Excel 2000, 2002, 2003, 2007)
By Ronnie Merritt
Do you have a formula that is not working as expected? If you want to see where it is failing, you can use the F9 key to convert any portion of a formula to its actual value.
To demonstrate this, type any numbers into cells A1 and A2 in an Excel worksheet. In cell A3, type the following formula:
=A1*A2
Press Enter to enter the formula. The result should be the product of the values you typed into cells A1 and A2. Now select cell A3 and click into the Formula Bar, which is located above the column letters. This is the area that shows you what a cell actually contains, so it should be displaying the formula. Using your mouse, select A1 in the Formula Bar, then press the F9 key on your keyboard. The Formula Bar will show the value that was entered into cell A1.
This method can be particularly useful for troubleshooting large and complicated formulas, because it simplifies the task of viewing the result of each portion of the formula. Just remember to use the Esc key after using F9 in the Formula Bar. Otherwise, those converted values will be saved.
***
3. Trace Precedents and Dependents (Excel 2000, 2002, 2003, 2007)
by Stephanie Hausman & Jeff Bowman
Formula auditing helps you locate the cells that make up the formula and any other cells that may be affected by the formula. Two useful features in formula auditing are the Trace Precedents and Trace Dependents arrows.
You can use these features by following the steps below for your version of Excel.
Excel 2000, 2002, 2003:
1. Click View and move your mouse over Toolbars.
2. Make sure the Formula Auditing toolbar is enabled.
3. Select the cell you would like to analyze.
4. Select “Trace Precedents” or “Trace Dependents” to to view which cells a formula is based on, or which other cells are based on the selected cell.
5. When finished, select the “Remove Arrows” option to remove all traces.
Excel 2007:
1. Click the Formulas tab of the Ribbon.
2. Select the cell that you would like to analyze.
3. From the “Formula Auditing” group in the Ribbon, select “Trace Precedents” or “Trace Dependents” to view which cells a formula is based on, or which other cells are based on the selected cell.
4. When finished, select the “Remove Arrows” option to remove all traces.
***
4. Circular References (Excel 2000, 2002, 2003, 2007)
by Matt Mahoney
In Excel, if we are not careful, we can cause what is known as a circular reference. This means that Excel is calculating a formula in a cell that refers to itself. For example, say you have the values 1, 2, 3, and 4 in cells A1, A2, A3, and A4. In cell A5, we want to sum those values, and here is the formula we use: =SUM(A1:A5).
The formula includes a reference to the cell that it is in, so when Excel tries to calculate that value, it adds the values in the first four cells, 1+2+3+4, which is 10, and then it tries to add that value to the current value of A5, which it thinks is 10. Here is what Excel is thinking: “OK, my value in A5 is 10, because I am adding up those four numbers, but I also need to add A5 itself. So that means my value in A5 is 20, but I cannot forget to add those four numbers, which makes the value in A5 actually equal to 30…” This can get quite confusing.
A circular reference sends Excel into an endless loop where it will never stop calculating the cell. Excel goes around and around, never stopping to give us a final number, because it is constantly increasing the number (in our example, at least).
But, Excel is smart enough to predict a circular reference, and it gives us a warning on the Status Bar at the bottom of the window. If you ever see the word “Circular” on the Status Bar, Excel is telling you to fix the error.
Here is how to locate and fix any circular references. Follow the instructions for your version of Excel:
Excel 2000, 2002, 2003:
1. Click on View in the menu, and select Toolbars.
2. From the list of Toolbars, select Circular Reference.
3. Using the drop-down menu on the Circular Reference toolbar, you can view and select any cells that have a circular reference. The drop-down list displays the location of the cell, in this case $A$5 to indicate cell A5.
4. View the formula in that cell by examining the Formula Bar.
5. To remove a circular reference, make sure that the formula in that cell does not refer to the cell itself. If we have a formula in cell A5, we cannot have the formula refer to cell A5, or we will still have a circular reference. For our example above, we could change the formula to read: =SUM(A1:A4). If we remove the reference to cell A5 from the formula, the circular reference disappears.
Excel 2007:
1. Click the Formulas tab in the Ribbon, and select “Error Checking” from the Formula Auditing group.
2. Select Circular References, and a list of all the cells in the worksheet with circular references is shown. The drop-down list displays the location of the cell, in this case $A$5 to indicate cell A5.
3. View the formula in that cell by examining the Formula Bar.
4. To remove a circular reference, make sure that the formula in that cell does not refer to the cell itself. If we have a formula in cell A5, we cannot have the formula refer to cell A5, or we will still have a circular reference. For our example above, we could change the formula to read: =SUM(A1:A4). If we remove the reference to cell A5 from the formula, the circular reference disappears. Now that you have removed your circular references, Excel will calculate the formulas in the worksheet normally.
Have a favorite Excel tip or trick you’d like to share? Send it my way or post it in comments.
MORE INFO IN: Desktop Application Support | PC Helps eTraining | Contact PC Helps
Recent Comments