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

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

177 ratings | 1789 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 (46)
JOYDEB CHAKRABARTY (2 days ago)
Thanks !!
Awesh Bhornya (3 days ago)
Awesome tricks from your channel but the problem is we don't have these formulas and functions for dynamic arrays till date
Vida (3 days ago)
Nice trick
Chris Mancinelli (5 days ago)
Nice video Mike!
ExcelIsFun (4 days ago)
Glad you like it, Chris!!!! Thanks for watching : )
Guy Roland KOMAN (5 days ago)
Great trick! Thanks a lot.
ExcelIsFun (5 days ago)
You are welcome a lot! Thanks for your support, Guy : )
John Borg (5 days ago)
This is a good one Mike. Thanks :) :)
ExcelIsFun (5 days ago)
You are welcome, John : ) : )
Amazing. Thanks Mike.
ExcelIsFun (5 days ago)
Glad it was amazing, Syed : ) Thanks foe watching and having fun : )
Sal A (5 days 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 days ago)
Glad the manipulation was helpful, Sal A!!! We HOPE that MS will make improvements : )
You're the man Mike.
ExcelIsFun (5 days ago)
Just a guy having fun with Excel : ) Thanks for the support, Kerry King!!!
Wayne Edmondson (5 days 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 days 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 days 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 days ago)
+Rico S , Keyboard rule!!!!!
ExcelIsFun (5 days 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 days 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 days 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 days 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 : (
Mechi GOV (5 days ago)
AWESOME!!! YOU ARE GREAT!!! THANKS FOR YOUR VIDEOS!!!!! :)
ExcelIsFun (5 days ago)
You are welcome for the videos, Mechi!!!!!
Hussein Korish (5 days ago)
Great Mike ....That's what i'm talking about .... i never get dissappointed in your channel
ExcelIsFun (5 days ago)
Glad you are having fun! Thanks for your support, Hussein!!!
Matt Schoular (6 days 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 days 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 (6 days ago)
Beats using the offset and counta for dynamic named ranges
ExcelIsFun (5 days 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 days 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 days 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 (6 days ago)
I want office 365 insider
Anil Dutt Bhargava (5 days 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 days ago)
I want you to have it too, Anil!!!!!
RRR program (6 days ago)
This really is COOOOLLLLL trick..... Wonder how people find one way or the other to get the result ... :D
ExcelIsFun (5 days ago)
Yes: Cooooooooollllll : )
RRR program (6 days 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 (6 days 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 (6 days ago)
Excel : Excelisful : : Human : Soul
ExcelIsFun (6 days ago)
WOW!!!!!! Thanks for the really cool analogy : )
edgie da pogi (6 days ago)
Bravo Mike
ExcelIsFun (6 days ago)
Glad it is good for you, edgie!!!! Thanks for the support : )
pmsocho (6 days ago)
Thumbs up! :)
ExcelIsFun (6 days 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.