How to Evaluate LET Formulas in Excel with F9
Question details
The user wants to inspect complex LET formulas by evaluating intermediate temporary variables using the F9 key.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Debugging a complex spreadsheet formula that utilizes the LET function to isolate errors and verify logic step-by-step.
- Observed behavior
- Pressing F9 to evaluate a temporary variable inside the LET function fails, often returning a #NAME? error because F9 evaluates highlighted segments in isolation and cannot resolve the defined temporary names.
Verify that your LET formula is syntactically correct and carefully select only the specific parts of the expression you want to evaluate to avoid syntax errors during debugging.
Output Intermediate Variables Directly as the Result
Since F9 struggles with temporary variables, modifying the LET function to output the specific variable is the safest and most reliable way to inspect its value.
The LET function structure consists of variable name and value pairs, followed by a final calculation. By replacing the final calculation with the name of your temporary variable, you can display its exact output in the cell without permanently breaking the formula.
Select the cell containing your LET formula and click into the Formula Bar at the top of the spreadsheet.
Locate the last argument in the LET function, which represents the final calculation of the formula.
Delete the complex calculation and type the name of the temporary variable you want to evaluate.
Press Enter to view the output of the selected variable directly in the worksheet cell. Once verified, change the final argument back to your original calculation.

Temporarily Replace Variables with Direct Expressions
Manually substituting the temporary variable name with its actual defined expression allows you to use the F9 key normally.
Use the Evaluate Formula Tool in WPS Spreadsheets
While debugging specific dynamic array functions manually can be error-prone, WPS Spreadsheets offers a built-in 'Evaluate Formula' tool that lets you sequentially step through complex functions to seamlessly identify errors.
- 1. Open your spreadsheet in WPS Office: Launch WPS Spreadsheets and open the workbook containing the complex formula you need to debug.
- 2. Select the formula cell: Click on the specific cell that contains the error or the logical structure you want to inspect.
- 3. Access the Evaluate Formula tool: Navigate to the 'Formulas' tab on the top ribbon menu and click on the 'Evaluate Formula' button.
- 4. Step through the calculation safely: Click the 'Evaluate' button in the popup dialog box to watch the formula calculate step-by-step, revealing intermediate values without altering your original cell data.

Frequently Asked Questions
Why do I get a #NAME? error when pressing F9 on a LET variable?
The #NAME? error happens because the F9 key attempts to evaluate the highlighted text in isolation. Since the temporary variable is only defined within the specific context of the LET function, the spreadsheet engine does not recognize it when evaluated independently.
Does the Evaluate Formula tool work better than F9?
Yes, using the Evaluate Formula feature is generally safer and more comprehensive. It steps through the logical sequence of your entire function step-by-step, whereas highlighting specific segments and pressing F9 can lead to accidental hardcoding if you forget to press Esc.
How can I revert my formula if I accidentally saved an F9 evaluation?
If you highlighted a formula segment, pressed F9, and accidentally pressed Enter instead of Esc, the formula is replaced with a static value. If the document is still open, immediately press Ctrl + Z to undo the action.
Are there other tools for debugging DAX or Power Query?
For broader debugging across Power Query (M code) and Power BI (DAX), F9 within the spreadsheet formula bar is ineffective. You will need to utilize the Advanced Editor in Power Query to step through applied steps, or use dedicated tools like DAX Studio for Power BI.




