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

Dynamic Spilled Arrays: Show All Formulas For All Columns in Cells as Auditing Trail EMT 1524

209 ratings | 2919 views
Download Excel File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1524-1525.xlsx In this video learn how to take multiple columns filed with formulas and list all the formulas and the cell reference that the formula sits in in a Vertical Column with a single cell formula. See the Functions: ADDRESS, ROW, COLUMN, FOMULATEXT, IFNA and TRANSPOSE. Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516)
Html code for embedding videos on your blog
Text Comments (80)
Mitch Romag (2 months ago)
Great videos! I came across your channel (I subscribed) in search to a problem I have with sorting a table with a column containing drop lists in it. The column with the drop down lists WILL NOT sort! Is this an easy fix? There are column has drop down lists containing many different lists. Thanks!!
Ismail Ismaili (2 months ago)
God I can't stop watching your videos Mr. Mike you always give me all what i need
DIGITAL COOKING (2 months ago)
cool technics, I like the way you work with excel, and I'm waiting for new videos about MSPTDA series, please continue the series it was great
DIGITAL COOKING (2 months ago)
of course! me too I like array formulas; in reality, I'm interested in everything in excel; just because I'm looking for ((70-779 exam)) and found you MSPTDA series very helpful for studying and preparing for the exam , it's a matter of time ..........thanks Mike
ExcelIsFun (2 months ago)
+DIGITAL COOKING , I can't wait either, even though I LOVE Array Formulas, I was not expecting to have to make all these videos, but it is sort of my duty as an Excel MVP to make videos about an area that is my expertise...
DIGITAL COOKING (2 months ago)
great! such good news:)
ExcelIsFun (2 months ago)
About one week and the MSPTDA series will start back up! Thanks for your support : )
Wayne Edmondson (2 months ago)
Hi Mike.. nice.. love that TRANSPOSE to switch the orientation. Thumbs up!
ExcelIsFun (2 months ago)
Thanks for the Thumbs Up, Wayne!!! Glad you like the video : )
pmsocho (2 months ago)
Thumbs up!
ExcelIsFun (2 months ago)
Thanks, pmsocho!!!!! Go Team : )
Thanks Mike.
ExcelIsFun (2 months ago)
You are welcome, most EXCELlent Syed : )
QuickMadeUpName (2 months ago)
nice
ExcelIsFun (2 months ago)
Glad it is nice for you, QuickMadeUpName!!! Thanks for your support ! P.S. Love the name : )
Kartick Chakraborty (2 months ago)
Sir, for your kind information, after lots of trying, I've found a formula that helps solving this problem and dealing with these merge cells but when the destination data and their format are as same as source data. But if I change the destination data and their format, it won't give the right result. I'm attaching this finished file to your google+ account. Check this file, you'll understand everything. Please reply me sir. I'm your die hard fan.
Chris Mancinelli (2 months ago)
Very cool tip Mike, great job!!
nboisen (2 months ago)
How would you do this without the new calculation engine? For example, Transpose will not work without "spill"...
nboisen (2 months ago)
+ExcelIsFunI had used "Ctrl+Shift+Enter" but couldn't get transpose to work initially. Now it's fine! Thanks!
nboisen (2 months ago)
+ExcelIsFun Yes, of course!
ExcelIsFun (2 months ago)
Did you like the video? I hope : )
ExcelIsFun (2 months ago)
Same formula, you just have to highlight all the cells in advanced and enter formula with Ctrl + Shift + Enter.
Bart Titulaer (2 months ago)
This is again a new way of auditing as spreadsheet. May I challenge you (or any of your vieuwers)? would it be possible to do this for a whole sheet, in stead of a row? And then, would it be possible to show ony the unique values? and then delete anything that is between the brackets. So in the end you should have a list of all used formula's. Greetings Bart.... Have fun with thinking about this...;)
ExcelIsFun (2 months ago)
Aaaa.... The whole sheet? 17,000,000,000 cells? Or just a section? I guess it is possible, but I have not tried it yet.
Mohamed Chakroun (2 months ago)
Nice trick from a briluant teacher :-)
ExcelIsFun (2 months ago)
Thanks, Mohamed!!!!!
RRR program (2 months ago)
Fantastic Mike..... Your energy and enthusiasm towards Excel is none to anyone
ExcelIsFun (2 months ago)
Glad it is fantastic for your RRR!!! It is fantastic to be part of the Team : )
John Borg (2 months ago)
Thanks for the nice morning Breakfast Mike :) :) Nice.....very Nice !!!
ExcelIsFun (2 months ago)
You are welcome for the breakfast!!! For a formula guy like yourself, this should be useful - even without the Spilled Array part...
nimrodzik1 (2 months ago)
Simlpe, useful and fun. Thanks :)
ExcelIsFun (2 months ago)
It is very useful because a complex Model without a clear trail of how it was created, is not such a good model : ) Thanks for your support, nimrodzik1!!!
Ankit Shivi Verma (2 months ago)
You are god of excel
ExcelIsFun (2 months ago)
Just a guy having fun with Excel : ) Thanks for your support, Ankit!!!
Bill Szysz (2 months ago)
Great....as always!!! :-))
ExcelIsFun (2 months ago)
+Bill Szysz @John Borg, Poet that is Royal and Rural : )
Bill Szysz (2 months ago)
+ExcelIsFun , @John Borg I am at most rural "poet" ;-)))
ExcelIsFun (2 months ago)
+John Borg , Bill Szysz = World Poet : )
John Borg (2 months ago)
Bill Szysz is like the National Poet of Excel :) but you are the father :) :) because you have taught me and lots of others alot over the years, and more to come. Thanks Guys.
ExcelIsFun (2 months ago)
Thanks, O Poet of Array and Power Query : )
Hussein Korish (2 months ago)
Wow ... it spiled to the right too ...i did not expect that ... that's Great ... Thanks Mike
ExcelIsFun (2 months ago)
You are welcome, Hussein : ) Thanks for the support!
ExcelIsFun (2 months ago)
It spilled to the right because the range was horizontal, a row filled with columns.
Micah Fischer (2 months ago)
Very helpful teaching tool! Thanks again for all the tips & tricks.
ExcelIsFun (2 months ago)
You aer welcome, Micah!!!! Thanks for the support : )
Richard Hay (2 months ago)
Will you do a video on SORTBY? Also do you know the ACTUAL reason MS developed SINGLE and what did they intend it to be used for? It appear so be a solution in search of a problem!
ExcelIsFun (2 months ago)
+Richard Hay , You are welcome! Thanks for the support, Richard, which helps me to keep going : )
Richard Hay (2 months ago)
Thanks a lot> Your SINGLE answer makes sense--I had not considered the "error" issue and I personally rarely use merge and center. You do a super job. You are having to answer certain questions from viewers just because you have access to the tools and many viewers do not. We appreciate your patience with questions that we can normally answer on our own!! :)
ExcelIsFun (2 months ago)
+Richard Hay , that is funny: Underwhelmed, and as you said a solution in search of a problem... But really the only reason is to accommodate the cases where Implicit intersection WAS used in older versions, like with Merge And Center - they had to put it in to avoid errors in these rare cases.
Richard Hay (2 months ago)
I did watch the video but was underwhelmed by SINGLE still wondered what motivation MS had in developing it. SortBy probably will be obvious once I get access to the Dynamic Arrays. Is it like DAX to use, for example, a number column to sort months so they are not Alpha Sort, or is it to simultaneously sort multiple columns?
ExcelIsFun (2 months ago)
I did not do a video on SORTBY because it seemed straight forward. But I did do a comprehensive video about all the others, including SINGLE: https://www.youtube.com/watch?v=7jJMDGZpjSk
imkuldeep (2 months ago)
Why is Ctrl+Shift+Enter not used? Sorry I could not get it from video...
ExcelIsFun (2 months ago)
This is the 10th video in the series about the new Excel Calculations Engine in Office 365. Here is the whole playlist of videos, and if you watch the first one it tells all about this new invention where we don;'t have to use Ctrl + Shift + Enter: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx
HARISH PAL DHIR (2 months ago)
HATTS OFF SIR PLEASE KEEP THIS WAY IN VERY ENERGETIC MANNER I AM GREAT FAN OF URS 😇😇😇
ExcelIsFun (2 months ago)
Thank you for being part of the Team, HARISH!!!! I will keep up the fun and energy : )
Sevag Barsoumian (2 months ago)
yes useful Thanks Mike
ExcelIsFun (2 months ago)
You are welcome, Sevag!!!! Thanks as always for your support : ) : )
Gentle Raj (2 months ago)
It's great and very useful.
ExcelIsFun (2 months ago)
Thanks for the support, Gentle Raj!!! Glad it is useful : )
Deepak Chopra (2 months ago)
Hi, Thanks for all your videos. I have one query and one request Query: Can you create two dynamic array lists one below the other such that the second list begins immediately after the first ends. The first list has dynamic number of items. Request: Can you start a poll asking how many believe that Microsoft should provide the new calc. engine to Office 2013 and Office 2016 users as well. You may then with the results persuade Microsoft to extend it to existing users as well. Thanks
ExcelIsFun (2 months ago)
+Bill Szysz , yes, but your amazing formulas using SEQUENCE are just un-real : ) I made a video about your "Take Column and Make it a Proper Data Set" example and will post it next week : ) Can't wait!!!!!
ExcelIsFun (2 months ago)
​+Oakley Turvey , 100% not false modesty. I left High School (here in America) illiterate (could barely read, could write only simple sentences, did not know my multiplication tables and simple logic eluded me). I had to fight REALLY hard in my 20s in Community Colleges and then again in the 1990s in graduate school to gain my missing basic skills and then build on those to lift myself to a moderate level of knowledge. To this day, I am slow and miss basic things at first or second glance that most people pick up quickly. BUT... that experience is what makes me an OK teacher because I understand what it is like to not understand. This experiences of having to piece things together to try and construct a story that makes complicated things easier to understand, is why sometimes the stories I tell (videos and classroom lectures) are successful. As for Microsoft, they do want to listen to ideas from key advocates and influencers - the whole MVP program and User Voice and many other avenues are their way to trying to make the product match what users want! And these programs have been wildly successful. But, for whatever reason, the occasional times that I have tried to make contributions in this context, it does not work. They do not listen to me. They do not invite me back for further conversations. No matter at all : ) The key to life is: to do what you love, try to make the world a better place, treat people with love and caring, and have fun!!!!! Go Team!
Bill Szysz (2 months ago)
+ExcelIsFun flip the range is a great trick!!! :-))
Oakley Turvey (2 months ago)
+ExcelIsFun False modesty Mike. You are definitely that smart...possibly too smart for them. They should listen to ideas from key advocates and influencers like yourself though., and maybe pass on any reasoning behind why they make certain choices so you can share with the wider user community. I have been frustrated over the years with them introducing a great new feature and not telling anyone... Sounds like it's MS who are not smart enough. Keep it FUN.
ExcelIsFun (2 months ago)
+Bill Szysz , Awesome, Poet of Poets!! I can NOT wait : ) : ) Did you see the crazy way I added a column to the bottom of a column bu using INDEX to flip the range...
Hokhin Yuen (2 months ago)
1
ExcelIsFun (2 months ago)
+1uh Thanks for being #2 : )
ExcelIsFun (2 months ago)
Thanks for the #1!!!!
1uh (2 months ago)
2

Would you like to comment?

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