Here’s how you can use the Data Tables option in the What-If Analysis group in Excel:
You can download the file here to follow along. If you see a preview, look for the download arrow in the upper right hand corner.
Data Tables can be used to create a table showing the results of a formula with one or two variables. I’ve created a scenario for each, using running as an example. In the single variable scenario, I want to know how many days, hours, and minutes I will have run in a year based on running 8:00 minute miles. The variables run (pardon the pun!) from 5 miles per week up to 40 miles, in 5 mile increments:
The first step is to create a formula that includes the variable. I’ve done so here, with the formula in cell E5:
Next, I want to set up the table where the results will be posted. Notice I have my variables running across the top of the table. You can also do this vertically:
The next step is to copy the formula into the row (or column if running vertically) where you want the results to be. All you need do is reference the cell using “=” and the cell reference where the formula resides:
Then, highlight the cells of your table, including the variables and the formula reference, Then click on the What-If Analysis option in the Data Tools group on the Data tab and choose Data Table…:
A dialog box will appear. You will want to populate the “Row input cell:” field with the variable in your formula table:
Click OK and Excel will populate your table with the results for each of the mileage variables:
The two-variable scenario is very similar. The main differences are that you will insert the formula in the upper left hand corner of the table, and fill in the variables for both the rows and columns:
Click OK and Excel will populate the table with the results based on the two variables we’ve identified:
Data Tables can be used for a variety of single or double variables from profits based on overhead expenses and sales numbers, to stock returns based on quantity and profits. Try several variations and I think you will be pleased with the results.