How to Create an Excel Interactive Chart with Dynamic Arrays

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Discover how to dynamically hide or show data series in Excel charts based on cell values. Learn to sort series automatically for impactful presentations!
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/interactive...
Quickly create an automatically sorted Excel bar chart that ALSO lets you hide and show categories on the chart based on a flag in the cell. It's super easy with Excel dynamic arrays - specifically with the new Excel FILTER and SORTBY functions.
📌 What You'll Learn:
▪️ Dynamic Series Visibility: Effortlessly hide/show chart series based on specific cell values, avoiding manual filtering or row hiding.
▪️ Automatic Sorting: See how to sort chart series automatically, enhancing your data visualization.
▪️ Using Excel’s Dynamic Array Functions: Leverage new functions in Excel for Office 365 to create responsive and interactive charts.
This is especially useful for Excel dashboards to include interactive charts depending on a selection.
The solution will be fully dynamic, i.e. we will not use manual filter or manual hiding. We will even take it another step further and automatically sort the Excel chart based on the visible sales data.
To summarize what you learn:
1. How to use Excel Dynamic Array referencing in charts
2. How to use Excel FILTER & SORTBY functions
3. How to Sort categories based on their values
Step 1: Data Preparation Table
Whenever the chart has a different setup than the source data, a data preparation table is needed in between. We will use the new Excel FILTER function for that. In order to get the chart properly sorted we will apply the new Excel SORTBY function. This function is used when the sorting criteria is not in the result. The tricky part is to ensure that the array for the sorting (by_array1) has the same size and order as the array for the visible sales data. We will solve this by nesting another FILTER function in the formula. To get the respective sales data we'll use a simple VLOOKUP.
Step 2: Excel Chart
For the visualization we'll use a column chart that's connected to the data preparation table. However, we want the Excel chart area to update automatically based on the number of data points in the data preparation table. In other words, we want the chart to be dynamic and to include any new selections from the data preparation table and exclude any blank areas.
Excel Chart references can either take direct references or they can take names. Therefore, to keep the chart dynamic we'll use names in the Name Manager and we'll use the "#" for the reference to spill. Then we'll update the Series Values and the Axis Labels of the chart with the names we created.
Step 3: Dynamic Dashboard
This technique can easily be used to create dynamic dashboards. Based on a dropdown selection the chart is automatically updated. I used the Excel IF and SUMPRODUCT functions to help with that. In order to make sure that new data is added to the chart I converted the data into an official Excel table.
00:00 Intro
01:26 Prepare Data Table
06:30 Create Excel Chart
09:38 Setup Dynamic Excel Dashboard
LINKS to Excel Dynamic Arrays Playlist: • Excel for Office 365 &...
Excel Dashboards & Reports Playlist: • Excel Dashboards & Rep...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ 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: / xelplus
Note: 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/interactive-chart-file

[@chh8860]

My Thursday morning routine … a cup of coffee w/ Leila … she is always interesting … she never disappoints.

[@LeilaGharani]

I like that routine :) Glad you find the videos interesting.

[@OnePercentPerfectionist]

Never ever

[@mozgus79ify]

Leila, you're amazing. Everyone who is afraid of Excel should watch your channel. Excel with you is fun!

[@johannesmp3]

Totally agree. Not only fun, but so user friendly

[@duncanwil]

I didn't know this, obviously and had looked at other Excel trainers and practitioners for help. This is the only one to demonstrated exactly this ... which is what I needed! Excellent!

[@catbou2885]

Fantastic. You remain my favorite educator on the internet.

[@arielspalter7425]

You're a life saver. This video has pointed straight to what I needed.

[@taimoor72]

I can't believe what I learned today. Leila you are just amazing. Thank you for this dynamic lesson.

[@LeilaGharani]

You're so welcome!

[@nixoniraj]

This is a super informative KZbin channel for excel users and finance professionals. I recommend others to subscribe the channel. I have learned a lot from her videos. Thanks Ms. Leila :) Much appreciation.

[@Antagraber]

I felt confident enough with Excel until I saw one of your videos, and I started to realize about the high level of this YT channel. I really appreciate not just this video (which is going to help me a lot), but also I am going to subscribe and thoroughly study all of the others, as well as the offering you are promoting. I found a huge source of knowledge and information on Excel that will help me a lot to know better this amazing product. Thank you so much, Leila !! It has been a really enriching experience.

[@LeilaGharani]

Thanks for the sub!

[@aviewfromthesaddle4160]

Genuinely the first time I've learnt something in Excel for a very long time, thanks. I've recently upgraded to 365 and will definitely make use of this.

[@LeilaGharani]

Glad you found something new.

[@aviewfromthesaddle4160]

@@LeilaGharani Looks like I'll need to reach out to our IT department to get access to these functions. I have an Office 365 Pro subscription, but it looks like I need to join 'office insiders' to get access to these new functions for now, as per this link: techcommunity.microsoft.com/t5/excel/excel-sort-and-filter-functions-are-missing/m-p/533484 Just sharing this here as it may explain to others why they can't yet access these functions :)

[@alexkim7270]

Thank you so much for making it easy to understand, Leila. I used to FEAR charting in Excel (that's why I went to Power BI). Your video has made everything really posh and easy to digest.

[@LeilaGharani]

I'm glad to hear that Alex. Nothing to fear about charts in Excel :)

[@brighnquisitive6217]

Awesone as always... How can one person be so consistently amazing?

[@mattschoular8844]

Excellent....Dynamic Arrays are here to stay... Thanks Leila

[@LeilaGharani]

So true. It's a game changer in Excel.

[@sasavienne]

Just *amazing as usual* Thanks Leila. You make everything easy to understand and easy to keep in mind. 🌟 🌟 🌟 🌟 🌟

[@Michael_Alaska]

Thanks for the great walk-through. Playing around I created four 4 prep ranges so I could use one each for all, red, green, and yellow; then using an earlier lesson on overlapping chart data series made the chart bars change color to match the group. Good fun.

[@alexeyagaev3852]

Leila, you’re terrific! Thank you very much for your content! It saves a lot of time while using excel and helps to create wonderful excel dashboards!

[@LeilaGharani]

My pleasure Alexey. Glad you like the video.

[@ahmadgarbaauwal6381]

I really appreciate your efforts, thank you so much and congratulations!

[@arumoydas7706]

Just Stupendous working with excel, looks so easy , thanks to the nicely explained tutorial by Ms Leila.

[@bahatidramou4818]

You are amazing @Leila!! I have been following your channel for awhile now. THANK YOU,

[@johnborg5419]

Thanks Leila. Filter part was Great!!!

[@akademipowerpointmalaysia9216]

Finding the lesson helpful! Thanks a lot!!

[@neilmurdoch9789]

You are the best. Very useful.

[@ryanmiguelsantiago]

Thank you, Leila! Very helpful and useful as always

[@trex-1393]

This video made my workbooks x10 more efficient than before.

[@LeilaGharani]

That's great to hear!

[@DougHExcel]

Thanks for the video, can't wait for dynamic arrays to become more widely available!

[@81039178]

This is Pivot tables for people that want to go waaayyy further than just Pivot Tables with segments.... I love it

[@zeljkocrljenica7135]

Excel-ent, thank you for this tutorial

[@serigamel]

again, exactly what i needed. thanks Leila!

[@daseaton]

Excellent and practical lesson as always.

[@jake2.037]

this is exactly what I need.... thanks Leila.

[@67duiker]

Great example and use of array-functions

[@nsanch0181]

Thank you Leila. Your videos and classes are so helpful.

[@LeilaGharani]

Glad you like them!

[@rupomExcel]

This is really awesome and clearly a great solution for Dashboard developers with only functions. Thanks Leila

[@LeilaGharani]

You're very welcome!

[@williamkiarie]

That SORTBY is awesome. Thanks for sharing.

[@LeilaGharani]

It sure is :)

[@rpb8186]

well, I used to watch your video previously and after watching all those amazing tricks, I decided to subscribe to your channel.

[@LeilaGharani]

Thanks for the sub!

[@user-nw4jt5qv1j]

you solve a problem that i was struggling with for weeks!!!

[@Luciano_mp]

Very good Leila. Thank you!👍👍👍

[@jimmyjammERz559]

Your vidz make me a better analyst Leila! Thank you!

[@VSP4591]

Excellent presentation. Thank you

[@Citobiopsias]

Uauau, Now I watch your videos every single day

[@vijaygusain119]

Hey leila...You are almost like family now so just wanted to say stay safe and keep spreading knowledge😇

[@danuarzani6128]

I don't know when i will use this formula But this is awwwwwwwwwssssoooommeeeee 😮😮😮🤯

[@renjithrajan8220]

Amazing, very useful, thanks

[@rgarlinyc]

Very clever! And beautifully explained, thank you LG!

[@LeilaGharani]

My pleasure RG :)

[@jingchunzhang1776]

Leila,thank you your video,Recently i watch it every day,Although I can't understand what you are saying, it is very helpful to me，Like your video, more like you

[@xenonexy]

This is very helpful!

[@17aig]

Dear Leila as always super excellent tutorial thx so much

[@ALPHERATZ3650]

Thanks for posting this.

[@antebubic-dracari]

I'm simple man! Whenever I see new excel video from Leila, I hit like ;)

[@johannesmp3]

So do I. So that YT knows what I like

[@ExcelExciting]

Dynamic Arrays...!!! they just made life so easy... thanks for the great tip :)

[@LeilaGharani]

That's so true!

[@HemanthKumar-lb4xt]

Excellent, the way of explanation is awesome 👌

[@sarmadali9493]

I Love your explanation.. Take care..

[@Craftman368]

These dynamic arrays are a game changer

[@LeilaGharani]

So true!

[@danielmathivathan7391]

Excellent explanation

[@sheryliously]

leila you just singlehandedly helped me with my assignment thank u so much ily

[@LeilaGharani]

Happy to help!

[@nikotra]

Amazing, thanks for sharing, as usual :)

[@msantosh1220]

Thankyou for this video, all logic's can be directly applied for office us.

[@LeilaGharani]

That's great! I'm glad it's helpful at work.

[@zaidkanM]

Thank you Leila, I was looking for dynamic chart and finally found it

[@LeilaGharani]

Glad it was helpful!

[@aftabmunshi4749]

Amazing work Ma'am as usual

[@dennisd5776]

Another problem solved. Great video and thank you for the demo.

[@LeilaGharani]

Glad to help Dennis :)

[@red2fourty650]

Amazing video Thanks!

[@your_shiin]

You always make my day. Thank you Leila

[@LeilaGharani]

Glad you like it :)

[@mussabeltayeb7793]

Thanks a lot for knowledge sharing

[@fahadpalladium2]

Amazing! Love it 😍

[@younessloukili4203]

Very interesting Thank you Leila

[@mohamedhassanen529]

you are amazing, please continue

[@rezamakvandi1366]

Leila you are the best thank you👍

[@shaileshkarne9862]

Thanks Leila, I was looking for something like the same you have explained here. Thanks a lot.🙏🙏

[@LeilaGharani]

You're welcome 😊

[@jorgearturomontesdiaz8049]

I love Leila.. is so Smart 😍

[@FredericLEGUEN-Excel]

I love the second part of your video with SUMPRODUCT 😉

[@sushantjoshi5865]

This was really amazing..

[@Crazyboyzz90]

Im addicted to excel, thanks to Leila

[@LeilaGharani]

😘

[@stephenbrincat7124]

Amazing technic, thanks Leila

[@monte4955]

I like this. Thanks Leila. You excel goddess you.

[@Be-Efficient]

If I could, I would click several times on Like button! Thanks, Leila for this awesome trick (once again !).

[@LeilaGharani]

I already appreciate the one like Olivier :)

[@joesatriani2189]

i need this, thank you so much

[@leksdic]

That is a very good answer to the pivot chart problem where the slicers mess up you charts. With this solution you can ommit the pivot chart. Thank you for this!

[@dgjanes917]

What's wrong with pivot charts? I was thinking the whole time "why not just use a pivot chart with slicers?"

[@leksdic]

@@dgjanes917 Don't know if other excel users face the same issue, but, i made a dashboard (cause of all this covid-19 extra sitting time) and when i use the slicers for my pivot charts the custome formating gets messed along with the series legend. Imagine you've put so much effort in making your raw data, creating your pivots, manually formating everything, you create a lustrous dashboard and BOOM, you click the slicers and everything gets messed up. Colors, legends, positions. The solution Leila gave circumvents the use of pivot charts altogether.

[@dgjanes917]

@@leksdic you probably need to check the "show items with no data..." And "Preserve cell formatting on updat" In the options. Also uncheck "autofit column widths...". That will probably fix most of your issues if not all

[@leksdic]

@@dgjanes917 Will try them. Thanks for the info. Edit: Did try them. Didn't work. Thanks for your time anyway.

[@tadawulreport9803]

Wooooooooow amazing You're the best as always Saying thanks not enough to Express my feelings about your work... Very big thanks 🌷🌷🌷🌷🌷🌷🌷🌷🌷🌷

[@LeilaGharani]

Glad you like it. Many thanks for the kind feedback.

[@eduardomunoz2764]

Genial, gracias Leila... Saludos

[@farhatali9062]

Thanks for sharing thid. Amazing addition to my skills

[@LeilaGharani]

My pleasure!

[@giovannispaziani5580]

Leila, you are simply the best! A question: how to change automatically the colour for the best value or the wrost one? Thank you

[@bondanswastika950]

Really Great... 👍🏻👍🏻

[@gerrythomas6297]

Thank you!

[@albanoteles4924]

Top!. Thanks for sharing.

[@darrylmorgan]

Hi Leila!Excellent Tutorial Using Dynamic Arrays,Also Nice To See VLOOKUP Make An Appearance...Thank You :)

[@muhammadnurhakimmohdderis4201]

I'm in love with Excell

[@tknijnenburg]

If you use for the SUMPRODUCT part a table as well, you can add apps to the largest group (in your case) Green. It will update the teams automatically.

[@adelba724]

you are always the best

[@shaheershujaat2223]

Amazing video well done 😊😊❤️❤️👍👍

[@balloonmanyw]

Amazing, as usual! Thank you, Leila. I really love learning from you. How would you do this in earlier versions of Excel?

[@harisuraj]

Thank you so much

[@wmfexcel]

Thanks for this nice trick, esp. using dynamic array in Named Range for Charting.

[@LeilaGharani]

Hope it will come in handy for you.

[@Luggruff]

6:00 You could also use =INDEX(B4:B18,MATCH(E4#,A4:A18,0)), and the benefit is the same as an INDEX/MATCH always is to a VLOOKUP: You kan use column names as references if your data is in a table, and it will stay the same no matter if you move it or add columns in between.

[@ireneogcanete5492]

Amazing! Thanks

[@alouettedeschamps]

Bravo, once again.

[@nikotra]

The use of hash symbol for defining spilled ranges is mind-blowing :)

[@LeilaGharani]

It really is a big step forward in Excel.

[@MuhammadKashifRizwan]

@@LeilaGharani hash symbol is not working in my Excel.

[@nancymahmoud1841]

brilliant Leila !

[@imranuddin2153]

Leila, I tried tweaking your formula a bit and found that this formula yielded the same result: SORT(FILTER(TbSales[[Company]:[Sales]],TbSales[Show]=1),2,-1) and seems much simpler. But I could be wrong, you're the expert.

[@bisikolok]

i was looking for this two weeks ago and sat down and used rank.avg in another column. but i knew i was going to start using your formula as soon as i clicked the video

[@LeilaGharani]

I hope it will come in handy for you Uğur.

[@rob4canada]

Recently, I did something very similar to this. Instead of the VLOOKUP, I choose to use the SORTBY command again and FILTER by the sorted column. For this example, that would be: =SORTBY(FILTER(B4:B18,C4:C18=1),FILTER(B4:B18,C4:C18=1),-1) So basically, you have a lot of options to choose from to get your second column.