What-If analysis in #Excel

Basic of One way Data Table

This is an extension of the post Start the new decade with Goal Seek in #Excel.  

In that post, we talked about using Goal Seek to find the initiate savings required to obtain a total savings of $1000 at period end, given the other variable remains constant.

How about if we want to have a list of outcomes with different initiate savings?

Do we need to change the value one by one manually and then do the great copy and paste?  Like the screen-cast below… 🤦‍♂️

Excel Tip - OneWay Data Table

Of course we don’t!  We can do it with (One-Way) Data Table under What-If Analysis.

You may download a Sample File to follow along.

Note: You may want to read this post to understand the context before moving on.

1. Setting up the spreadsheet

Excel Tip - OneWay Data Table1

The range G4:H14 is where we are going to put the Data Table.

  1. The values pre-input in G5:G15 are the list of initiate saving (variables) that we want to examine.
  2. In H5, input =B4 (i.e. referencing to the variable)

Excel Tip - OneWay Data Table2.1

2. Inserting Data Table

Now we are ready to insert the Data Table.  Nevertheless, we have to select the range G5:H14 first. (tip: It’s up to you to have a shorter/longer list)

Excel Tip - OneWay Data Table3.0

With the range selected,

  1. Go to Data Tab
  2. What-If Analysis
  3. Data Table…

Excel Tip - OneWay Data Table

In the Data Table dialog box, reference to $B$5 (the variable) in the Column input cell:

Excel Tip - OneWay Data Table3.1

Excel Tip - OneWay Data Table3

Then OK

Here we go!

Excel Tip - OneWay Data Table4

Let’s watch it in action! 

Excel Tip - OneWay Data Table(End)Tip: We may change the values in column G if required

As simple as this! 🙂

Well, you may be thinking… can we add one more variable (Weekly incremental % in our example) so that we can have a cross tabular result listing multiple scenarios?  Yes, we can.  Let’s talk about it in the next post!  Stay tuned.  😉

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.