How to Use an Excel Data Table for "What-if" Analysis

An Excel Data Table is a great way to see the results of substituting two values in a formula. For example, to see what your monthly payment will be by changing BOTH the Interest Rate and the amount that you borrow.

However, reading about how to set up an Excel Data Table can be confusing. Where, exactly, do you place the Row Inputs and the Column Inputs?


Seeing how a Data Table is constructed is SO much easier. I invite you to watch this short Excel Training Video Lesson to see how to set up and use an Excel Data Table to analyze your data.

You can find additional resources on my website: www.thecompanyrocks.com - I look forward to your comments.

Category:

Howto & Style

Tags:

• Excel
• Data
• Analysis
• Tables
• Column
• Input
• Row
• Formulas
• Functions
• Substitute
• Values
• Lessons
• Tutorial

[AMiCIXELSYD]

Hi Danny, I just wonder if you can help me to see if there is another ways to set up my formula which I don't have to change monthly or yearly. I have an excel spreadsheet which keep track on of the sales for the pass couple years and I would like to set up the formula so at month end it will tell me how much sales for that month, and here is my formula

=COUNTIFS('Tracking'!$A:$A,">=­02/01/"&B20,' Tracking'!$A:$A,"<=02/28/"&B20­,'Tracking'!$L:$L,"L")

here is my formula

=c

[DannyRocksExcels]

@AMiCIXELSYD

My preference - in this case - is to use a Pivot Table to produce these results.

With a Pivot Table you would not have to change the formula, you would simply "point" your Pivot Table to the new data source each month.

The one thing your WOULD need to do is to GROUP the Date Field to produce Date, Month & Year Fields - This takes about 6 clicks of the mouse.

That is my suggestion.

Danny Rocks

The Company Rocks

[sarchnar9]

Hi Danny Rocks. i am currently student learning Excel, and I wounder if you could help me. i am looking for Formula and Function book. i dont know which book is the best and i am just a beginner. thank you, (God bless you)

[DannyRocksExcels]

@sarchnar9

I highly recommend the book "Excel 2007 Formulas" written by John Walkenbach. This is available for several versions of Excel. The Publisher is Wiley.

Good luck with your studies!

Danny Rocks

The Company Rocks

[abarashyasir1]

wawwww! thats really very informative and i got a lot of ideas in analysis.U did a goood job.

[DannyRocksExcels]

@abarashyasir1

Thank you for adding your comment. I am pleased that you picked up some good ideas from my tutorial.

Sincerely,

Danny Rocks

The Company Rocks

[abarashyasir1]

@DannyRocksExcels Dear could u please help me in defining the model that is relateed to risk and return.

[DannyRocksExcels]

@TrollUnderTheVolvo

Thanks! I DO hope that I was able to help you to complete your assignment.

Wehn I was learning how to use Excel, I almost always got confused by the explanations that I found in books on this topic. That is why I worked so hard to make my demonstration clear for my viewers to understand.

Danny Rocks

The Company Rocks

[TrollUnderTheVolvo]

I should have watched this 7 hours ago, when I started working on this stupid assignment!

[DannyRocksExcels]

@dbskholicable

My pleasure. Thank you for writing to tell me that you enjoyed my video tutorial.

Danny Rocks

The Company Rocks