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

Подписаться 1 млн

Просмотров 6 тыс.

50% 1

Download Excel File: people.highlin...
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: people.highline...
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)

Опубликовано:

8 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 89

@rrrprogram8667 5 лет назад

Fantastic Mike..... Your energy and enthusiasm towards Excel is none to anyone

@excelisfun 5 лет назад

Glad it is fantastic for your RRR!!! It is fantastic to be part of the Team : )

@HusseinKorish 5 лет назад

Wow ... it spiled to the right too ...i did not expect that ... that's Great ... Thanks Mike

@excelisfun 5 лет назад

It spilled to the right because the range was horizontal, a row filled with columns.

@excelisfun 5 лет назад

You are welcome, Hussein : ) Thanks for the support!

@utubeAgape 3 года назад

Wow! This is excellent for creating an easy way for the boss to review reports before publishing! Saves a lot of time - not as much explanation required! Learning SO much from your channel and can’t thank you enough. The dynamic spilled arrays especially have been a great win at work👍

@excelisfun 3 года назад

I am glad to help. No doubt, showing formulas increases understanding significantly! I LOVE to hear that dynamic spilled arrays are helping at work. Most business do not have this feature yet, or do not know about it. You say your work is embracing it. If yes, that is awesome : ) : )

@ismailismaili0071 5 лет назад

God I can't stop watching your videos Mr. Mike you always give me all what i need

@tecwzrd 5 лет назад

Very helpful teaching tool! Thanks again for all the tips & tricks.

@excelisfun 5 лет назад

You aer welcome, Micah!!!! Thanks for the support : )

@johnborg6005 5 лет назад

Thanks for the nice morning Breakfast Mike :) :) Nice.....very Nice !!!

@excelisfun 5 лет назад

You are welcome for the breakfast!!! For a formula guy like yourself, this should be useful - even without the Spilled Array part...

@DIGITAL_COOKING 5 лет назад

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

@excelisfun 5 лет назад

About one week and the MSPTDA series will start back up! Thanks for your support : )

@DIGITAL_COOKING 5 лет назад

great! such good news:)

@excelisfun 5 лет назад

@@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 лет назад

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

@harishpaldhir5915 5 лет назад

HATTS OFF SIR PLEASE KEEP THIS WAY IN VERY ENERGETIC MANNER I AM GREAT FAN OF URS 😇😇😇

@excelisfun 5 лет назад

Thank you for being part of the Team, HARISH!!!! I will keep up the fun and energy : )

@nsanch0181 4 года назад

This is an excellent series Mike. Thank you so much for putting this together.

@excelisfun 4 года назад

You are welcome, N Sanch01!!!!

@chrism9037 5 лет назад

Very cool tip Mike, great job!!

@wayneedmondson1065 5 лет назад

Hi Mike.. nice.. love that TRANSPOSE to switch the orientation. Thumbs up!

@excelisfun 5 лет назад

Thanks for the Thumbs Up, Wayne!!! Glad you like the video : )

@spilledgraphics 4 года назад

Sweet stufff Mike !!!! amazing tip for documenting your model in Excel, thanks !!!!

@excelisfun 4 года назад

Sweet like candy : )

@khanabdussabur8604 5 лет назад

Wow! Awesome Mike, Thanks a lot.

@excelisfun 5 лет назад

You are welcome a lot! Thanks for your support, Khan!!!!

@BillSzysz1 5 лет назад

Great....as always!!! :-))

@excelisfun 5 лет назад

Thanks, O Poet of Array and Power Query : )

@johnborg6005 5 лет назад

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 5 лет назад

@@johnborg6005 , Bill Szysz = World Poet : )

@BillSzysz1 5 лет назад

@@excelisfun , @John Borg I am at most rural "poet" ;-)))

@excelisfun 5 лет назад

@@BillSzysz1 @John Borg, Poet that is Royal and Rural : )

@AnkitVerma-cn3px 5 лет назад

You are god of excel

@excelisfun 5 лет назад

Just a guy having fun with Excel : ) Thanks for your support, Ankit!!!

@nimrodzik1 5 лет назад

Simlpe, useful and fun. Thanks :)

@excelisfun 5 лет назад

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

@barttitulaerexcelbart9400 5 лет назад

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 5 лет назад

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.

@mohamedchakroun4973 5 лет назад

Nice trick from a briluant teacher :-)

@excelisfun 5 лет назад

Thanks, Mohamed!!!!!

@SyedMuzammilMahasanShahi 5 лет назад

Thanks Mike.

@excelisfun 5 лет назад

You are welcome, most EXCELlent Syed : )

@gentle2005phir 5 лет назад

It's great and very useful.

@excelisfun 5 лет назад

Thanks for the support, Gentle Raj!!! Glad it is useful : )

@dipkchopra 5 лет назад

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 5 лет назад

Unfortunately, a poll will not matter, Microsoft says it will never happen. They said that under no circumstance will any version other than Office 365 get the new Excel Calculation Engine. They did not say why, but I suspect that it is because it is the actual engine and not just a feature that has been fundamentally changed.

@excelisfun 5 лет назад

Also, I am not an employee of Microsoft and I am not that smart and they do not listen to me. I have no influence with Microsoft at all. I just make fun videos that try to teach efficient ways to use Excel : )

@dipkchopra 5 лет назад

hmmm.... get it. On the query part - Is there a solution to the query that I put up there?

@TSSC 5 лет назад

Deepak Chopra: Dynamic arrays is a welcome addition, but don’t forget other dynamic options such as PowerQuery. You can append queries, and even “stack” (i.e. load to) cells below an existing query result and have the top one not overwriting the bottom one when it expands.

@GeertDelmulle 5 лет назад

Deepak Chopra a couple of videos ago (it was video 1528 if I recall correctly) we’ve learned to append dynamic arrays. So as an exercise you can do this yourself (provided you’ve got the new calc engine).

@rostuff123 5 лет назад

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

@pmsocho 5 лет назад

Thumbs up!

@excelisfun 5 лет назад

Thanks, pmsocho!!!!! Go Team : )

@sevagbarsoumian516 5 лет назад

yes useful Thanks Mike

@excelisfun 5 лет назад

You are welcome, Sevag!!!! Thanks as always for your support : ) : )

@imkuldeep 5 лет назад

Why is Ctrl+Shift+Enter not used? Sorry I could not get it from video...

@excelisfun 5 лет назад

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: ru-vid.com/group/PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx

@nboisen 5 лет назад

How would you do this without the new calculation engine? For example, Transpose will not work without "spill"...

@excelisfun 5 лет назад

Same formula, you just have to highlight all the cells in advanced and enter formula with Ctrl + Shift + Enter.

@excelisfun 5 лет назад

Did you like the video? I hope : )

@nboisen 5 лет назад

@@excelisfun Yes, of course!

@nboisen 5 лет назад

@@excelisfunI had used "Ctrl+Shift+Enter" but couldn't get transpose to work initially. Now it's fine! Thanks!

@kartickchakraborty7948 5 лет назад

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.

@richardhay645 5 лет назад

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 5 лет назад

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-7jJMDGZpjSk.html

@richardhay645 5 лет назад

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 5 лет назад

@@richardhay645 , 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.

@richardhay645 5 лет назад

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 5 лет назад

@@richardhay645 , You are welcome! Thanks for the support, Richard, which helps me to keep going : )