Test the Data Table’s source formula with each input value manually, because a Data Table repeats that formula and will propagate any #N/A produced by the underlying calculation.
- Note the cell specified as the Row input cell or Column input cell.
- Temporarily enter one of the simulation values directly into that input cell.
- Inspect the original output formula—the formula linked from the corner/header of the Data Table.
- Repeat with a value whose table result currently shows
#N/A.
If the original output also becomes #N/A, fix that formula or its source data first. #N/A generally means a formula cannot find the requested value. For lookup formulas such as XLOOKUP, VLOOKUP, or MATCH, check that:
- The lookup value exists.
- The lookup value and source value have the same data type—for example, both are numbers rather than one number and one text value.
- Referenced cells do not already contain
#N/A or NA().
Use Ctrl+1 → Number to compare formats. If an entire column contains numbers stored as text, set the intended format and use Data → Text to Columns → Finish to convert it.^1^
Do not use IFERROR merely to hide the problem during diagnosis. After correcting the source, it can provide an intentional fallback where a missing result is valid:
=IFERROR(XLOOKUP(A2,LookupRange,ResultRange),"Not found")
Since the Data Table has already been created, inspect its configuration rather than recreating it blindly.
For a one-variable Data Table:
- Values running down a column require the model’s variable cell in Column input cell; leave Row input cell empty.
- Values running across a row require the variable cell in Row input cell; leave Column input cell empty.
- The formula at the top edge of the Data Table must ultimately depend on that same input cell.
For a two-variable Data Table:
- The top-left corner contains the output formula.
- Values across the top row map to Row input cell.
- Values down the first column map to Column input cell.
- The output formula must use both input cells.
Select the complete Data Table range, then open Data → What-If Analysis → Data Table and inspect those references. Excel supports only one or two variables in a Data Table.^2^
Also confirm that neither the output formula nor any formula feeding it refers to a result cell inside the Data Table. That would create a circular dependency.
3. Force the Data Table to recalculate
A correctly configured table can remain stale when calculation is set to Manual or excludes Data Tables.
In Excel for Windows:
- Open File → Options → Formulas.
- Under Workbook Calculation, select Automatic.
- Open Formulas → Calculate Now.
If the workbook intentionally uses Automatic Except for Data Tables, select the Data Table formulas and press F9 to calculate them manually. Calculation settings in desktop Excel affect all open workbooks.^3^
If the source formula calculates normally for every test value, the input references match the table orientation, and recalculation still produces #N/A, the next details needed are:
- The formula in the Data Table’s top-left/header cell.
- Whether it is a one- or two-variable table.
- The selected Row and Column input-cell references.
- One input value that reproduces the error.
References
- How to correct a #N/A error | Microsoft Support
- Calculate multiple results by using a data table
- Change formula recalculation, iteration, or precision in Excel