Create a Two Variable Data Table

This video is about creating a two variable data table feature. The use case for this is if you want to input two sets of varying pieces of data (two input variables) and see how the output changes. For example say you are taking out a loan and maybe not sure how much you want to take out. At the same time you are unsure of the rate you might get. What are the possibilities of monthly payments you'll have? It's almost endless because you can calculate multiple results with your two input variables. With a two variable data table you can a range of interest rate AND a range of loan amounts and let the handy-dandy data table feature figure out the different monthly payments that are possible. The example in the video will cover a mortgage loan payment scheme.
BTW there is a difference between creating a two variable data data and a one variable data table. To see how to create a one variable data table go to my other video at • Create a One Variable ...
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltrain...
📚 Excel Books & Tech Gear ➜ www.amazon.com...
⚙️ Tools: Screencasting ➜ techsmith.z6rj...
⚙️ Tools: Microsoft Office ➜ microsoft.msaf...
⚙️ Tools: TubeBuddy ➜ www.tubebuddy....
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to www.buymeacoff...
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~

[@daniellelavoie1173]

Thank you for posting this! I've been trying to figure this out for several hours and couldn't get the correct answer. Once I watched this, I FINALLY got the right answer! Thank you again!

[@DougHExcel]

You're welcome; glad you found it useful!

[@daniellelavoie1173]

I posted it to my instructor's curriculum/assignment page and she said she was going to post it for future classes because it was so good :) Thanks again!

[@mohammadpourheydarian5877]

simple, up to point, no waste of time. Thank you.

[@DougHExcel]

Hi Mohammad Pourheydarian...you're welcome, glad you liked!

[@rainonyoureyes]

Thank you so much ! I really appriciate your time and effort ! Your video is more helpful to me than my expensive textbook.

[@petercrawford5027]

Thanks! I've been trying to figure this out for hours!

[@tacsman]

Thank you so much for the easy-to-understand tutorial.

[@DougHExcel]

You're welcome!

[@Namasaty]

Thank you! I know this video was published ages ago, but I just missed a hw deadline because I didn't understand this, and now, at least I sort of do

[@DougHExcel]

Hi Janis Tyus, thanks for the comment!

[@yvonnelo4144]

really helpful!!!your voice is good and the explanation is clear! thanks so much.

[@DougHExcel]

Hi Yvonne Lo...you're welcome, glad you liked!

[@HisLilyPrincess]

Why does everyone do interest rates?? Can't we do colored t-shirts and their costs or something? Puppies? Unicorns?

[@DougHExcel]

+pinkaj Garcia In the future maybe I'll use unicorns or UFOs.....

[@sylvesterthompson]

Having once been a trainer I can tell you from experience just dang near everyone can relate to purchasing a home or car. It's such a practical example.

[@jamesbharadwaj354]

thanks...you explained everything so clearly.

[@adfs87]

Thank you very much for this video, I too found it very helpful! Although it is not within the scope of this video, I'd like to add how to create the table quickly if you don't have a spreadsheet program that supports this function, meaning you would have to manually input the formulas. Eg. in Cell D9, I would use this "=PMT(D$7/12,$B$3*12,$C8)" and then drag. (I'm using Excel 2011 for mac and 2010 for windows, and this is what I have been doing, shame on me!)

[@YC-musica]

Thank you Doug!!! I am taking an online Excel 2013 class, and the Pearson textbook lab site videos and the textbook do not explain the concepts well at all. Your instructional videos are extremely clear, concise, and helpful. I don't think that I could have passed most of the advanced Excel assignments for this class without your "supplemental" lectures!

[@gardawg62]

Im having trouble with mine. mine is different as my teacher wants us to use monthly contribution and interest rate. When i do this i get nothing but zeros

[@zm2813]

Great video. Question: Should the value in B7 be linked to value in B4. At the end of the video when you change the number up top the number in B7 is different. Can you please clarify? thanks

[@tweetums42]

Thank you! Thank you! Thank you! You're a Lifesaver! How I wish my professor is as easy to comprehend as you!

[@DougHExcel]

Thanks tweetums42, glad it helped!

[@vishnupv9481]

thank you so much

[@DougHExcel]

Hi Vishnu Pv...you're welcome, glad you liked!

[@MJ-ql2lj]

Still clueless, but thanks. I'll drop the class.

[@mohammadpourheydarian5877]

don't drop it keep working you will succeed good luck

[@DougHExcel]

Hi M J, thanks for the comment! Hope you didn't give up, stay curious!

[@NorthJerseyGarden]

Thank you ! pretty cool and simple explanation

[@ArianaCorpuz]

Just saved my life on a homework problem. Thanks!!

[@coreyd5418]

You saved me too. Thank you!

[@malikdebracey4559]

this is my very first time using excel, i need help in creating a two way table for math 45 at city college . I need assistance in creating a Column % Rows for Categorical v. Categorical data and Quantitative v. Quantitative data. can someone help me please. its my final grade.

[@Kairenia]

Taking an online Excel class. You're 1000% better than this book. Thank you for saving my butt in understanding the basics.

[@Lukec169]

Thanks so much !!! try to figure this problem for so much time.

[@DougHExcel]

Happy to help!

[@victorblaer]

Hi, nice explanation. I would add stuff like, does the data table have to be on same sheet as the input data? what if you want to use a dynamic interest rate in your row part of your data table and have the rates change relative to that? So G7 = B2, and F7 = G7 - 1% (or whatev change you want) and H7= G7 + 1%. Also, a lot of times instead of actual values on your row, you want to see how your output changes as the interest rate chgs. Ex. G7 = 0% and other are +- 1%. :)

[@Asgardian93]

The value remains the same throughout the table, don't know what Im doing wrong?

[@s1lverrush55]

how can i make a chart in excel with two.... how to explain this, a row of numbers on the x AND y axis on a chart?

[@chomop77]

Thank you! This was very helpful and easy to follow.

[@mustaffabinhassan8225]

Thanks Doug. Your EXCEL videos are amazing to say the least.

[@hahohahohful]

Nice, thanks for the video! Are you a teacher yourself?

[@sonjagray7547]

thank you for all your help. you are clear and easy to understand!!!

[@saratsrinivas4857]

Thx a ton..........it's very useful......keep posting

[@apocalypsezombyRC]

You are a GODSEND! Thank you so much!

[@DougHExcel]

Hi Douglas Barfield...you're welcome, glad you liked!

[@cindyhutchison1260]

Saved my butt on homework due tonight by midnight. THANKS MUCH!!

[@bluephoenix387]

Thank you!!!! Your explanation clarified where the MOS Study Guide's wording really confused me!

[@bethred5272]

Thanks! Your videos are so clear, they're helping me cram in as much Excel Know How as I can in preparation for starting a new job.

[@DougHExcel]

Glad they are helping. Congrats on the new job!

[@engw7090]

Hi, thanks for the video. one qns, data table only works for single output? and the input maximum is 2?

[@DougHExcel]

assuming you mean two variables (i.e. x variable for the horizontal list of values and y variable for the vertical list of values), then yes, the input variable maximum is two.

[@SeymoreTanzarian]

you are a life saver! Im doing a 150 by 150 grid 2 variable data table and I was going column by column. thank you!

[@DougHExcel]

You're welcome, glad you liked it!

[@Jennafer211]

Thank you for the clear explaination!! You saved my excel test grade!

[@channykthx]

Thank you so much, trying to figure this out for time and finally got it lol

[@chuckcarr9323]

need some help using IRR LTV (rows) and purchase price(columns) Data table

[@DougHExcel]

Maybe this will help => kzbin.info/www/bejne/bqPKdaN4qr9lhpI

[@nickloke6404]

Thanks for the tutorial, I followed every step of the way but still found my values to be static, i.e. did not change with a new input variable. Then someone told me the missing step is that Excel needs to recalculate the output of all formula. You do so by hitting the F9 key (Fn F9 or Alt-Ctrl-F9 depending on your keyboard). Hope this helps!

[@DougHExcel]

Thanks Nick Loke for adding to the thread. You may also try to enable automatic calculation (it may be set to Manual). Formulas tab > Calculation Options

[@zhangshazi1994]

Thanks!!! helped a lot

[@horebiswajit2010]

THANKS

[@alistairong2228]

Thank you!

[@wintapang9013]

Thanks

[@fredy_antony]

Thank You ! Will never forget for life time.

[@Francisco50771]

Hello Doug, great work explaining. Thanks ☺

[@DougHExcel]

You're Welcome!

[@purnakumartfi]

thanks for your simple explanation sir

[@DougHExcel]

Glad you liked!

[@paulavanessamarin7722]

This was incredibly helpful!!! Thank you!

[@DougHExcel]

Hi Paula Vanessa Marin...you're welcome, glad you liked!

[@Bonitaclavel]

Thanks, Thanks, 1,000 Thanks.

[@DougHExcel]

Hi Claudia Lopez, you're welcome!

[@dezottis]

Excellent explanation! Thank you!

[@DougHExcel]

Hi Donald DeZottis, glad you liked it, thanks for commenting!

[@carmendong3384]

Thank you soooo much for the video!

[@DougHExcel]

You're Welcome!

[@christian_mancheno]

"Not 275,000%" lmao

[@DougHExcel]

Christian Mancheno, that is very interesting...thanks for letting me know.

[@modifiedjaymill]

You skipped steps

[@DougHExcel]

Michael Miller, that is very interesting...thanks for letting me know.

[@storage8797]

im clueless

[@DougHExcel]

Hi FartDog 22, thanks for the comment!

[@storage8797]

Doug H what? I looked at my comment history and appears this is my first comment, I have absolutely no memory of writing it or why I would. Especially considering I did understand this topic at the time.

[@storage8797]

But I appreciate the feedback!

[@sangeetakumari9957]

Mujhe akhi pakarta h sr

[@DougHExcel]

Sorry, don’t understand the comment/ question...