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.
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.
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!
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 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! 🙂
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!
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!
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.
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)
Loved the call to changing calculation types and liked having the side-by-side if your entire data set is only a couple columns from the source but would probably still stick with PowerPivot beyond that and update my reference data to then be used to compare to source, but very good solution in this use case. Knew it was gonna be good and don't recall the last I used this so will try and remember when it's relevant to only a few parameters as it was very useful in this case.
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
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 RU-vid but no one explained it better than you did.
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.
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 ;-)
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!
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!!!
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...)
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!
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
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. 😉
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.
Good Day Ms. Leila. Can you make some videos where the returning value should be the closest value. I've seen a lot of xlookup examples but it just shows 1 criteria only. xlookup(0,Abs(range)-value,, 0) this works but i have 3 criteria as lookup value.
I appreciate your Xelplus tutorials, Leila. I'm currently working on designing a 13-month rolling cash flow statement. Any assistance you can provide on this matter would be highly valued. Thank you for your support and guidance.
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.
Am I right to say that the What-If Analysis seems to be equivalent to doing the formula but with absolute references? That's how I've always done done it for two-variable analyses.
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.
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 🥰
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
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.
Hello Leila, I am your big fan, but one video I am still missing, how to integrate monthly comments to reports in dynamic way based not on Power BI, but as a solution for Powerpivot, which is totally forgotten in my opinion on RU-vid 🤗thanks a lot!
