This action will open the Evaluate Formula dialog box, which will show the formula that will be evaluated. For this example, we have selected cell D2: To use this command, first select the cell that contains the formula that you would like to work with. The Evaluate Formula command takes this concept one step further by showing you every single calculation that was used by the formula to reach its end value. Tracing formula precedents and dependents allows you to quickly and accurately track what a selected formula is doing. If you press enter when in F9 mode, the formula will accept the calculated value and not the original formual. You must press ESC to return the formual to the original state. However using F9 comes with a heath warning. This will calculate that part of the formula for you and show you the result in the formula. Highlight any part of the formula in the formula bar and press F9. When you are entering a formula or in edit mode, you can check for accuracy as you go along.
When a formula is in edit mode, the corresponding cells in the workbook are color coded. When you are working with a formula there are a number of options to help you avoid errors or to correct errors.Ī quick reminder for those that have forgotten, you can edit the formula in a selected cell by pressing F2. Where reference is the cell containing the formula that you want to display. If you are using later versions of Excel, you can also use the function FORMULATEXT. This command will also expand the column widths for all columns so that formulas are easier to read:Ĭlick Formulas → Show Formulas again to return to the default view that shows calculated values and returns columns to their normal widths. To do this, click Formulas → Show Formulas:Īll formulas will now be displayed in full. When working with multiple formulas in your workbook, it is sometimes easier to view all of the formulas in the worksheet rather than their computed values.
All arrows on the current workbook will be removed: The drop-down command allows you to remove all arrows, remove only precedent arrows, or remove only dependent arrows:įor this example, we have clicked Remove Arrows. To remove the arrows, click Formulas → Remove Arrows (drop-down arrow):Ĭlicking on the Formulas → Remove Arrows command directly will remove all arrows from the worksheet. If we Click cell B4 and then click Formulas → Trace Dependents:Īnother blue arrow will be drawn from the selected cell, pointing towards the dependent cell, which in this case is the formula in cell D4:Īs you can see in the images, the Trace Precedents and Trace Dependents commands can be used at the same time. However, the Loan Period (B) column does have dependents. This command will not work for values in the Monthly Payment column (D) because it has no dependents. The Trace Dependents command works the opposite way of Trace Precedents: it highlights all the items that depend on the value in the current cell. In this particular example, you can see that cells A2, B2, and C2 are all used by the formula in D2 to calculate the end value: Each blue dot on this arrow corresponds to a value that is used in the formula, while the arrowhead points to the end value. You will now see a blue arrow drawn across the current row, where all of the data used by the currently selected formula appears. Next, we clicked Formulas → Trace Precedents: This cell contains a formula that calculates monthly payments: In the image below, we have selected cell D2. This allows you to quickly find and identify what values a selected formula is dependent on. Tracing precedents allows you to backtrack through all of the cells that are used to calculate a formula. Tracing Formula Precedents and Dependents In this article we will cover step by step the options available to help resolve errors in Excel formula. This includes the ability to evaluate formula and set error checking options. But thankfully Microsoft has equipped Excel with tools to help resolve errors in Excel formula. It is not uncommon to encounter errors in spreadsheets.
0 kommentar(er)
