OmniDocHow to use Excel formula evaluation function: view the calculation process step by step
When the formula result is incorrect but the reason cannot be found, you can use the formula evaluation function of Excel to expand the calculation process layer by layer, locate the error section, and explain the operation steps, common mistakes and modified verification methods.
Key points of this article
- Formula evaluation only displays the calculation process and does not change the formula or data in the cell.
- Starting from the inner layer of the formula and evaluating it step by step, it is easier to find the wrong section than just looking at the overall result.
- When encountering a cell reference, expand to view the referenced content. After confirming that it is correct, collapse it and return to the original formula to continue.
- Text numbers, extra spaces and search area misalignment are the three common reasons for abnormal formula results.
- After repair, it is necessary to re-evaluate and check the intermediate results and final results at the same time to avoid repairing only part of the problem.
When making tables, it is very common that the results calculated by the formula do not match the expectations. For example, in a sales table with more than ten rows, the result of a conditional summation formula is missing. It often takes a long time to look at it cell by cell, and the reason may not be found. This article is written for people who often use Excel for statistics, reconciliation, and summary. It explains the method of evaluating Excel formulas, that is, taking a formula apart and looking at what each section of it calculates step by step in the order of calculation.
After reading this article, you should be able to find the location of the formula evaluation function, understand the role of each operation, use it to locate the error section, and know how to change it after finding it. The following operations are described in general Excel usage. The interface locations of different versions may be slightly different. If you cannot find it, you can search for "formula evaluation" in the software help.
The formula result is wrong. How to find the error step by step? One sentence conclusion
You can use formula evaluation to expand the formula in calculation order, and only execute a small section at a time. Whichever section's intermediate results are not as expected, the problem lies in that section. The following table provides recommended practices based on common situations.
| your situation | Recommended practices | reason |
|---|---|---|
| The formula is short and the results are obviously abnormal. | Check the referenced cell contents first | The short formula can usually be located by looking directly at the reference. |
| Multi-level function nesting, such as VLOOKUP inside IF | Use formula evaluation to execute step by step starting from the inner function | You can see the return value of each layer of function |
| The result is an error value such as #N/A | Evaluate step by step to see which step starts to report an error | Errors often start at a search step and propagate outward |
| The formula references another worksheet or workbook | Expand the referenced content to view | Can confirm whether the data source itself is correct |
| I just want to confirm the result of a certain section of the formula | Select this section and press F9 to view it | No need to follow the entire formula step by step |
Before you start: Confirm these points first
Taking a minute or two to prepare before you start can reduce the back and forth.
- Confirm that the cell to be checked is selected. Formula evaluation is based on the currently selected cell. If you choose the wrong one, you will see the calculation process of another formula, which is easy to misjudge.
- Confirm that the referenced data is completely visible. If the formula refers to another table or file, open that table or file first. The source file is not open or has been moved. You may not see the content you expect when you expand the reference.
- Keep a copy. The formula evaluation itself does not change the formula, but it is easy to modify the cells during the troubleshooting process. Save a separate copy so you can roll back if something goes wrong.
- Confirm the number type of the table. Many "formula calculation errors" are actually caused by numbers being saved as text. Such problems are easily exposed during evaluation and need to be paid attention to in advance.
- If the data comes from PDF, organize it into a table first. The PDF editor of OmniDoc desktop client supports OCR recognition of scanned PDFs and can be exported to Excel. After exporting, you still need to check the numbers item by item with the original file to confirm that they are correct before starting to troubleshoot the formula.
Use Excel formulas step by step to evaluate and view the calculation process layer by layer.
The following example is a formula: first use the search function to find the price corresponding to cell A2 in the price list, then determine whether the price exceeds 100, and return "high" or "low". If there is an extra space at the end of the product name in A2, the search will fail and #N/A will be displayed. The formula itself seems to be fine. Only by executing it step by step can you see which step is wrong.
- Select the error cell. Check the formula bar to make sure it shows the complete formula you want to troubleshoot.
- Open formula evaluation. Find "Formula Evaluation" in the formula-related ribbon of Excel. After opening, a dialog box will pop up, showing the current formula and underlining the part to be calculated next.
- Execute evaluation. The underlined part will be replaced by the calculation result. Only a small step is calculated each time it is executed, so the return value of each layer can be clearly seen.
- Expand and view when encountering a cell reference. Now you can see the actual contents of the referenced cell. In the example, you will find that the content of A2 is followed by a space, which is why the search fails. After confirmation, collapse the expanded content and return to the original formula to continue execution.
- Execute step by step to the end and record the intermediate results. Write down the return value of each step and find the step where the exception occurred for the first time. In the example, the exception occurs in the search step, and subsequent judgments and returns are the continued transmission of error values.
- Return to the editing bar to modify. After finding the problem, correct the formula or source data in the edit bar. For example, use a function that removes spaces to process the lookup value, or clean up the spaces in the source data.
- Re-evaluate and confirm. After modification, open the formula evaluation again and execute it from the beginning to confirm that the intermediate results of each step are in line with expectations, and the final result is also correct.
Check each item after completion
Once you get the correct results, don't close the file immediately. The following suggestions should be checked one by one.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Are the intermediate results as expected? | Check the return value of each step step by step and compare it with manual calculation | Go back to the step of the first exception and modify it. |
| Whether the citation scope is correct | Expand the reference to see if the table header is included and if the new rows are omitted | Adjust the reference area and confirm the boundary of the range before modification |
| Whether the number is saved as text | The number is on the left and there is a small green triangle on the cell corner, which is mostly text. | Re-evaluate after processing with the sorting or numerical conversion function |
| Whether the search methods match | Find out whether the last parameter of the function is set to an exact match | When an exact match is needed, change the parameter to 0 or FALSE |
| Is the result consistent with the original data? | Several rows are randomly selected and manually checked with the original data or the original PDF | Go back to the source data to check whether the import is complete |
# Core comparison
When checking formulas, there are three common practices, each of which has its own applicable scenarios.
| Method | Suitable for the scene | Matters needing attention |
|---|---|---|
| Formula evaluation | Multi-layer nesting, want to see the calculation order completely | For viewing only, the formula will not be changed |
| Press F9 after the section is selected | I just want to know the value of an expression in the formula. | Press Esc to cancel after checking to avoid replacing the formula with the result |
| Detach to secondary column | The formula is very long and the intermediate results need to be retained for a long time. | The contents of the table will be added and can be deleted after troubleshooting. |
# frequently asked questions (FAQ)
1. Will formula evaluation modify my formula or data?
No, I won't. The evaluation of the formula only shows the calculation process, and the formula in the cell remains the same. Only if you modify it manually in the edit bar will you change the formula or data.
2. What does it mean to show # Nram An or # validated after evaluation?
These error values indicate that a step cannot be completed. Common reasons include that the lookup value does not exist in the source data, that the number is saved as text, or that the type of Operand does not match. In step-by-step evaluation, the first step in the error is the starting point for troubleshooting.
3. The formula refers to another file, but what if you can't see the content when evaluating it?
First open the referenced file, and then re-select the formula to evaluate. If the source file has been moved or renamed, the reference is invalidated and the path needs to be corrected in the edit bar.
4. The form is from PDF, and the numbers don't match. How to check it?
First verify that the import process is complete. The scanned version of PDF can be OCR using the PDF editor of the OmniDoc desktop client, and then exported to Excel. There may be a recognition error in the OCR result, which needs to be checked item by item with the original PDF, and then return to the formula evaluation check.
5. Do you want to keep the auxiliary column after the inspection?
Let's see if we still need it. Auxiliary columns take up table space and may make it difficult for others to understand the intention of the formula. After confirming that the results are correct, it is recommended to delete or indicate the use next to it.
# Summary
The value of the evaluation of Excel formula lies in dividing a calculation into a process that can be observed step by step. Start from the inside, check when you encounter a citation, find the first abnormal step, and then modify it. Most formula errors can be located within a few steps.
