Data table all same values

When first using Data Tables in Excel you may come across a situation where you feel you have done everything correctly, yet the data table is clearly wrong as all the numbers in the middle are exactly the same instead of something like the image below.

two-variable-data-table
A normal looking data table

This could be due to one of two reasons.

YouTube Data Tables has all the same values

Calculation Mode set to ‘Automatic except Data Tables’

First click on your F9 key. If the table updates itself it means that your calculation method is set to Automatic except Data tables. This is actually a good idea as data tables can be intensive, but if you want to see the table change all the time, click on FORMULA, then CALCULATE OPTIONS, the AUTOMATIC (as shown below).

data-table-all-same-values
Change your calculation mode to see the Data Table working

Your Row or Column input cell is incorrect

When you set up the data table it is important to make sure that you correctly assign the correct cell to the Row input cell and Column input cell. If you mix these two around, or click on the wrong cells, you will either get the same result or else nonsensical results.

If you are using Data Tables to run sensitivities then perhaps look at Excel's other sensitivity tools: What If Analysis in Excel

Part of Data Table changed, rest the same number

A little known problem with Data Tables, which happens most when the Data Table is very big, is that it stops working the moment you click in another cell.

So you MUST let the data table finish (look at the bottom right corner of screen until it stops calculating and then wait 5 more seconds!). For some reason a click stops the data table in its tracks so some cells will have been calculated and others all have the same value.

Data table slowing down Excel

Speed up a slow Excel spreadsheet