The data table is powerful tool, but it brings a new level of complexity which can result in more ways to break or create wrong results. This can cause wrong calculation output values or the table to not work at all. Below are causes of why your data table does not work, and how to fix it. It is not meant to be an exhaustive list, but it should cover 90% of the type of issues. I hope this is helpful to you!
If you do need any help, please leave comments in the bottom or contact us directly.
Terminology
- Input cells: The 2 row and column input cells that is used to store the data table test cases. They are the two cells you select if you select Data -> What-if Analysis -> Data Table
- Heading row / heading column: The heading row / heading column contains the input values that is put into the input cells one by one.
- Excel calculation: The original Excel calculation that is tested by the Excel data table and generates the output that fills a data table.
Data Table Limitations
- A data table’s heading row / heading column is dependent on, or indirectly dependent on another data table’s output.
- Symptom: Some very strange data table results. Some values may be the same throughout.
- Solution: Excel just does not allow this (even though this may make sense to us.) Use the Formula -> Trace Dependents tool to check your table’s heading row and heading columns to make sure it is not dependent on another data table’s output. If it is, you will have to hard code the values of the entire heading row/column.
- A data table’s heading row / heading column is dependent on a cell in the Excel calculation that changes due to the data table.
- As the data table changes the Excel calculation through the input cell, the row/column headings change too, due to its linking to the Excel calculation. So obviously, it will create some strange results, as the row/column heading values are different from before the calculation took place.
- Symptom: Some very strange data table results.
- Solution: Use the Formula -> Trace Dependents tool to check your table’s heading row and heading columns to make sure it is not dependent on a cell in the Excel calculation. If it is, you will have to hard code the value(s) that is dependent on the Excel calculation.
Row and Column Input Cells
- Row input cell and Column input cell are switched.
- You switch the row/column input cell, your data tables are inserting the wrong values in the wrong place.
- Symptom: Some very strange data table results.
- Solution: Row input cell refers to where the Row Heading inputs should go. Column input cell refers to where the Column Heading inputs should go.
- Row/column input cell are pointing at a cell which is not properly linked to the Excel calculation.
- You made the input cell but forgot to use it as part of your Excel calculation.
- Symptom: Same values throughout the row / column of a data table.
- Solution: Use the Formula->Trace Dependents to check what cells are linking to your input cells. Make sure all cells that are supposed to link to your input cell is correct. Then go to each cell to make sure the linking and the formula is correct.
- Heading row and column cells is not the right value because of display formatting.
- If you use formatting rules that hides a certain number of digits (showing millions in thousands, like this “#,##0,” etc.), and you are using the values incorrectly (treating the millions as if it was thousands).
- Symptom: The data table will show some very strange results.
- Solution: Show the heading row and column without the special formatting. Check all cells linking to your input cell to make sure the digits are treated correction. Then re-format your input cells.
- Hidden heading row / columns.
- Typically for formatting reasons, the heading row / columns are hidden, and the properly formatted labels are visible, but not the actual heading cells. This can cause problems when checking your formulas if you do not check for the hidden heading row / columns. While the visible labels are correct, the real heading rows / columns may be wrong.
- Symptom: Results that are off.
- Solution: Unhide all heading rows and columns and make sure the values showing are correct.