Effortlessly Create Dynamic Charts in Excel: New Feature Alert!

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-courses
Learn how to take advantage of dynamic chart ranges in Microsoft Excel. This way you can create a perfect chart even when the data range gets bigger or smaller based on criteria. No more white space or empty spaces in your chart.
Grab the file I used in the video from here 👉 pages.xelplus.com/chart-dynamic-file
The chart range also automatically expands if you have new data. You no longer need to use name manager or create advanced Excel functions together with name manager to have dynamic chart ranges. You have it now in-built as long as you use Dynamic array formulas or Excel functions that spill (or use the hash to spill a range). If you have this in place, and you create an Excel chart based on this range, the chart will automatically adjust as the range gets smaller or longer. This is a great time-saving feature specially when you create Excel Dashboards.
Note: Requires Microsoft 365
00:00 How to Remove Empty/White Space From Your Chart
00:39 Preparing the Filtered Data
02:52 Insert the Chart
04:26 Cool Dashboard Chart Trick
09:37 Wrap Up
🌍 My Online Courses ► www.xelplus.com/courses/
🎬 LINKS to related videos:
Excel What-If Analysis Data Table: kzbin.info/www/bejne/aofYgGSihJuIidE
How To Use the new Excel TEXTBEFORE & TEXTAFTER Functions: kzbin.info/www/bejne/pmqVhZRpf5qpfbc
➡️ Join this channel to get access to perks: kzbin.info/door/JtUOos_MwJa_Ewii-R3cJAjoin
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: lgharani
LinkedIn: www.linkedin.com/company/xelplus
This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/chart-dynamic-file

[@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!!!

[@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!!!

[@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!

[@PurplePegasus2531]

I swear you're getting me promotions at work and helping me get my projects so much faster!

[@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! 🙂

[@eroteam2]

this formule of graph was incredible, thank you! I hope your channel continuous growing, because it's incredible!

[@sm4015]

Thank you sooo much Leila, this is what I was searching for the whole month.😀

[@Untaian_Kata]

Very loud and clear explaination. Thank you Leila.

[@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!

[@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!

[@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

[@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!

[@Ticky66MN]

That is a beautiful thing! I just love the new filter function. Thank you Leila.

[@LeilaGharani]

Glad you like it!

[@debashiss7410]

Awesome solution for the date range selection... always learning with Leila...Thanks 👍🏻

[@essieesterhuizen7049]

Thanks Leila! This is EXACTLY what I needed!!

[@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!

[@behnamferdowsi]

Thank you very much for this fantastic video.

[@lennartl.4588]

Thank you. I´ve been searching for this the last two hours

[@labmeeting]

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!

[@surfer3662]

Sending ❤️ to Leila. Thanks for your amazing tutorials. Wishing you a lot of more subscribers for 2023. Happy New Year 🎉🎉

[@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

[@komplitrandom]

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.

[@MBRTBT2023]

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

[@bpkingcobra]

This is PERFECT. I actually have a use for this with my dashboard, thanks for the tip!

[@moonglumclampflower8803]

This has saved so much work. Many thanks.

[@Matt-rw9py]

Awesome. Will use it for my spreadsheets. Thanks Leila

[@tifouraamina1761]

😃 didn't know about multiple function, I always learn something new with you Leila. You're my best 😎😍🤗 thank you

[@moisestatis5418]

Another great video, as usual, thank you Leila!

[@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..

[@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.

[@philippedemecheleer2381]

Great video about dynamic charts and I learned the F9 key for formulas!

[@CarlosAguilar-xw7ot]

Thank you TEACHER, GREAT thinking, illustration and knowledge sharing. Greetings from México 🇲🇽

[@darrengodkin]

As always, brilliant 👏, very intuitive video

[@dantemaese8316]

Nice tricks, thanks you so much !

[@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!

[@winter9798]

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%

[@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!

[@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.

[@MiRofek]

Excellent tutorial. As always.

[@felixgar1000]

I love this class !

[@ronaldoliver1700]

Thank you! Great information!

[@bevonclarke]

I am going to use it everytime I have to make a chart! Best of the best.

[@Reddysangam]

Thank a lot for this

[@vikastiwari7134]

Very helpful, learned alot from your videos, thankyou so much

[@harlansilva8935]

Thank you! This video help me to build some graphics that I needed

[@theresahaidvogl1438]

Thank you so much Leila for sharing the video! It will make my work life so much easier :)

[@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!

[@E-ToolBox]

good to know, very helpful will make amazing dashboards, good presentation

[@sumitbhokare]

Class and simple as always ❤

[@hemvaidya]

so well explained

[@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.

[@wayneedmondson1065]

Awesome! Thanks Leila!!

[@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!

[@chrism9037]

This was fantastic, thanks Leila!

[@LeilaGharani]

Thanks for watching, Chris!

[@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!

[@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!

[@mejabelajar]

learned alot from your videos, thankyou so much

[@umarchitapure]

Really helpful

[@robertoalvarez3533]

Fantastic, you made my life a lot easier!

[@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!

[@mumtazazizi8945]

Brilliantly explained and easy to understand. Simple, concise, and to the point. Keep up the great work!!

[@LeilaGharani]

Much appreciated!

[@ramachandranck3719]

Amazing thank you

[@eduardopassos9928]

Great tips and videos! I’m always sharing your videos with my team!

[@LeilaGharani]

Awesome! Thank you!

[@unnikrishnansanthosh]

superb, thank you for sharing

[@dieterkonopka9523]

Sehr gutes Video 👍

[@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!

[@gustavopineda7933]

Wow!! Amazing tips

[@jajwarehouse1]

Definitely something that has been needed for years.

[@realityman5619]

I’m in an training course currently; I wish you were the instructor 😩. I’ve learned more from you.

[@kanhaiyashaha2182]

You are great….best presentation skills 👌

[@yulinliu850]

Thanks Leila! I'm now encouraging my students to use the dynamic array functions and they love it.

[@LeilaGharani]

Wonderful Yulin!

[@kibetkirui1054]

Great work Leila.

[@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!

[@x9rtu3os1t8]

Great great teacher. Thanks a lot.

[@danielluis7211]

Awesome!!! Well explained. Thanks Leila Gharani.

[@LeilaGharani]

Glad you liked it, Daniel!

[@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)

[@magdahassib694]

You always present something new. Thanks 👍

[@LeilaGharani]

My pleasure 😊

[@bijumonmolilkunjukunju4968]

Truly a great tip

[@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.

[@yogeshsali]

It's an entirely new thing for me in the chart. Thank you Leila for this fantastic video.

[@LeilaGharani]

You're so welcome!

[@pedrohenriquecarneiro904]

Amazing!

[@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.

[@DebayanKar7]

Excellent !!!!

[@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!

[@AnandMishra-ie9uv]

Great as usual

[@gizemgorur4627]

You are a true angel

[@brunovanherpe8423]

Thanks

[@favmusicsong]

Thank you for solving a issue I had 😊

[@LeilaGharani]

Happy to help!

[@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.

[@preeteshramdas3651]

This is brilliant 😃

[@edhazell7131]

This is great news!

[@ExcelTutorials1]

THATS AWESOME!!!!!

[@Fxingenieria]

Espectacular !!!

[@temyraverdana6421]

Leila, a gread lesson. Thanks

[@Jakeu1701]

love this.

[@suzysasse]

Thanks!

[@LeilaGharani]

Thank you, Suzy!

[@samvarona9019]

Helpful!!! ❤️

[@ruthngmj]

I wish KZbin has a ❤ button.. as usual, I learn something new! thank you!

[@paulchiporombe5413]

I just knew Leila had my solution 😁😁. Thank you Leila.

[@LeilaGharani]

You're welcome 😊

[@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?

[@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.