Hi, when I FOLLOW YOUR EXACT STEPS all my values in the G Column return 73,335. Can someone please tell me what I am doing wrong if I am following her exact steps ?
Leila, there is a really neat way to use data tables, eg to run a set of scenarios with many variable parameters. To do this, 1. set up the scenarios, one per row, with the parameters in as many columns as you want alongside. 2. Create a scenario selection cell that will look up all the parameters for a given scenario, using the scenarios number as a row offset, ie 1 will pick the items in the first scenario row. In this way, you can select a whole scenario's worth of inputs by changing one cell. 3. Create a data table with the inputs down the left being the sequence 1,2,3,4.... for the number of scenarios. In the columns alongside, put the values you want to store for each scenario. Now run the data table and it will process all the scenarios and produce a set of results for each. So in this way, you can change many variables using a data table, which is extremely useful.
Great video!! Alternatives: FV is also dynamic: =FV(SEQUENCE(17,,0.03,0.005)/12,20*12,-200) =FV(SEQUENCE(17,,0.03,0.005)/12,SEQUENCE(,7,4,4)*12,-200) Or an universal approach, an user friendly custom-made lambda helper function, for any function fn(x,y) WIDT(r,c,fn,[nm]) What If Data Table Lamda Helper Function. Arguments; r: rows vector(vert) , c: clms vector(horiz) , fn: lambda helper function , [nm]: results name (top left cell) =LAMBDA(r, c, fn, [nm], LET( w, IF(ISOMITTED(r), 1, ROWS(r)), l, IF(ISOMITTED(c), 1, COLUMNS(c)), m, MAKEARRAY(w, l, LAMBDA(x, y, fn(INDEX(r, x), INDEX(c, y)))), HSTACK(VSTACK(IF(ISOMITTED(nm), "Wht if", nm), r), VSTACK(c, m)) ) ) We can call: =WIDT(SEQUENCE(17,,0.03,0.005),20,LAMBDA(x,y,FV(x/12,y*12,-200))) for first first scenario =WIDT(SEQUENCE(17,,0.03,0.005),SEQUENCE(,7,4,4),LAMBDA(x,y,FV(x/12,y*12,-200))) for 2nd scenario The function lists the results with rows/clm headers. ✌
Leila, you are the best tutor I have ever listened too. All your presentations makes things look simple and flawless and they are very easy to understand. You are truly my MVP when it comes to Excel and Power BI
Leila, you are the best teacher ever! I just currently found your channel, and already using some new features you showed me. THANK YOU! You are brilliant!
Thank you Leila! I had used Data Tables long back and your video was a much needed refresher to revise and start using them again where needed. Great! 🙂
Yes terribly unintuitive when choosing the row and column input cell(s) considering they exist in the base formula. Using the first value in the column or row in the table being created would make more sense to me, but I guess doing it the way they are doing it allows the purpose of the numbers in the row or columns in the destination table be defined as they relate to the base formula. If so, the dialog to choose the inputs could still be made more intuitive so that the relationship being made is more prominently displayed and evident.
Thank you, Leila, for this wonderful tutorial! I am a professional UX/UI designer trying to switch careers and become a full-time data analyst or at least part-time. I've been working with SQL for a long time since I am a CS graduate and I recently learned SSIS, and Power BI on my own. I was actually having a little bit of a rough time with the concepts of data analysis and formulas, etc. so I decided to learn Excel. I stumbled upon Data Tables last week, I got stuck on it and I just couldn't understand why are data tables used and how to use them. It's been two weeks now, I tried searching around and watched numerous videos on RUclips but no one explained it better than you did.
Hi Leila, this is a great tutorial! I was hoping you could offer some guidance on a couple of questions. Q1: is there a way to dynamically change the size of the datatable? I'm thinking with Array Formulas or if the output can be dynamically changed to an Excel table. Q2: Is there a way to "consume" the data table that gets created? For example with PowerQuery, Python in Excel. Thanks!
I don't have a background in finances, but I manage my own budget in Excel. The other day I was wondering how I could make these kinds of projections. This is a great video! Easy to understand and informative. Thank you!
Thank you for adding some much-needed explanation and clarity to this element of Excel. A video is worth 1,000,000 (1,000²) words. The example helps demonstrate what's going on. Too bad the real world doesn't work quite so easily. "Yes, I need my interest rate to be twice as much to achieve my goals." and BOOM! The interest rate is double. The most controllable piece of the equation is the monthly contribution. But that's a completely different conversation. 😀 Hmm, now that might be a slightly better example with a double lesson: the data table and the contribution amount.
So nice to return to my standard early morning routine, 'Thursday's With Leila', i.e. hot coffee, biscotti and another insightful training video ... and I know exactly where I will employ this technique. Thank you ... thank you .... thank you. Sooooooooo nice to have you back ... 😍😍😍😍
I have been following your your videos for many years now. I am convinced that we all use not more than 10% of what Excel offers. Great video as always.
Yet again, your channel has made me more productive at work. I've been itching to make a scenario analysis table with a bunch of complex financial data with moving 1 or two variables... This did the trick! One other thing I'd add, is if you're just moving a columnar variable, you can have many different reference columns that all update with the far left column values! I also couldn't get it to work until I made the Column Input Cell on the same sheet as the What If table. Thanks!!!
Thank you very much. I used Access with mathematical formulas to create reports of hardware needs forecasting. Based on your explanation, I tried to do the same with "What If" in Excel and created a vast and complex forecast of hardware needs based on many parameters. All are presented on one sheet without the need for complex report creation. Thank you!
Subscribing to this channel is definitely a great investment, and free too! Very nicely explained. I have been using Excel for 25 years, but never used Data Tables.
I never used this function but now we have a clue. Miss Leila thanks a lot for your excellent video. And thanks to the video editor for that remarkable job
LEILA! I use data tables every single day is it is a part of one of key excel files. This excel file is very large, and running the data tables can take a couple minutes, so I always click "automatic except for data tables". HOWEVER, every time I paste-as-values (alt-e-s-v), there's a ~30% chance that the data tables will calculate, wasting 2 precious minutes of time, EVEN IF I have them set to automatic except for data tables! AHHRGH! Please bring this up with your windows contacts when you have the chance. Thank you, great video as always!
Thank you for explaining it wonderfully. Data table was not working so I had to go to options > formula and turn of the Automatic from either manual or automatic except from tables options
Great video Miss Leila, I will certainly use it in my work, my bosses will love this analysis... I work for a Petroleum Company and on a weekly basis we need to make this analysis... thanks a lot.
Great video… very well explained! Out of every command on the ribbon this is the one that I don’t really understand the use case for, seems like it can be achieved with absolute & mixed referencing.
Hi David - I see the main benefit of this to be for complex calculations where you have multiple inputs and steps to get to the end result. Then it wouldn't be so easy to replicate with formulas.
@@LeilaGharani and more important, the use of data table for monte carlos simulations (it's a nice manner to force excel to repeatedly calculate the rand fonction)
Really it helped me understand, whether i should go for rent or buy an apartment. I found a big difference based on this calculation. Many thanks dear 🥰
Great tutorial! This is a ‘classic’ Excel feature from the 1980’s and ‘borrowed’ from Lotus 1-2-3. Before pivot tables, power query and power BI. I find the implementation in LibreOffice easier to use.
I can't believe u make this video no longer after I request it! Idk did u make it because I request it or not, whatever it is, thank u so much! Now I can apply this feature
Thanks for the refresher on data tables. For very, very complex calculations, this may still be the way to go, but… Many of us are very fond of “single cell computations” where we perform rather complex calculations in a single cell (repeat for effect). And we can go a really far way courtesy of functions like: LET, LAMDA (and all its friends at the DA-office ;-). Then we can perform vectorised calculations where we lift (1D) of broadcast (2D) parameter sets (that by themselves could be created using the SEQUENCE function). I believe that approach to be more computationally efficient. I believe data tables to be Old School in this context - Dynamic Arrays can do the full scope of data tables, IMO.
The most interesting possibility offered by tables are not presented in the video. Data tables are the "only" possibility for creating "monte carlo" simulations in a sheet. It forces Excel to recalculate the random variables. For PV or PMT you don't really need tables ;-)
Haven't used Data Table before, very interesting video .. to make it clearer, perhaps a macro button can be created to refresh the data instead of pressing F9 ... the button will make clear that the calculation isn't automatic. 😉
You can use spilled formulae where the arguments are ranges to get the same effect. e.g. you could put interest rates in a row, and payment values in a column to the left, and in the formula, for rate reference the row of rates, for pmt reference the column of payments, and you get a spilled rectangle of fro representing the result for combinations of rate above and payment at left. (e.g. =FV(E2:K2/12,B4*12,D3:D23)) Now to find a way to do a 3-dimensional array. Maybe over multiple worksheets?. (Answer: No, "#REF" - but was worth a try...)
Thank you Leila, I think Data Table is the nr 1 feature in Excel that is a) mostly unknown, b) so very powerful. Maybe you also make a video of a Monte Carlo simulation? One thing that I found/find so weird is that you in that case you just have to refer to an empty cell ! If you like operations management you might use a waiting line model.
Hello Laila, thanks for the helpful videos. Can you make a video where we fill a excel data to a website. For example: Result of a course into a webpage automatically.
Grab the file I used in the video from here 👉 pages.xelplus.com/what-if-file
thanks i was quite worry for my project
Hi, when I FOLLOW YOUR EXACT STEPS all my values in the G Column return 73,335. Can someone please tell me what I am doing wrong if I am following her exact steps ?
Leila, there is a really neat way to use data tables, eg to run a set of scenarios with many variable parameters. To do this, 1. set up the scenarios, one per row, with the parameters in as many columns as you want alongside. 2. Create a scenario selection cell that will look up all the parameters for a given scenario, using the scenarios number as a row offset, ie 1 will pick the items in the first scenario row. In this way, you can select a whole scenario's worth of inputs by changing one cell. 3. Create a data table with the inputs down the left being the sequence 1,2,3,4.... for the number of scenarios. In the columns alongside, put the values you want to store for each scenario. Now run the data table and it will process all the scenarios and produce a set of results for each. So in this way, you can change many variables using a data table, which is extremely useful.
Very nice! Thanks for sharing your solution.
I would love to see this translated in a video demonstrations. Sounds VERY useful. Please Leila make it happen!
@@LeilaGharani hi
No idea how to execute the idea but that is exactly what I need.
Leila, it would be neat to have a video to demonstrate what’s being discussed here!
Great video!!
Alternatives: FV is also dynamic:
=FV(SEQUENCE(17,,0.03,0.005)/12,20*12,-200)
=FV(SEQUENCE(17,,0.03,0.005)/12,SEQUENCE(,7,4,4)*12,-200)
Or an universal approach, an user friendly custom-made lambda helper function, for any function fn(x,y)
WIDT(r,c,fn,[nm]) What If Data Table Lamda Helper Function. Arguments; r: rows vector(vert) , c: clms vector(horiz) , fn: lambda helper function , [nm]: results name (top left cell)
=LAMBDA(r, c, fn, [nm],
LET(
w, IF(ISOMITTED(r), 1, ROWS(r)),
l, IF(ISOMITTED(c), 1, COLUMNS(c)),
m, MAKEARRAY(w, l, LAMBDA(x, y, fn(INDEX(r, x), INDEX(c, y)))),
HSTACK(VSTACK(IF(ISOMITTED(nm), "Wht if", nm), r), VSTACK(c, m))
)
)
We can call:
=WIDT(SEQUENCE(17,,0.03,0.005),20,LAMBDA(x,y,FV(x/12,y*12,-200))) for first first scenario
=WIDT(SEQUENCE(17,,0.03,0.005),SEQUENCE(,7,4,4),LAMBDA(x,y,FV(x/12,y*12,-200))) for 2nd scenario
The function lists the results with rows/clm headers. ✌
Other scenario : r: vertical array, years array =SEQUENCE(10,,4,3) (10 years 4;7;10;...;31 )
c: horiz array , mnthy pymt =SEQUENCE(,10,200,30) (10 dif mnth pymnt 200,230,.....470)
Single cell function called:
=WIDT(SEQUENCE(10,,4,3),SEQUENCE(,10,200,30),LAMBDA(x,y,FV(0.04/12,x*12,-y)))
Leila, you are the best tutor I have ever listened too. All your presentations makes things look simple and flawless and they are very easy to understand. You are truly my MVP when it comes to Excel and Power BI
100% agree
Leila, you are the best teacher ever! I just currently found your channel, and already using some new features you showed me. THANK YOU! You are brilliant!
Great to have you here!
@@LeilaGharani you have an imposter fan
Thank you Leila! I had used Data Tables long back and your video was a much needed refresher to revise and start using them again where needed. Great! 🙂
It's a very overlooked yet sometimes quite helpful little feature. Glad you enjoyed the refresher!
This could have use cases for sure, but it certainly is not very intuitive. Thanks for sharing Leila.
Yes terribly unintuitive when choosing the row and column input cell(s) considering they exist in the base formula. Using the first value in the column or row in the table being created would make more sense to me, but I guess doing it the way they are doing it allows the purpose of the numbers in the row or columns in the destination table be defined as they relate to the base formula. If so, the dialog to choose the inputs could still be made more intuitive so that the relationship being made is more prominently displayed and evident.
Not intuitive at all. Thanks for watching, Matt!
Thank you, Leila, for this wonderful tutorial!
I am a professional UX/UI designer trying to switch careers and become a full-time data analyst or at least part-time.
I've been working with SQL for a long time since I am a CS graduate and I recently learned SSIS, and Power BI on my own.
I was actually having a little bit of a rough time with the concepts of data analysis and formulas, etc. so I decided to learn Excel. I stumbled upon Data Tables last week, I got stuck on it and I just couldn't understand why are data tables used and how to use them. It's been two weeks now, I tried searching around and watched numerous videos on RUclips but no one explained it better than you did.
Hi Leila, this is a great tutorial! I was hoping you could offer some guidance on a couple of questions. Q1: is there a way to dynamically change the size of the datatable? I'm thinking with Array Formulas or if the output can be dynamically changed to an Excel table. Q2: Is there a way to "consume" the data table that gets created? For example with PowerQuery, Python in Excel. Thanks!
I don't have a background in finances, but I manage my own budget in Excel. The other day I was wondering how I could make these kinds of projections. This is a great video! Easy to understand and informative. Thank you!
Thank you for adding some much-needed explanation and clarity to this element of Excel. A video is worth 1,000,000 (1,000²) words. The example helps demonstrate what's going on.
Too bad the real world doesn't work quite so easily. "Yes, I need my interest rate to be twice as much to achieve my goals." and BOOM! The interest rate is double. The most controllable piece of the equation is the monthly contribution. But that's a completely different conversation. 😀 Hmm, now that might be a slightly better example with a double lesson: the data table and the contribution amount.
You are great to explain things in a simple way. Thank you, Leila.
Glad you think so!
So nice to return to my standard early morning routine, 'Thursday's With Leila', i.e. hot coffee, biscotti and another insightful training video ... and I know exactly where I will employ this technique. Thank you ... thank you .... thank you.
Sooooooooo nice to have you back ... 😍😍😍😍
Glad you enjoyed our Thursday date :) Biscotti is a good choice!
I have been following your your videos for many years now.
I am convinced that we all use not more than 10% of what Excel offers. Great video as always.
Ms Leila, you're my favorite Excel Teacher.
Love you, from Malaysia
Wake up, watch Leila on RUclips and learn something new. great way to start my day. Thanks!
I Think I found my Best Teacher in Data Analysis.Thank You And I hope more tips.👍👍👍👍👍👍
watched so many data table videos, it kind of always seemed unintuitive. With this video, I kind of got a sense that I got it. Thanks!
Wow ! Couldn’t have asked for a better explanation. Kudos !
I truly learnt a good skill in this video. Easy to follow and grasp. Thanks Leila!
Every time when i have any query I have also found the most suitable solution from your channel. Good Job,
I owe you big time with your tutorials. Thanks a lot.
Yet again, your channel has made me more productive at work. I've been itching to make a scenario analysis table with a bunch of complex financial data with moving 1 or two variables... This did the trick! One other thing I'd add, is if you're just moving a columnar variable, you can have many different reference columns that all update with the far left column values! I also couldn't get it to work until I made the Column Input Cell on the same sheet as the What If table. Thanks!!!
A very big Thank you Leila. Your video Your contents and Your clear pronunciation though you are not a native English speaker. Really awesome!
love you, you make everything simple and easy to understand... you are the best excel trainer
Thank you very much. I used Access with mathematical formulas to create reports of hardware needs forecasting. Based on your explanation, I tried to do the same with "What If" in Excel and created a vast and complex forecast of hardware needs based on many parameters. All are presented on one sheet without the need for complex report creation. Thank you!
Nice work!
Mysterious but cool Excel feature. Thanks for sharing!
Love it, Leila! Another great, simple, and more importantly, useful, tutorial!
I almost cried reading the textbook. Thanks you 😊❤
Subscribing to this channel is definitely a great investment, and free too!
Very nicely explained. I have been using Excel for 25 years, but never used Data Tables.
I never used this function but now we have a clue. Miss Leila thanks a lot for your excellent video. And thanks to the video editor for that remarkable job
Your channel is indeed an investment, and so is this video.
Wow! You just Simplified a concept that intimidates the life out of me.
Thanks, Leila
You're a lifesaver! Thank you for the crystal clear teaching! 💎
You're so welcome!
Hello Leila. I had used this table way back in 2003 when I used to teach MS Office. Thanks anyways. Good day.
ABSOLUTELY AMAZING... SEEING THESE TRICKS IS LIKE A MAGIC FOR ME.
Thank you so much. A great explanation indeed.
Thanks, that's neat especially the rows and columns explanation.
I was struggled with it, thanks a lot. That's really healpful for beginners like me.
LEILA! I use data tables every single day is it is a part of one of key excel files. This excel file is very large, and running the data tables can take a couple minutes, so I always click "automatic except for data tables". HOWEVER, every time I paste-as-values (alt-e-s-v), there's a ~30% chance that the data tables will calculate, wasting 2 precious minutes of time, EVEN IF I have them set to automatic except for data tables! AHHRGH! Please bring this up with your windows contacts when you have the chance. Thank you, great video as always!
Lesson completed, thank you so much.
Thank you for explaining it wonderfully.
Data table was not working so I had to go to options > formula and turn of the Automatic from either manual or automatic except from tables options
You are a great teacher. Thank you.
A super model doing math, incredible 😊
Thank you Leila, I love your tutos as always they are very straightforward and easy to understand !
Fantastic video and examples. Thank you so much!
Glad you enjoyed it!
Simply awesome, I learned a lot from this short tutorial. Thank you!
Well explained, thanks !
Never tried this. Wow! Just Wow!!
Hope it will come in handy.
This was very helpful for me, so thanks Leila
Great video about data tables. helped me a lot to understand and apply.
Super helpful Leila. I don't use this often enough. Thanks!
Great video Miss Leila, I will certainly use it in my work, my bosses will love this analysis... I work for a Petroleum Company and on a weekly basis we need to make this analysis... thanks a lot.
Great & Simple Explanation
This was impressive. Thank you.
Wow! This is amazing.
Thank you, Leila
Great video… very well explained! Out of every command on the ribbon this is the one that I don’t really understand the use case for, seems like it can be achieved with absolute & mixed referencing.
Hi David - I see the main benefit of this to be for complex calculations where you have multiple inputs and steps to get to the end result. Then it wouldn't be so easy to replicate with formulas.
@@LeilaGharani and more important, the use of data table for monte carlos simulations (it's a nice manner to force excel to repeatedly calculate the rand fonction)
How about we celebrate getting 2M subs!!! Well deserved! 😎
Thank you!
Great demo. Can you also demonstrate how you would plot those in spider plot? What if you have three variables?
Galing, isa kang alamat Lodi.
I really appreciate to your video after seeing this I don't have any query related to this topic
Thank you. This is a very useful tool. I think you have explained this very well and it is not complicated at all.
It helped me for my college assignment 😅.
Thank you.
One of the most important feature in Excel
Thank you, explained nicely
Thx for the explanation, it was pretty useful 💕
Always a great learning experience when I watch your videos! Thanks 👍
So nice of you!
Thanks to your guidance and efforts to make every complicated tasks easier. Your content is incredibly helpful
Thanks from India 🇮🇳
I had NO idea this was possible. THANKS, LEILA!
Very nice video Leile. Thanks for your hard work 👍
I make sure, I watch one video of yours everyday. #greatlearning
Really it helped me understand, whether i should go for rent or buy an apartment. I found a big difference based on this calculation.
Many thanks dear 🥰
Glad it was helpful!
@@LeilaGharani I owe a coffer☕
thank you so much for this video, it helps me a lot.
perfectly explained. Thank you!
Glad you enjoyed it!
Great tutorial! This is a ‘classic’ Excel feature from the 1980’s and ‘borrowed’ from Lotus 1-2-3. Before pivot tables, power query and power BI.
I find the implementation in LibreOffice easier to use.
Great very useful information 👍 Thanks
Thanks Leila this is so clear im jumping in..
Thank you for reviving…🙏🏻
Thanks. Very Informative.
I can't believe u make this video no longer after I request it! Idk did u make it because I request it or not, whatever it is, thank u so much! Now I can apply this feature
Hope it is helpful :)
Very very intersting mam, thank you so much.
Thank you so much for sharing
Excellent explanation
Short sweet and informative
Very nice explanation.
This video is gold ✨💎✨ thank you for sharing it
Glad you enjoyed it!
Thanks for the refresher on data tables. For very, very complex calculations, this may still be the way to go, but…
Many of us are very fond of “single cell computations” where we perform rather complex calculations in a single cell (repeat for effect).
And we can go a really far way courtesy of functions like: LET, LAMDA (and all its friends at the DA-office ;-).
Then we can perform vectorised calculations where we lift (1D) of broadcast (2D) parameter sets (that by themselves could be created using the SEQUENCE function). I believe that approach to be more computationally efficient.
I believe data tables to be Old School in this context - Dynamic Arrays can do the full scope of data tables, IMO.
The most interesting possibility offered by tables are not presented in the video. Data tables are the "only" possibility for creating "monte carlo" simulations in a sheet. It forces Excel to recalculate the random variables. For PV or PMT you don't really need tables ;-)
@@COURSSTATSCHAMBERY Nope, Dynamic Arrays can do that just fine.
Thanks for creating such a amazing vedio on excel, your vedio is very helpuf for me
This is sooooo brilliant! Thank you a lot... 💡👍👍👍👍🙂
Haven't used Data Table before, very interesting video .. to make it clearer, perhaps a macro button can be created to refresh the data instead of pressing F9 ... the button will make clear that the calculation isn't automatic. 😉
Thank you Leila.
You can use spilled formulae where the arguments are ranges to get the same effect. e.g. you could put interest rates in a row, and payment values in a column to the left, and in the formula, for rate reference the row of rates, for pmt reference the column of payments, and you get a spilled rectangle of fro representing the result for combinations of rate above and payment at left. (e.g. =FV(E2:K2/12,B4*12,D3:D23)) Now to find a way to do a 3-dimensional array. Maybe over multiple worksheets?. (Answer: No, "#REF" - but was worth a try...)
Thank you Leila, I think Data Table is the nr 1 feature in Excel that is a) mostly unknown, b) so very powerful. Maybe you also make a video of a Monte Carlo simulation? One thing that I found/find so weird is that you in that case you just have to refer to an empty cell ! If you like operations management you might use a waiting line model.
Thank you so much for this video!
Excellent video!
Brilliant work
Hello Laila, thanks for the helpful videos. Can you make a video where we fill a excel data to a website. For example: Result of a course into a webpage automatically.
Thank you for being helpful
Beautifully done!