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 ?
Hi, Leila. Good day. When I repeat the process you just did on this content, I get the same values inside the box. I make sure that all the formulas in the income statement are intact and the data I feed on the data table comes from the output cells- the ones that are formulated. But still, the same figures appear Please help me understand
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
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!
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 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.
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 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! 🙂
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, 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.
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 ... 😍😍😍😍
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!
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!!!
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
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 ;-)
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 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
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)
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.
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 🥰
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
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!
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.
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...)
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, 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.
![Noob To Max With DRAGON REWORK In Blox Fruits [FULL MOVIE]](http://i.ytimg.com/vi/LnBMOinoOvA/mqdefault.jpg)
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 ?
Hi, Leila. Good day. When I repeat the process you just did on this content, I get the same values inside the box. I make sure that all the formulas in the income statement are intact and the data I feed on the data table comes from the output cells- the ones that are formulated. But still, the same figures appear
Please help me understand
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
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!
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 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.
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 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!
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
Ms Leila, you're my favorite Excel Teacher.
Love you, from Malaysia
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.
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!
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!
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.
Love it, Leila! Another great, simple, and more importantly, useful, tutorial!
Every time when i have any query I have also found the most suitable solution from your channel. Good Job,
Wow ! Couldn’t have asked for a better explanation. Kudos !
A super model doing math, incredible 😊
You are great to explain things in a simple way. Thank you, Leila.
Glad you think so!
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!
Wake up, watch Leila on RUclips and learn something new. great way to start my day. Thanks!
Hello Leila. I had used this table way back in 2003 when I used to teach MS Office. Thanks anyways. Good day.
Well explained, thanks !
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
I owe you big time with your tutorials. Thanks a lot.
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
Galing, isa kang alamat Lodi.
Thank you Leila, I love your tutos as always they are very straightforward and easy to understand !
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.
Leila,
I'm new to Excel. I found this video very useful.
Thank you.
Wow! You just Simplified a concept that intimidates the life out of me.
Thanks, Leila
I truly learnt a good skill in this video. Easy to follow and grasp. Thanks Leila!
Never tried this. Wow! Just Wow!!
Hope it will come in handy.
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 almost cried reading the textbook. Thanks you 😊❤
Very very intersting mam, thank you so much.
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 :)
Great & Simple Explanation
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)
I really appreciate to your video after seeing this I don't have any query related to this topic
goated video, there's no way i wouldve figured this out on my own
Excellent! Great explanation!
You're a lifesaver! Thank you for the crystal clear teaching! 💎
You're so welcome!
How about we celebrate getting 2M subs!!! Well deserved! 😎
Thank you!
Wow! This is amazing.
Thank you, Leila
This was very helpful for me, so thanks Leila
Thank you so much. A great explanation indeed.
Lesson completed, thank you so much.
You are a great teacher. Thank you.
Thanks, that's neat especially the rows and columns explanation.
Very nice video Leile. Thanks for your hard work 👍
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.
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☕
Leila I follow your channel always and get more information, learn a lot God bless you more!! Thank you for your class👋👋👋👋👋
I was struggled with it, thanks a lot. That's really healpful for beginners like me.
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
This is sooooo brilliant! Thank you a lot... 💡👍👍👍👍🙂
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!
Super helpful Leila. I don't use this often enough. Thanks!
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.
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...)
Mysterious but cool Excel feature. Thanks for sharing!
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. 😉
I make sure, I watch one video of yours everyday. #greatlearning
Thanks from India 🇮🇳
It helped me for my college assignment 😅.
Thank you.
Short sweet and informative
Great very useful information 👍 Thanks
Thanks Leila this is so clear im jumping in..
ABSOLUTELY AMAZING... SEEING THESE TRICKS IS LIKE A MAGIC FOR ME.
Your channel is indeed an investment, and so is this video.
As ALWAYS, you are awesome!!
Thank you for reviving…🙏🏻
Thanks to your guidance and efforts to make every complicated tasks easier. Your content is incredibly helpful
This was impressive. Thank you.
This video is gold ✨💎✨ thank you for sharing it
Glad you enjoyed it!
Great video, thank you!
I had NO idea this was possible. THANKS, LEILA!
Great video about data tables. helped me a lot to understand and apply.
Always a great learning experience when I watch your videos! Thanks 👍
So nice of you!
Excellent video!
Fantastic video and examples. Thank you so much!
Glad you enjoyed it!
Thank you. This is a very useful tool. I think you have explained this very well and it is not complicated at all.
Thanks for creating such a amazing vedio on excel, your vedio is very helpuf for me
Thx for the explanation, it was pretty useful 💕
Simply awesome, I learned a lot from this short tutorial. Thank you!
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.
Beautifully done!
Very nice explanation.
thank you so much for this video, it helps me a lot.
Thank you so much for this video!
just loved!
Thanks. Very Informative.
Great One Leila. thank you for this
Great video ! Thank you lala 👍
Excellent work 😍