If you’re struggling to work out how a formula works, or why it doesn't, it can be very helpful to evaluate selected components one at a time. Excel now has three ways to do this and two out of three ain’t bad.
Introduction
Often, when troubleshooting a formula, or just working out how it is intended to work, it can be useful to evaluate individual components. This used to require the use of the Evaluate Formula feature to work through a formula step-by-step until you reached the component in question, or flirting with catastrophe. Now, there is an easier way.
Evaluate Formula
The Formulas Ribbon tab, Formula Auditing group, Evaluate Formula command evaluates each component of a formula and can also ‘Step into’ formulas in cells that feed into the selected formula. In this example, we are using a formula to convert dates entered in the American format to dates recognised in the rest of the world. We have used some ‘helper’ cells to hold some intermediate calculations. Having selected cell D18, we have clicked Evaluate Formula and clicked the Evaluate button to evaluate the contents of cell A18, then twice again to evaluate B18 and B18+1. The next Evaluate click selects C18. With the C18 reference in our formula selected, the ‘Step in’ button becomes available because C18 itself contains a formula. Clicking on ‘Step in’ moves the active cell to C18 and allows us to step through that formula, including using ‘Step in’ again to select A18 as part of the formula in C18:
Having evaluated all the supporting formulas and cells required, we can then use the ‘Step out’ button to return to evaluate the rest of the main formula, through to the eventual result.
As useful as Evaluate Formula can be, it does require working through the formula from the beginning rather than being able to go directly to a particular component.
F9 – Beware! Extreme Danger!
There is a way to go directly to our chosen component within a formula but, as we have mentioned, it is fraught with danger. If you click a cell to edit it in the formula bar, or double-click to edit it within the cell, you can select the component and then press the F9 key to replace that element of the formula with its result. The jeopardy comes from the fact that pressing Enter after you’ve pressed F9 will make your change permanent, possibly replacing a key element of your formula with a fixed value and preventing it from evaluating correctly in the future.
Tooltip preview
Although the Evaluate Formula dialog provides additional functionality that’s useful when working with particularly complex formulas, it’s now much easier and safer to get a quick preview of the result returned by part of a formula. Either in the formula bar, or directly in a cell, you can select the required component and just hover over it with the mouse. A tooltip will display the evaluated result without changing the formula itself:
This change was released to the Windows Current Channel in May, from version 2303 (Build 16227.20212). Details of updates and associated release dates can be found in the ‘Excel Features Flyer’:
https://onedrive.live.com/View.aspx?resid=E07B6F5DD91EC58B!261&authkey=!AMNbD8E1w2sfb9c
Links
How to Review a Spreadsheet Part 5: The Formula Auditing Group
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.