How to Fix Incorrect Inputs in an Excel Two-Variable Data Table
Question details
The user needs to correct a two-variable data table used for sensitivity analysis that is returning incorrect results because the inputs are linked to formulas rather than fixed values.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up a two-variable data table to perform a sensitivity analysis.
- Observed behavior
- The data table displays incorrect variables or calculation errors because the row and column input values are mistakenly calculated by percentages or linked to other inputs.
Before modifying your data table, locate the source formula you are testing and ensure you know exactly which original input cells dictate your row and column variables.
Replace Formulas with Hard-Coded Values in Data Table Inputs
Data tables require hard-coded constants for row and column input variables rather than cell references or formulas.
Excel's two-variable data table tests different values for a formula simultaneously. If the input variables (the row and column headers of your table structure) are linked to other cells via formulas or dynamically calculated percentages, Excel cannot properly substitute the varying values into the formula to calculate the results.
Click on the row and column header cells in your data table structure and check the Formula Bar at the top of the screen.
If you see a formula (for example, '=A1*10%') or a cell reference (like '=C4'), delete it. You must type the actual numerical value directly into the cell.
Highlight the entire data table range, making sure to include the top-left intersection cell containing the master formula, the row inputs, and the column inputs.
Go to the 'Data' tab on the ribbon, click on 'What-If Analysis' in the Forecast group, and select 'Data Table'.
In the Data Table dialog box, select the actual original input cells in your main calculation model for the 'Row input cell' and 'Column input cell', then click OK to recalculate.
Create Two-Variable Data Tables with WPS Spreadsheet
WPS Spreadsheet provides powerful What-If Analysis tools, including full support for two-variable data tables. It allows you to build complex sensitivity analyses accurately and is a free, lightweight alternative to Microsoft Excel.
- 1. Prepare your data: Open your workbook in WPS Spreadsheet. Ensure your data table structure has the master formula in the top-left corner and hard-coded values in the row and column headers.
- 2. Select the table range: Highlight the entire range of your data table, including the formula and all input variables.
- 3. Access What-If Analysis: Navigate to the Data tab on the top ribbon, click on What-If Analysis, and select Data Table from the drop-down menu.
- 4. Generate results: In the dialog box, enter the references for your Row input cell and Column input cell, then click OK to calculate the sensitivity analysis.
Frequently Asked Questions
Why is my Excel data table returning the same value in every cell?
This usually happens if your workbook's calculation mode is set to 'Automatic except for data tables'. To fix this, go to Formulas > Calculation Options, and select 'Automatic', or manually press F9 to force the sheet to recalculate.
Can I use formulas in the input cells of a sensitivity analysis?
No. The row and column input values that form the headers of your two-variable data table must be hard-coded constants. If they contain formulas linking to other cells, the What-If Analysis tool cannot successfully substitute the variables to test the outcomes.
Where exactly do I put the formula in a two-variable data table?
The master formula you are testing must be placed in the top-left intersection cell of the row and column inputs in your data table range. The What-If Analysis tool uses this single master formula to calculate all the varying results inside the table grid.
