Grab the file I used in the video from here 👉 pages.xelplus.com/chart-dynamic-file
@anazarianАй бұрын
The chart range does not dynamically change in the downloaded file the way it looks like it does in your video. What's up with that!? This is what I am really looking for. Please help!
@LoChamp93 Жыл бұрын
I know I’m not might not gonna use this one, or some other tutorials, but each and every time Leila posts a new video, I HAVE TO watch it. She makes everything so intelligible and so pleasant to watch.
@Mike-In-O-Town Жыл бұрын
As someone who's done a bunch of analysis work over time, you never know what of this training will benefit you in the future. As such, my approach is similar to yours, I watch all these videos and absorb as much as possible with the understanding you can never know too much.
@staceyl.thienel1499 Жыл бұрын
Amen!!!!!!! I completely agree!!!
@mustafamalik4211 Жыл бұрын
Quick tip: @7:39 you can use a little trick to shows "1" for "TRUE" and "0" for "FALSE." Its done by adding two consecutive minus signs outside the parenthesis: =FILTER(TSales, --(TSales[Date]>=E2) * --(TSales[Date]=E2) and press F9, it will show 1's and 0's instead of True and False.
@largpack Жыл бұрын
what's the benefit?
@mustafamalik4211 Жыл бұрын
@@largpack Imagine that you have hundreds of cells to scan through, TRUE/FALSE take up 4 characters each, whereas 0/1 are single characters. The only benefit is ease of viewing.
@felixgar1000 Жыл бұрын
Thank you for your new trick
@komplitrandom9 ай бұрын
Bro, how do I create that drop-down list with departmental names the way she has done it. Each department selected has its own sets of data. Kindly help.
@MBRTBT20238 ай бұрын
of course that would be a man trying to "better explain" or to add something...you guys are so tiring. When it's a man saying shit you are there to give them compliments for nothing. Afffff
@extraktAI13 күн бұрын
I swear excel is such a pain and then I watch your videos and you make it loos soooooo easy. Thank you! 🙏
@betoalema Жыл бұрын
Thanks Leila! You have an incredible talent for explaining things, it only really shows how good you are at it that you can an explain it in such a simple way
@LeilaGharani Жыл бұрын
Thank you so much!
@toyclyde Жыл бұрын
Awesome tip! So simple. I found out that this also works very well with Google Sheets. The modified formula I used was =filter(B2:C, A2:A=F1). F1 is the cell with the dropdown.
@LeilaGharani Жыл бұрын
Good stuff!
@barttitulaerexcelbart9400 Жыл бұрын
Thank Leila, nice to see the new FILTER function in action. But do not forget the old slicers in a table: They have the same effect and are a lot easier. To harvast the selected value of the slicer and put this in a dynamic title is however another story, that is not so easy...
@LeilaGharani Жыл бұрын
Hi Bart. True. Table slicers are great too - specially if we can directly use the table results in the chart and don't need to do any extra calculations.
@mattschoular8844 Жыл бұрын
Thanks, Leila.. These are fantastic features and will be put to use in our workflows. The only disadvantage is that users need to be mindful when sharing files with Non MS365 users. Great video...
@LeilaGharani Жыл бұрын
Glad it was helpful!
@LuisCarlosChavez717 Жыл бұрын
I recently attempted to use a new functionality and encountered some difficulties. However, after watching your video, I now have a better understanding of how to use it. Your videos are always enjoyable and helpful in making our lives easier. Thank you for sharing them with us!
@LeilaGharani Жыл бұрын
That's great to hear, Luis!
@PurplePegasus2531 Жыл бұрын
I swear you're getting me promotions at work and helping me get my projects so much faster!
@Ninjutsu2K Жыл бұрын
Your videos are always astonished helpful, and your standards for understanding and explanation it's the most impressive I saw in my thousands of times on KZbin, I follow many creator contents in many areas of tech, but your videos it's always highly efficient and boosts my productivity a lot, thanx for all your help.
@LeilaGharani Жыл бұрын
Wow, thank you! Very happy to hear that Thiago.
@grad007me Жыл бұрын
So helpful!!!! I could watch (and I do) Leila's videos for hours on end. I tried this with column chart that has multiple series, ie. still a single spill area but more than 2 columns, but it does not behave dynamically as it does with only a single series. Please show us how to do this with multiple series! I also need microsoft to get on board with error bars in excel - imagine if we could apply this concept to error bars as well, my life would be complete (specifically for instances with multiple series)
@PardeepLongia Жыл бұрын
I'm a noob with Power Query. I was trying to do something particular to data-filtering and dynamic data rep. I opened up my browser put the search terms in. It's difficult to describe how perfectly your video answered what I was looking to do. Thank you! This was such a great presentation of the FILTER function and its use.
@LeilaGharani Жыл бұрын
Glad it was helpful!
@sco0tpaАй бұрын
Excellent video. Exactly what I needed for a project I am working on. Thank you.
@kvlpnd Жыл бұрын
It is a great video as always. It would be great if you could include the same result with the name manager trick for non 365 users.
@briankkrieger1685 Жыл бұрын
Your videos are absolutely amazing. Straight and to the point, but detailed enough not to leave less knowledgeable people behind. I'm addicted to all these new (to me) capabilities in excel. Thank you!!!
@winter97989 ай бұрын
This is very practical as it makes it easy to send to new users and requires little to no maintenance. Long and short - will use this 100%
@stevegrey9829 Жыл бұрын
The TRUE/FALSE logic behind the * in the FILTER function seems to be consistent with the SUMPRODUCT formula logic. Anyway, this is a nice improvement on creating dynamic name ranges using OFFSET. Thanks, Leila!
@LeilaGharani Жыл бұрын
Hi Steve. Yes. It's similar to SUMPRODUCT.
@irmanorminton8157 Жыл бұрын
Sometimes, I just watch for fun, but I learn so much every time. When I then have to do something, I recall that I saw a video somewhere that can do the thing! You are my go-to expert!
@LeilaGharani Жыл бұрын
Great to hear Irma!
@greymanpl Жыл бұрын
Nice tips & tricks for charts but the real mind-blowing trick was with F9 and debugging! A game changer! Thank you! I used the "evaluation" tool but this is much better!
@IronBucks5 ай бұрын
Followed your advice and everything works as it should, the Filter function returns the correct values, however, my chart doesn’t update regardless of what I do. Thanks for all your advice, the video tutorials are easy to follow.
@ricardoromo96 Жыл бұрын
Sometimes for simple reports I used pivot tables/charts but I hate adding instructions for people to refresh them in case data is refreshed. But now with this dynamic charts it will be easier :') Love your videos!
@johnandnaanaowusu8028 Жыл бұрын
Great video Leila! 👏With the interactive chart, I am running in to this problem: the chart area is only grabbing the number of rows and columns for the first department I had selected when I inserted the chart. Example, if finance was active with 4 rows when I insert the chart, when I select another department say marketing with 7 rows, I am only getting 4 rows still for the chart area. How can I fix this without having to manually adjust the chart area each time? Thanks
@grad007me Жыл бұрын
me too! I have a dynamic array, and I have 365, but it isn't auto adjusting the array size like it is in her video. just keeps whatever the size was when I first made the chart from the array
@ynlee354 Жыл бұрын
I have the same issue here as well
@ericmoz17 ай бұрын
Appreciate the video! I'm glad to know I can make a chart off dynamic ranges without the fixed range to worry about. One thing I kept hearing you say that I'm not sure is true is that you have to make you array source a table.. you can filter out blanks easily with your FILTER function by just declaring a column that should never be null is not = "" For some reason I am such an anti-table person. I feel like it restricts me and I cannot stand the cell references when making formulas. It makes me feel I'm not in control or can validate what I'm doing. Although, I cant make a great defense against NOT using tables, maybe I'm just spiteful that excel still cannot match Sheet's dynamic capabilities. This is a good step forward... especially now I'm forced to use MS environment at my new job.
@lukasselgert9287 Жыл бұрын
Hey Leila, can you show us how to combine both filters you have created in this tutorial? For example to filter on deparment and the date at the same time in the dynamic charts?
@wendylefty Жыл бұрын
You would use the format of the second example and multiply the department condition in the parentheses.
@djstone5518 Жыл бұрын
Love the dynamic array functions including FILTER, SORT, UNIQUE, etc. and though I've read contradictory info regarding whether XLOOKUP and XMATCH are technically part of that family I use them often as well. Thank you Microsoft and thank you Leila for sharing your knowledge. Love your videos and I refer people regularly. Very helpful using OFFSET with FILTER to filter a dynamic range e.g. =FILTER(OFFSET(V6,0,0,ROWS(Table1_DATA),1),OFFSET(AB6,0,0,ROWS(Table1_DATA),1)>0) where I'm pulling 44 items of 90 that meet the criteria specified in the 'include' portion of the FILTER parameter.
@labmeeting7 ай бұрын
Absolutely incredible Leila, exactly what I've been looking for! I imagine I can use slicers to have users dynamically filter the table as well. One HUGE step forward for my dashboard!
@sm40155 ай бұрын
Thank you sooo much Leila, this is what I was searching for the whole month.😀
@Ticky66MN Жыл бұрын
That is a beautiful thing! I just love the new filter function. Thank you Leila.
@LeilaGharani Жыл бұрын
Glad you like it!
@surfer3662 Жыл бұрын
Sending ❤️ to Leila. Thanks for your amazing tutorials. Wishing you a lot of more subscribers for 2023. Happy New Year 🎉🎉
@realityman5619 Жыл бұрын
I’m in an training course currently; I wish you were the instructor 😩. I’ve learned more from you.
@kylelane630 Жыл бұрын
I’m going to start using this from tomorrow!!! I update a list of audit findings weekly and this, in conjunction with a week number selection tool, to give users the info they want. Thank you so much!
@12boxes Жыл бұрын
I'm keeping a record of our energy consumption. This approach will enable me to select a range on the fly. Looking forward to experimenting.
@E-ToolBox3 ай бұрын
good to know, very helpful will make amazing dashboards, good presentation
@yulinliu850 Жыл бұрын
Thanks Leila! I'm now encouraging my students to use the dynamic array functions and they love it.
@LeilaGharani Жыл бұрын
Wonderful Yulin!
@Cody-iy4cb Жыл бұрын
Leila, I greatly appreciate all that you do! I need your help though please. I have a table that has Failure ID type down the left in column A, and years (2016-2023) across the top (column B:I) for counting the number of Failures by year. I think this is the perfect example for the Dashboard Trick, however I cannot figure out how to get this to work as shown. I need to be able to filter by multiple Failure types in column A and a range of years selected by drop downs and have it update in a dynamic table. Please help!
@yogeshsali Жыл бұрын
It's an entirely new thing for me in the chart. Thank you Leila for this fantastic video.
@LeilaGharani Жыл бұрын
You're so welcome!
@NancyPickering Жыл бұрын
This doesn't yet seem to be available on the Current Channel for Microsoft 365 (Version 2212). Hope people on the Preview and Beta Channels are getting to experience it!
@LeilaGharani Жыл бұрын
Did you try manually checking for updates?
@CarlosAguilar-xw7ot Жыл бұрын
Thank you TEACHER, GREAT thinking, illustration and knowledge sharing. Greetings from México 🇲🇽
@borstenpinsel Жыл бұрын
Can't wait for this feature to arrive. I tried it just now and while the graph "sees" the full range due to the #, it doesn't update the range when the #-range gets smaller or bigger.
@vivekjain2488 Жыл бұрын
Hi, thanks for the lovely tutorial. However, while using the same in my excel I observed that the chart data source is not dynamic and is static. How can we make it dynamic?
@kumaranshuaki Жыл бұрын
Big Fan of yours. I see your videos on regular basis. Can you please create inventory dashboard analysis using Excel/Power BI/Pivot table. It will be very helpful and insightful if you explain considering the following things:- 1. Ageing analysis report 2. ABC class of inventory 3. ROP, Safety Stock and Max, Min stock and reorder quantity against each SKUs 4. Revenue wise analysis against each SKUs 5. You can also include add on points as per your knowledge
@rnunez047 Жыл бұрын
¡Excelente Explicación!...I will use it in a project expenses table. I gotta filter "supplier" -"sponsor" - "materials" - "equipment" and "workforce".... ¡Gracias por tu explicación!. Me gusta como lo haces simplificando recursos y bien explicado.
@rjmorpheus Жыл бұрын
This has been a godsend. I had a similar problem to solve like the dynamic dates thing and the work around gave me a headache. This is so much more easier!!
@LeilaGharani Жыл бұрын
Excellent!
@behnamferdowsi4 ай бұрын
Thank you very much for this fantastic video.
@vikaschauhan7574 Жыл бұрын
Hi Leila , Kindly do markup & margin on excel sheet video.
@tifouraamina1761 Жыл бұрын
😃 didn't know about multiple function, I always learn something new with you Leila. You're my best 😎😍🤗 thank you
@rajeevgosavi4514 Жыл бұрын
Leila, as usual you are truly mesmerizing, we never realize when your tutorial ends, should just go on and on.
@LeilaGharani Жыл бұрын
Thank you so much!
@rychei5393 Жыл бұрын
Dayum, you're so smart. Thank you for sharing that golden mind of yours! I totally see my self coming back to this one to track my sales better than the auto programs try to force on me. They limit ranges and some of my views. This is really great!
@bpkingcobra Жыл бұрын
This is PERFECT. I actually have a use for this with my dashboard, thanks for the tip!
@bevonclarke Жыл бұрын
I am going to use it everytime I have to make a chart! Best of the best.
@grahamesson8495 Жыл бұрын
I use the filter function when I am playing FM23 pc game I export stats for my squad then create charts for training players and monitor staff wages and movements in and out. It makes it so much easier and I have great fun creating in Excel while playing games for hours. I am passed retirement age but feel I am keeping my hand in and brain active. Great videos, I love trying your tips. Although I suspect they are not aimed at my demographic profile😂😂😂 thank you from a 70+ grey haired Scotsman.
@zeepeesih1321 Жыл бұрын
Hi ma'am So many thanks to you. Please make a video on 'How to draw line graph in Excel from the dynamically changing single cell data '. I'll be so thankful 🙏🙏🙏🙏
@rn_ain Жыл бұрын
I love how you explain thing. Everytime i have problem with excel your channel is the first thing i will go to for reference. 🌸 Anyway do you have suggestion on how to solve my problem: Table A containing data to be verify. Column A food (ex: candy) Column B food category (ex: confection). However, column B might incorrectly filled. Ex: Column A (candy) Column B (vegetable). How can i check it automatically without have to go through 300,000 data and checking it one by one.
@olliehopnoodle4628 Жыл бұрын
This is so cool. I have fought and fought with a chart where name manager loses the 'offset' formula and reverts back to the entire selection. I just tried this using 'take' and a small table and it works like a charm. Thanks for posting how to do it. I was trying to figure it out on my own and, of course, I was over complicating it. THANK YOU
@LeilaGharani Жыл бұрын
Glad it was helpful!
@olliehopnoodle4628 Жыл бұрын
@@LeilaGharani Thanks for reply! Is this feature in a limited release at this time? I am subscribed to Office 365 and use a lot of the features (lambda, LET, etc) But when I tried this dynamic charts it didn't work. Acted just like the old charts. I even duplicated your example and no luck. Thanks!
@Duncan_kadesdal Жыл бұрын
Hi Leila, my name is Duncan and I really like the high quality of your tutorials. I am planning to start my KZbin Chanel soon, can you please tell me the software you use for your tutorials.
@LeilaGharani Жыл бұрын
You can check out my gear here: www.xelplus.com/resources/#gear
@Duncan_kadesdal Жыл бұрын
Thank you so much Leila
@udani301Ай бұрын
This video is really helpful! I was trying something similar but a little bit expanded version. I have the date column and some more columns for different tasks to repot their volumes. Ex: task 1, task 2, task 3,,,. I want to create dynamic charts for each task type with month in the x axis , volumes in y axis and years as legends. Could you please advise how to do that?
@darrengodkin Жыл бұрын
As always, brilliant 👏, very intuitive video
@deltawing9 Жыл бұрын
Thank you so much! Needed this so many times and did so many boring and bulky workarounds with tons of possible mistakes. This improvement is awesome, thank you for showing it. It doesn't work for me though, looking forward to getting it.
@LeilaGharani Жыл бұрын
Yep. I'm glad those workarounds aren't need anymore. It'll probably take some time to get to you depending on which update frequency you're on.
@paulchiporombe54135 ай бұрын
I just knew Leila had my solution 😁😁. Thank you Leila.
@LeilaGharani5 ай бұрын
You're welcome 😊
@Picla_Peremohy Жыл бұрын
I am so wanting this feature. We have an Enterprise Office 365 license and am on Version 2202 (Build 14931.20858) and it doesn't appear to have that functionality yet, unless I am missing something. We are on a Semi-Annual Enterprise Channel so it looks like I am just going to have to keep this in my hip pocket for now. Great video, Leila, as always. Your Power BI and DAX courses are over the top! Thank you.
@LeilaGharani Жыл бұрын
Glad you like it, Jim. Yes, you may have to wait for the next semi-annual update round.
@jajwarehouse1 Жыл бұрын
Definitely something that has been needed for years.
@ytHobie4474 Жыл бұрын
How have I missed F9 for debugging formulas? That's a juicy tidbit I'll put right to work. As for the filter function, I like to use named ranges to name criteria like DateFrom and DateTo. This makes the formulas a bit more readable.
@yadelliart Жыл бұрын
Hi Leila. Your videos are an inspiration to me. Thanks for this free and amazing content you're producing! I would be thrilled to cooperate in the future!
@bgood8299 Жыл бұрын
Thank you and happy New Year! Your tips have been very useful at my job. Now when my coworkers bother me about doing something in Excel, I just direct them to your channel. Win-win!
@MrLexhoya Жыл бұрын
Oh wow, with the new year and new plans for this year this helps me soo much to visualise sales over region and product (your first example). Thank you for this very easy looking video
@mosesgitonga47 Жыл бұрын
Thanks. I had earlier watched your video on creating dynamic charts using the name manager (and had used that to create dynamic charts) but this is very simple compared to that one! As usual, very informative content..
@boukebos1294 Жыл бұрын
Great video, thanks! Especially helpful to learn how you can use multiple conditions in the FILTER function. I've tried the OR / AND functions, but they don't seem to work inside the FILTER function.
@LeilaGharani Жыл бұрын
Great to hear!
@Untaian_Kata Жыл бұрын
Very loud and clear explaination. Thank you Leila.
@douglasbrown3354 Жыл бұрын
Excellent video! You always do a wonderful job. I have learned so much from you and have always been impressed with your knowledge but also your effective style of teaching. Thank you again!
@GeertDelmulle Жыл бұрын
Yes, good for this to come to Excel. I. Use the DA for graphs technique all the time. And now (soon?) it will be much easier to create. Here’s (in return) a little challenge for you: now do the date range selector again, but this time using a timeline “slicer”. 😊
@LeilaGharani Жыл бұрын
That's a nice challenge Geert ☺️
@barttitulaerexcelbart9400 Жыл бұрын
Hi Geert, I use just a table for this, and a slicer, but you can't add a timeline slicer to a table, for a pivot you can. But you put the word "slicer" in quotes so you mean something else? To make a scroll bar (2x?) with the develloper tab?
@gizemgorur46278 ай бұрын
You are a true angel
@ruthngmj Жыл бұрын
I wish KZbin has a ❤ button.. as usual, I learn something new! thank you!
@ronanokennedy8592 Жыл бұрын
Great video. would love to have in desktop version for my use case evaluting bioprocess data from bioreactors
@philippedemecheleer2381 Жыл бұрын
Great video about dynamic charts and I learned the F9 key for formulas!
@favmusicsong11 ай бұрын
Thank you for solving a issue I had 😊
@LeilaGharani11 ай бұрын
Happy to help!
@eroteam2 Жыл бұрын
this formule of graph was incredible, thank you! I hope your channel continuous growing, because it's incredible!
@dancarter214 Жыл бұрын
Hello Leila. Been watching your tutorials for years and have built many dashboards from these lessons. Using Office365 but when attempting to recreate the same dynamic graph the chart range does not adjust when entering new date ranges. The filter command works just find but the chart stays stagnant. Any ideas what could be causing this issues? Thank you.
@vida1719 Жыл бұрын
This is a great feature - actually a replacement of pivot table to get a chart.
@t.wilson Жыл бұрын
Extreme game changer for my work! Those dang name ranges and COUNTA, OFFSET functions are no more!! Can't believe I'm this late to seeing this feature 🤦♂
@yasserfaisal4886 Жыл бұрын
you are so marvelous, I ask, How can I get your videos from the first one till the last? thanks alot
@mumtazazizi8945 Жыл бұрын
Brilliantly explained and easy to understand. Simple, concise, and to the point. Keep up the great work!!
@LeilaGharani Жыл бұрын
Much appreciated!
@HanySalem2 ай бұрын
It is great! However, I need to ask if I can use AND /OR/NOT in FILTER function in Excel?
@lennartl.4588 Жыл бұрын
Thank you. I´ve been searching for this the last two hours
@kelvin551415 ай бұрын
Thanks Leila, how would it work if value was a sum meaning it does not exist in the table so it would not return with the spill. For example you used a unique and filter function to produce the list and the value is calculated if a value is returned. In this scenario would do you create a dynamic chart range. Thank you
@harlansilva8935 Жыл бұрын
Thank you! This video help me to build some graphics that I needed
@dantemaese8316 Жыл бұрын
Nice tricks, thanks you so much !
@sunandsandtrip9423 Жыл бұрын
Thank you, Leila, for your great tutorial. It's helpful and amazing trick to create a dynamic chart. How about to create chart with 2 or 3 dynamics conditions such as date, with 1 or 2 other conditions? Would you please help me for this case?
@debashiss7410 Жыл бұрын
Awesome solution for the date range selection... always learning with Leila...Thanks 👍🏻
@Matt-rw9py Жыл бұрын
Awesome. Will use it for my spreadsheets. Thanks Leila
@shivamrai162 Жыл бұрын
I love excel. Also learning excel vba thanks to Leila 👍
@noragrosbach8429 Жыл бұрын
This update solves precisely the problem I am experiencing. Unfortunately, my employer does not have Microsoft 365. Could you give more information on how to accomplish this same result in excel 2016?
@vijayarjunwadkar Жыл бұрын
Happy New Year Leila! Great start of the year with a very useful tutorial! Thank you and looking forward for more such videos throughout this year and beyond! Stay blessed! 🙂
@ninonino8332 Жыл бұрын
Hi Leila, Thanks for sharing. How about dependent filters. On your example Categories-Finance and the dates are dependent to each other, how to go about it?
@usmaniqbal1836 Жыл бұрын
Perfect Leila, please share data file for practice..
@theresahaidvogl1438 Жыл бұрын
Thank you so much Leila for sharing the video! It will make my work life so much easier :)
@chrism9037 Жыл бұрын
This was fantastic, thanks Leila!
@LeilaGharani Жыл бұрын
Thanks for watching, Chris!
@haseebansari1966 Жыл бұрын
You Saved me، May Allah bless you
@BenoitLamarche Жыл бұрын
Can’t wait to watch this. Leila - have you experimented with the new Automate tab in Excel for the Web (and now desktop) ?
@LeilaGharani Жыл бұрын
Yes. I have a few videos on Office Scripts. I'm happy the recorder made it to desktop. I'll work on a video for that one :)
@MetalforLife202415 күн бұрын
Hi everyone. I am new to these Dynamic Excel functions. I would like to know whether the table in this video is considered a pivot table per se?