Computer Tutoring Logo

Excel Data Tables for WhatIf Analysis


How to use Data Tables in Excel 2016 for WhatIf Analysis?

I want you to imagine that you are buying a car. You need to take out a load for the car. Cars are expensive and money is tight, like it always is. If you could have a matrix of data that you could quickly cross reference to see how differing interest rates, periods and loan amounts will affect your monthly repaying would that help? A Data Table is fantastic for WhatIf analysis in Excel. This feature has been around since 2007 so if you happen to be using an older version of Excel then that's OK.

In this tutorial you will accomplish the following:

  • Create a Data Table
  • Use the Payment function to calculate a montly repayment
  • Create a drop down list
  • Use a Name Range with The Offset function to create a drop-down list
  • Little bit of VLooup

Phew! We have a lot to get through so click on the video to start.

One of things you might need is the formula for OFFSET which allows you to create a dynamic range as a named range. Pretty handy should you need a decent drop down menu for data validation. So here it is:

=OFFSET(Sheet1!$L$3,0,0,COUNTA(Sheet1!$L:$L)-1,1)

By the way here's the completed file. This is for those who would like to see what the resulting spreadsheet looks and feels like before you get started.