Excel Data Table not calculating correctly

Excel Data Table not calculating correctly

Excel Data Tables allow you to run 1, 2 or more sensitivities within a financial model. However, we often get queries about an Excel data table not calculating correctly (typically all answers are the same).

This is generally caused by one of the following:

  1. Push the F9 function key. If the data table updates it means that your spreadsheet was set to Manual calculation or Automatic except for data tables. To make the update automatic you need to go the the Formula Tab, click on Calculation Options, and choose Automatic. Alternatively leave as is and remember to push F9 when you want to see updated results
  2. You have mixed up the column and row references. Try and swop them around to see if it works (don’t forget to push F9 as per above as you could have both problems)

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