How to Create an Excel Interactive Chart with Dynamic Arrays

[@LeilaGharani]

Create charts that wow your audience. Learn the secrets now-start today! 👉 link.xelplus.com/yt-g-interactive-charts-course 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!

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

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

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

[@trex-1393]

This video made my workbooks x10 more efficient than before.

[@LeilaGharani]

That's great to hear!

[@garzagarza01]

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

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

[@bahatidramou4818]

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

[@vijaygusain119]

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

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

[@benponderin]

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

[@sheryliously]

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

[@LeilaGharani]

Happy to help!

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

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

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

[@mattschoular8844]

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

[@LeilaGharani]

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

[@bangladeshexcelmeetup]

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

[@LeilaGharani]

You're very welcome!

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

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

[@naveed9999]

Leila this was amazing. I really liked the thumbs up sound syncing with your lips!! Is this your hidden spill!

[@catbou2885]

Fantastic. You remain my favorite educator on the internet.

[@zaidkanM]

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

[@LeilaGharani]

Glad it was helpful!

[@nsanch0181]

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

[@LeilaGharani]

Glad you like them!

[@HemanthKumar-lb4xt]

Excellent, the way of explanation is awesome 👌

[@Craftman368]

These dynamic arrays are a game changer

[@LeilaGharani]

So true!

[@arielspalter7425]

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

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

[@17aig]

Dear Leila as always super excellent tutorial thx so much

[@jimmyjammERRz]

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

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

[@DiegoZaccariotto]

This is a great functionality of excel... I really wished they backported it to excel 2016... it would make my life easier with a couple of dashboards where office 365 is not available.

[@borstenpinsel]

Everything shown herein can be done in old excel versions as well. You just need more complex formulas, and helper cells. Sadly because of the way excel deals with formulas and calculated cell values, it's also going to be a lot slower if you use it multiple times in a dashboard. But it certainly is possible

[@DiegoZaccariotto]

@@borstenpinsel yaeh, I know. I am using those complex formulas. Dynamic arrays would just make it way easier to get to the same results.

[@shaileshkarne9862]

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

[@LeilaGharani]

You're welcome 😊

[@sarmadali9493]

I Love your explanation.. Take care..

[@NicholasWalker-g1t]

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

[@danuarzani6128]

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

[@arumoydas7706]

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

[@Citobiopsias]

Uauau, Now I watch your videos every single day

[@ahmadgarbaauwal6381]

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

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

[@williamkiarie]

That SORTBY is awesome. Thanks for sharing.

[@LeilaGharani]

It sure is :)

[@neilmurdoch9789]

You are the best. Very useful.

[@Crazyboyzz90]

Im addicted to excel, thanks to Leila

[@LeilaGharani]

😘

[@ExcelExciting]

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

[@LeilaGharani]

That's so true!

[@DougHExcel]

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

[@OCteahead]

Love your videos. I’ve used many of your ideas at work to create spreadsheets, and more importantly, graphs, to wow my coworkers. Since you are buried in data all day long, I will most humbly remind you that the word DATA is plural...”data are”, not “data is”. 🙂

[@LeilaGharani]

Thanks for the feedback Scott. Goes to show that English isn't my first language :)

[@OCteahead]

Leila Gharani - Wow! One would never know it!

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

[@kimstenback5645]

How did you manage to make a table out of a spill range, whenever I try to do any form of chart or table I get a #spill! range error. great video

[@akademipowerpointmalaysia9216]

Finding the lesson helpful! Thanks a lot!!

[@FredericLEGUEN-Excel]

I love the second part of your video with SUMPRODUCT 😉

[@khalilkarbani6178]

Hello Leila - i just love your voice, its very calming and relaxing. a quick question for you: on a list from a dropdown menu that i have created (data - data validation - list) the writing is so small that i can hardly see it. if i reduce my computer resolution then i can read it but then the excel cell are too big. CAN I make this dropdown list writing bigger without going into my computer setting. I hope this makes sense.

[@rezamakvandi1366]

Leila you are the best thank you👍

[@r_ak_xxxx0144]

Thanks for this Leila. Very helpful. Am trying to use the same technique to create a wage analysis. I have a table with Name, Base Pay and Title as columns. I used the FILTER option to select the names that match a given Title and pull the salary using the VLOOKUP. Fine till now... Now, I want to add the MIN, MAX and AVG salaries in the selected rage as a line in the chart, but I'm struggling to do that as MIN does not behave fine with the spill ranges. How do I achieve this outcome?

[@kenc763]

This is such a helpful video -- thank you! I'm now struggling about how to have a dynamic line chart where there are two variables: time on x axis (dynamic data set on which years are to be included), $ on the y axis and each line in the chart would represent a different company (dynamic on which companies are to be included). I'll look to see if you have a video addressing this. Thanks again!

[@Baseball17Bucks]

HAVING THE SAME ISSUE!!!

[@danielmathivathan7391]

Excellent explanation

[@franciscomoutinho4715]

Hi Leila, great video! Some years ago, you posted a video called “Excel Dynamic Chart Range (…)”. I wonder if there’s a way to merge these two lessons - meaning, a chart with both dynamic ranges and series. Thank you very much!

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

[@VSP4591]

Excellent presentation. Thank you

[@sasavienne]

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

[@Luciano_mp]

Very good Leila. Thank you!👍👍👍

[@andrewmoss6449]

This is a really cool method to combine a chart with dynamic arrays! One big limitation I've noticed with dynamic array lists though is that they cannot be formatted as tables and you cannot use filtering on them. I'd like to think in time this will be something Microsoft address.

[@paramveerssachdeva]

1) I sadly subscribe to office 365 business premium, and have multiple licences, Microsoft excel features are far lesser than google sheets, and also don't work when we try to collaborate with others whilst working in the browser mode. The warning we get is that multiple features aren't available on the browser mode, like toolbar, active x controls, so the UNIQUE, SORT, FILTER which are new functions do not work at all on the browser. Even the data validation cancels out in the browser. Google sheets on the other had is easier to collaborate on and works wonders. Especially the import range feature.

[@brianxyz]

@@paramveerssachdeva Unique, etc. are all available online in Excel.

[@paramveerssachdeva]

@@brianxyz Yes, I know, I use them, but are only applicable if i am working on the excel sheet from my hard drive, but when i collaborate with my team, as my team members work online from their browsers, as that makes data syncing error free (otherwise if everyone works from their hard drive with ONEDRIVE app, conflicting data / multiple sheets are made, causing chaos), those formulas don't work when you collaborate through your browsers.

[@paramveerssachdeva]

@@brianxyz Appreciate your reply, and you are right. I subscribe on the office insider fast, So i had them, but weren't working earlier, till a few months ago. and now they are working, i tried after you told me. regards

[@jasveersingh7486]

Thank you for such a nice example ....how this can be formatted based on the sales target met /not met ? (Individual and teams)

[@serigamel]

again, exactly what i needed. thanks Leila!

[@darrylmorgan]

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

[@dennisd5776]

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

[@LeilaGharani]

Glad to help Dennis :)

[@dillibabu568]

Dear Leila, thanks. Do you have a similar video for a horizontal table data?

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

[@67duiker]

Great example and use of array-functions

[@santiagocabred1943]

Thanks for this video, Leila! Is there any way that I can create a graph with two different series that depend on a different column and that can update automatically? For example, I want the same graph but with a series for the companies from the West Coast and another from the East Coast. Thanks again!

[@ruchitradia7063]

Hi Leila, Thank you for this vdo. however,i would like to request you small thing. whenever you publish anything related to office 365, kindly also publish the alternate method or formula in video, that can be used as per old excel's like 2016 etc. because everyone does not posses latest version always. so it will be really helpful, in case alongwith the office 365 formulas and ideas, you can also suggest the older methods or formulas, that goes well with the older excel version, possibly in the later half of the same video. Thank you in advance for this...! :-)

[@wmfexcel]

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

[@LeilaGharani]

Hope it will come in handy for you.

[@aftabmunshi4749]

Amazing work Ma'am as usual

[@bondanswastika950]

Really Great... 👍🏻👍🏻

[@farhatali9062]

Thanks for sharing thid. Amazing addition to my skills

[@LeilaGharani]

My pleasure!

[@fahadpalladium2]

Amazing! Love it 😍

[@AnushaUppuluri]

Hi Leila, The horixontal axis reference is pointing to the entre workbook automatically even if I set it manually to a particular sheet. Also only the starting cell is being selected even after adding a # sign in the reference. Any thoughts

[@younessloukili4203]

Very interesting Thank you Leila

[@ryanmiguelsantiago]

Thank you, Leila! Very helpful and useful as always

[@balloonmanyw]

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

[@antoniorodriguez7864]

Hi Leila, I have been looking for this kind of work for a spreadsheet that I am working on, quick question for you, does it work for pivot tables and pivot charts? I tried doing it on a pivot chart that is based on a pivot table and it gave me an error, thank you in advance

[@shaheershujaat2223]

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

[@mussabeltayeb7793]

Thanks a lot for knowledge sharing

[@MalinaC]

Awesome! Names always kelp 🙂

[@LeilaGharani]

They sure do Malina :)

[@xenonexy]

This is very helpful!

[@johnborg5419]

Thanks Leila. Filter part was Great!!!

[@rgarlinyc]

Very clever! And beautifully explained, thank you LG!

[@LeilaGharani]

My pleasure RG :)

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

[@Xyour_starX]

You always make my day. Thank you Leila

[@LeilaGharani]

Glad you like it :)

[@mirasforaccounting570]

I am a First comment ,you are fantastic lady and I love you so much. Thank you

[@LeilaGharani]

That was really fast :) Thanks for your support.

[@sfityani]

Hello .. thank you for every single video you provide .. each and everyone is useful.. I am trying to use sort formula with the filter formula but it is sorting 0 then 10 to 18 and then 2 to 8 .. I tried everything but couldn't know why .. may you help me ? Can I send clearer picture by email?

[@SAP_IBP]

Like all your followers, big thank from France. I have a tricky question about graphics I wish submitting you. In an SAP framework, using IBP module, data are presented in an excel format, however in an horizontal time axis, not vertical. This stops the Excel table to work nicely, in particular with regards to graphic. There is the switch row/column option but not fully satisfying. I also tried with pivot chart, however limited. My wish would be to get a graph, dynamically adaptable, lije in your video, this to avoid user configuring. My current option is vba based, works fine but a little complicated with regards to maintenance. Thk anyway for your channel, Daniel

[@jorgearturomontesdiaz8049]

I love Leila.. is so Smart 😍

[@hamidrezamozafary3274]

سلام عید شما مبارک . مث همیشه عالی بود

[@ricos1497]

Very cool. I like the sort view on the chart, I think that's a nice presentation feature. It would be interesting to see an "Other" column - perhaps on the secondary axis if really necessary - that summed the total of all other values. I think that'd be more likely a scenario in my world, where people might want to see the top X sales, with the rest being amalgamated onto a single bar. I suppose you could just use a FILTER to get the top X in the list and subtract from the total. All good though, you've got me thinking! Although my OCD was triggered by the layout of the Teams in the second example. I was hoping to see them in [Team Name],[Team Colour] format, in a nicely formatted Excel table.....!

[@LeilaGharani]

That would be a great solution. Let us know how it worked out.

[@ricos1497]

@@LeilaGharani I will. Although I might be quite busy over the next month, sitting inside staring at the walls...

[@mateolopez6158]

Can you use the "*" in filter or sort formula when you define the criterials? For example =filter(A1:A12,(B1:B12="Yell*")

[@bunhakuy55]

Hi, Leila! I want to know if we can put a drop-down menu button using VBA. By that I meant another buttons show up as a list not a list of word from any cell. Thank you!