Home // Blog & Excel Tips // Excel Data Table not calculating correctly

Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. What is the shortcut to ONLY select the visible cells? Guess, then click 'See Answer' as it can be done.
2. How do you take all the formatting from chart (see 1 below) and PASTE it onto another chart (2)? Guess and then see how it can be done with 'See Answer'

 

 

Excel Data Table not calculating correctly

Comments Closed
Uncategorised

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