HomeОбразованиеRelated VideosMore From: ExcelIsFun

Excel Dynamic Arrays & Charts: Spilled Arrays in Charts with Defined Names (Excel Magic Trick 1527)

214 ratings | 3051 views
Download Excel File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1526-1527.xlsx This video discusses how to get Spilled Arrays into Charts. Direct Spilled Arrays don’t seem to work, but we can use the Old Fashion Method and use Defined Names to point to our Spilled Arrays.. Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm Excel Dynamic Arrays: Fully Dynamic Cross Tabulated Reports? Unbelievable! EMT 1520 : https://www.youtube.com/watch?v=ZXn0D4Ch7zg
Html code for embedding videos on your blog
Text Comments (50)
Ismail Ismaili (5 months ago)
you always blow my mind up with new ideas you are an inspirational thank you Mr.Mike
Peter Bartholomew (5 months ago)
Great stuff Mike! As the one person on this planet that hasn't used a direct reference for coming up 10 years now it is important to me to see that defined names 'play nice' with dynamic ranges. If, as it seems, they act as a wrapper enabling the spilt array syntax 'to boldly go' where no man had planned to take it, that is a pure bonus! p.s. Other uses of Names as wrappers include: MXL macro commands; dynamic picture assignments; range intersection and ISREF for conditional formatting; array formulas without CSE (now history). None of it exactly mainstream but the link to charts is far more significant.
Rhonda Darbouze (5 months ago)
Thanks !!
Awesh Bhornya (5 months ago)
Awesome tricks from your channel but the problem is we don't have these formulas and functions for dynamic arrays till date
Vida (5 months ago)
Nice trick
Chris Mancinelli (5 months ago)
Nice video Mike!
ExcelIsFun (5 months ago)
Glad you like it, Chris!!!! Thanks for watching : )
Guy Roland KOMAN (5 months ago)
Great trick! Thanks a lot.
ExcelIsFun (5 months ago)
You are welcome a lot! Thanks for your support, Guy : )
John Borg (5 months ago)
This is a good one Mike. Thanks :) :)
ExcelIsFun (5 months ago)
You are welcome, John : ) : )
Amazing. Thanks Mike.
ExcelIsFun (5 months ago)
Glad it was amazing, Syed : ) Thanks foe watching and having fun : )
Sal A (5 months ago)
Excellent manipulation. Again this reconfirms my opinion that Microsoft does not do good job of testing and releases these new functions/tools (power pivot/query) in a hasty manner.
ExcelIsFun (5 months ago)
Glad the manipulation was helpful, Sal A!!! We HOPE that MS will make improvements : )
You're the man Mike.
ExcelIsFun (5 months ago)
Just a guy having fun with Excel : ) Thanks for the support, Kerry King!!!
Wayne Edmondson (5 months ago)
Hi Mike.. great video. Nice to see the old tricks still coming in handy, although it would be great if Microsoft just included the ability to add the # hash directly to the spilled array range formula reference without having to name it first. So maybe that will be dynamic array formulas 2.0. Thanks and Thumbs up!
ExcelIsFun (5 months ago)
You are welcome, Wayne!!! Most of what we do in Excel is Old School ; ) And yes, hopefully the chart engine will accept RAD43# , ROA2018# and TAX2018# soon! (yes, those are all really cell addresses : )
Micah Fischer (5 months ago)
Down to 3 more weeks till we upgrade to Office 365 and these videos are getting me so pumped for Dynamic Arrays! As a serious shortcut user since Window 3.1, I always appreciate you mentioning the shortcuts in Excel even if I know 90% of them. Makes life so much more productive :)
ExcelIsFun (5 months ago)
+Rico S , Keyboard rule!!!!!
ExcelIsFun (5 months ago)
I can't wait for you either!!!!! 3 more weeks : ) Then be sure when you get it on the first day to sign up for Insider.
Rico S (5 months ago)
Yep, shortcuts are magical. I spend a lot of time during the day watching people (I do an element of training in my role) mousing about like slow tortoises. It makes me so happy to imagine Mike keying his way to Excel genius level as I watch his videos.
DRSteele (5 months ago)
Nice workaround, Mike. Notice that the cell formatting for the parent cell does not apply to the spilled cells, which is irritating. MS could fix several problems, like that one and charting, by allowing spillies in Excel Tables.
ExcelIsFun (5 months ago)
Yes, as was painfully discussed in a few of these Dynamic Array comments sections, the Spilled Array can see the formatting and spill that too : (
Mercedes Galarce (5 months ago)
ExcelIsFun (5 months ago)
You are welcome for the videos, Mechi!!!!!
Hussein Korish (5 months ago)
Great Mike ....That's what i'm talking about .... i never get dissappointed in your channel
ExcelIsFun (5 months ago)
Glad you are having fun! Thanks for your support, Hussein!!!
Matt Schoular (5 months ago)
Thank you Mike. I'm hoping that by the time this rolls out they will have some of these anomalies fixed as part of the rollout. Keep up the great "problem solving" workarounds. Much appreciated.
ExcelIsFun (5 months ago)
You are welcome, Wayne!!!! Yes, everything we do in Excel is ultimately a workaround, because once MS releases great new features like Excel Tables or Power Query or Dynamic Arrays, we then find new uses that we never thought of before and then we have to create some "workaround" for the new solution we are trying to create!
Dave Bowman (5 months ago)
Beats using the offset and counta for dynamic named ranges
ExcelIsFun (5 months ago)
+Rico S , I almost always use INDEX for Dynamic Ranges. Ocassionally there are great uses for OFFSET, like Subtotals (video example: https://www.youtube.com/watch?v=o325ACwuRgo), but for Dynamic Ranges, INDEX is the way : )
Rico S (5 months ago)
​+ExcelIsFun I've never really liked Offset and always use Index. Is there an instance where you would use offset rather than index (i.e. because index won't work)? I just don't like the volatilty of Offset. I don't trust it! And it's slower. Probably.
ExcelIsFun (5 months ago)
Totally!!!! I have used INDEX and OFFSET for decades, and now : ) I still remember my first time teaching Dynamic Ranges with OFFSET in my Excel Class at Highline College back in about 2004. Now I can't wait for the computer labs to all have Office 365 so I can just teach Excel Tables and Spilled Arrays for Dynamic Ranges!
Anil Dutt Bhargava (5 months ago)
I want office 365 insider
Anil Dutt Bhargava (5 months ago)
+ExcelIsFun already purchase office 2019. But new function in office 365. Your all array video watching but easy way of office 365insider
ExcelIsFun (5 months ago)
I want you to have it too, Anil!!!!!
RRR program (5 months ago)
This really is COOOOLLLLL trick..... Wonder how people find one way or the other to get the result ... :D
ExcelIsFun (5 months ago)
Yes: Cooooooooollllll : )
RRR program (5 months ago)
Mike i would recommend, instead making spilled array videos in "Excel Magic Trick" series... These videos should be under another series or playlist namely "Spilled Array Excel " series... coz, later it will make easy to follow under this series
ExcelIsFun (5 months ago)
I already have that playlist. It is predominately featured at the excelisfun channel on the front page : ) Here is the link: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx If you go to the excelsifun channel (https://www.youtube.com/user/excelisfun) it is the first playlist under my Excel Basics and Advanced Excel Classes : )
RRR program (5 months ago)
Excel : Excelisful : : Human : Soul
ExcelIsFun (5 months ago)
WOW!!!!!! Thanks for the really cool analogy : )
edgie da pogi (5 months ago)
Bravo Mike
ExcelIsFun (5 months ago)
Glad it is good for you, edgie!!!! Thanks for the support : )
pmsocho (5 months ago)
Thumbs up! :)
ExcelIsFun (5 months ago)
Thanks for the Thumbs Up, Teammate pmsocho!!!!

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.