How to Create an Excel Interactive Chart with Dynamic Arrays

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

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

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

[@benponderin]

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

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

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

[@garzagarza01]

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

[@trex-1393]

This video made my workbooks x10 more efficient than before.

[@LeilaGharani]

That's great to hear!

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

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

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

[@mattschoular8844]

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

[@LeilaGharani]

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

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

[@Craftman368]

These dynamic arrays are a game changer

[@LeilaGharani]

So true!

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

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

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

[@nsanch0181]

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

[@LeilaGharani]

Glad you like them!

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

[@sheryliously]

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

[@LeilaGharani]

Happy to help!

[@bangladeshexcelmeetup]

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

[@LeilaGharani]

You're very welcome!

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

[@zaidkanM]

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

[@LeilaGharani]

Glad it was helpful!

[@neilmurdoch9789]

You are the best. Very useful.

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

[@ahmadgarbaauwal6381]

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

[@akademipowerpointmalaysia9216]

Finding the lesson helpful! Thanks a lot!!

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

[@DougHExcel]

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

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

[@jimmyjammERRz]

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

[@serigamel]

again, exactly what i needed. thanks Leila!

[@17aig]

Dear Leila as always super excellent tutorial thx so much

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

[@williamkiarie]

That SORTBY is awesome. Thanks for sharing.

[@LeilaGharani]

It sure is :)

[@danuarzani6128]

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

[@Crazyboyzz90]

Im addicted to excel, thanks to Leila

[@LeilaGharani]

😘

[@dillibabu568]

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

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

[@NicholasWalker-g1t]

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

[@arumoydas7706]

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

[@HemanthKumar-lb4xt]

Excellent, the way of explanation is awesome 👌

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

[@ExcelExciting]

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

[@LeilaGharani]

That's so true!

[@67duiker]

Great example and use of array-functions

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

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

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

[@ryanmiguelsantiago]

Thank you, Leila! Very helpful and useful as always

[@naveed9999]

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

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

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

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

[@thedavidseatonshow]

Excellent and practical lesson as always.

[@johnborg5419]

Thanks Leila. Filter part was Great!!!

[@Citobiopsias]

Uauau, Now I watch your videos every single day

[@VSP4591]

Excellent presentation. Thank you

[@mateolopez6158]

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

[@jasveersingh7486]

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

[@Xyour_starX]

You always make my day. Thank you Leila

[@LeilaGharani]

Glad you like it :)

[@danielmathivathan7391]

Excellent explanation

[@sarmadali9493]

I Love your explanation.. Take care..

[@balloonmanyw]

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

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

[@denizschecter]

Hello Leila thanks for your effort. Great tutorial. But what is the alternative way to do the same thing if we do not have filter function for the case in the video?

[@aftabmunshi4749]

Amazing work Ma'am as usual

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

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

[@zeljkocrljenica7135]

Excel-ent, thank you for this tutorial

[@jake2.037]

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

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

[@dennisd5776]

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

[@LeilaGharani]

Glad to help Dennis :)

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

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

[@xenonexy]

This is very helpful!

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

[@lbeelle2812]

I like how you have showed us to use the show marker in this video to change the presentation of data but I'm wondering how to apply that to my situation. I have a list of quality control issues that I am pulling out of a master list by month and then count the number of occurances for that period for each quality issue. I have done that fine but now I'm having difficulty showing those results in a dynamic chart. One month might have only 5 issues, another month might have 7 or more etc. Do you have any suggestions please?

[@sasavienne]

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

[@jaywindley]

Love this video. Would like to make more "user friendly" by including checkboxes rather than a column of 1s and blanks. However that would add a lot of work on the front end by the sheet developer.

[@Star_boy369]

Another great video. Quick question: can Index match be used as an alternative here for those that don’t have the 365 subscription? Thanks.

[@wmfexcel]

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

[@LeilaGharani]

Hope it will come in handy for you.

[@FredericLEGUEN-Excel]

I love the second part of your video with SUMPRODUCT 😉

[@rgarlinyc]

Very clever! And beautifully explained, thank you LG!

[@LeilaGharani]

My pleasure RG :)

[@rezamakvandi1366]

Leila you are the best thank you👍

[@mussabeltayeb7793]

Thanks a lot for knowledge sharing

[@shaileshkarne9862]

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

[@LeilaGharani]

You're welcome 😊

[@ALPHERATZ3650]

Thanks for posting this.

[@anupamshukla2346]

Hi Leila, Is it possible to select two options in drop down at a time abd based on which make dynamic graphs on different selections

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

[@pardiss]

Hi thanks for your useful channel, I have a question please. with regard to indirectly name, my company names are not located in one column but in i one row, how i can include them in one group name in formula and name manager with hash sign?thank you

[@Luciano_mp]

Very good Leila. Thank you!👍👍👍

[@ninap6489]

Great video. can we sort on the prep/dynamic array table? I have some formulas such as sumifs in the prep table and want to Sort on the Grand Total in the prep table (this does not exist in the source table). It gives me 'You cant change part of array" error. Any suggestions/workarounds?

[@javedrasool6633]

Leila I really enjoyed your videos... I have a question... How create a dynamic map chart visualization of US states which shows unemployment rate or GDP of each state from 1960 to 2018. Please help in this regard. Corresponding year and corresponding values changing and fully dynamic.. Thanks

[@fahadpalladium2]

Amazing! Love it 😍

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