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

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

194 ratings | 2407 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 (78)
DIGITAL COOKING (5 days 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 (5 days 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 (5 days 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 (5 days ago)
great! such good news:)
ExcelIsFun (5 days ago)
About one week and the MSPTDA series will start back up! Thanks for your support : )
Wayne Edmondson (6 days ago)
Hi Mike.. nice.. love that TRANSPOSE to switch the orientation. Thumbs up!
ExcelIsFun (6 days ago)
Thanks for the Thumbs Up, Wayne!!! Glad you like the video : )
pmsocho (6 days ago)
Thumbs up!
ExcelIsFun (6 days ago)
Thanks, pmsocho!!!!! Go Team : )
Thanks Mike.
ExcelIsFun (6 days ago)
You are welcome, most EXCELlent Syed : )
QuickMadeUpName (7 days ago)
ExcelIsFun (7 days ago)
Glad it is nice for you, QuickMadeUpName!!! Thanks for your support ! P.S. Love the name : )
Kartick Chakraborty (8 days 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 (8 days ago)
Very cool tip Mike, great job!!
nboisen (8 days ago)
How would you do this without the new calculation engine? For example, Transpose will not work without "spill"...
nboisen (8 days ago)
+ExcelIsFunI had used "Ctrl+Shift+Enter" but couldn't get transpose to work initially. Now it's fine! Thanks!
nboisen (8 days ago)
+ExcelIsFun Yes, of course!
ExcelIsFun (8 days ago)
Did you like the video? I hope : )
ExcelIsFun (8 days ago)
Same formula, you just have to highlight all the cells in advanced and enter formula with Ctrl + Shift + Enter.
Bart Titulaer (8 days 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 (8 days 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 (8 days ago)
Nice trick from a briluant teacher :-)
ExcelIsFun (8 days ago)
Thanks, Mohamed!!!!!
RRR program (9 days ago)
Fantastic Mike..... Your energy and enthusiasm towards Excel is none to anyone
ExcelIsFun (9 days ago)
Glad it is fantastic for your RRR!!! It is fantastic to be part of the Team : )
John Borg (9 days ago)
Thanks for the nice morning Breakfast Mike :) :) Nice.....very Nice !!!
ExcelIsFun (9 days ago)
You are welcome for the breakfast!!! For a formula guy like yourself, this should be useful - even without the Spilled Array part...
nimrodzik1 (9 days ago)
Simlpe, useful and fun. Thanks :)
ExcelIsFun (9 days 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 (9 days ago)
You are god of excel
ExcelIsFun (9 days ago)
Just a guy having fun with Excel : ) Thanks for your support, Ankit!!!
Bill Szysz (9 days ago)
Great....as always!!! :-))
ExcelIsFun (7 days ago)
+Bill Szysz @John Borg, Poet that is Royal and Rural : )
Bill Szysz (7 days ago)
+ExcelIsFun , @John Borg I am at most rural "poet" ;-)))
ExcelIsFun (9 days ago)
+John Borg , Bill Szysz = World Poet : )
John Borg (9 days 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 (9 days ago)
Thanks, O Poet of Array and Power Query : )
Hussein Korish (9 days ago)
Wow ... it spiled to the right too ...i did not expect that ... that's Great ... Thanks Mike
ExcelIsFun (9 days ago)
You are welcome, Hussein : ) Thanks for the support!
ExcelIsFun (9 days ago)
It spilled to the right because the range was horizontal, a row filled with columns.
Micah Fischer (9 days ago)
Very helpful teaching tool! Thanks again for all the tips & tricks.
ExcelIsFun (9 days ago)
You aer welcome, Micah!!!! Thanks for the support : )
Richard Hay (10 days 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 (9 days ago)
+Richard Hay , You are welcome! Thanks for the support, Richard, which helps me to keep going : )
Richard Hay (9 days 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 (9 days 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 (9 days 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 (9 days 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 (10 days ago)
Why is Ctrl+Shift+Enter not used? Sorry I could not get it from video...
ExcelIsFun (10 days 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 (10 days ago)
ExcelIsFun (10 days ago)
Thank you for being part of the Team, HARISH!!!! I will keep up the fun and energy : )
Sevag Barsoumian (10 days ago)
yes useful Thanks Mike
ExcelIsFun (10 days ago)
You are welcome, Sevag!!!! Thanks as always for your support : ) : )
Gentle Raj (10 days ago)
It's great and very useful.
ExcelIsFun (10 days ago)
Thanks for the support, Gentle Raj!!! Glad it is useful : )
Deepak Chopra (10 days 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 (8 days 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 (8 days 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 (8 days ago)
+ExcelIsFun flip the range is a great trick!!! :-))
Oakley Turvey (8 days 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 (9 days 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 (10 days ago)
ExcelIsFun (10 days ago)
+1iuh Thanks for being #2 : )
ExcelIsFun (10 days ago)
Thanks for the #1!!!!
1iuh (10 days ago)

Would you like to comment?

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