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

Excel Dynamic Arrays & Conditional Formatting for Spilled Arrays (Excel Magic Trick 1525)

166 ratings | 2551 views
Download Excel File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1524-1525.xlsx In this video learn about Spilled Dynamic Arrays & Conditional Formatting. 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 (72)
NoShadowOfDoubt (3 days ago)
It looks like I'm going to keep my highlighter next to me for an emergency? ... :-)
Vida (3 days ago)
Thanks for awesome series of Dynamic Arrays. I can't test them yet as don't have Office 365. I know array can't be converted to table, but I wonder what would happen if you create an empty table and try to enter dynamic array formula in a blank cell.
Wayne Edmondson (6 days ago)
Hi Mike.. love it. The trick I use now is to go to the first empty cell at the bottom of the list that I want to conditionally format and color that empty cell dark gray or something otherwise indicative of it being the end of the current list.. like a big noticeable underline. Then when applying the conditional format, I apply it to the range including the last colored empty cell and use your AND function as described in the video. If I need to expand the range because the data changes, I insert a row at the colored empty cell or insert and shift cells down from the empty colored cell which expands the range that is defined in the conditional format. Then, I copy the formula into the expanded range and the CF automatically applies to the new data. Also, in this way, I can always visualize the height of my CF range vs. having to manually check it. For the dynamic array formula example, I guess I would have to otherwise activate my empty cell with some kind of character (a space, a hyphen, the word END, etc.) to force a SPILL error which would then alert me to insert as needed to expand the range, otherwise, the SPILL behavior would blow through my empty cell and not carry the CF to the new data. Does that sound right? I can't test it yet.. maybe you can give it a try. Not to give away my age, but I've been using the include an extra cell at the end of the range trick since the days of LOTUS 123. Thank God I'm not so old that I said VisiCalc.. haha!! Thanks and Thumbs up!
Chris Mancinelli (7 days ago)
Great video Mike, keep them coming!
ExcelIsFun (7 days ago)
Okay! I will keep them coming - at least one each day this week : )
hany Aly (7 days ago)
You are the best , simple and easy to understand Thanks a lot
ExcelIsFun (7 days ago)
You are welcome, hany!!! Thanks for your kind words and for your support : )
rf05mjy (7 days ago)
Thank you... My PC at work (Office 365 Pro Plus) doesn't even have the option to join the insider, so I can only try it at home with my own personal office 365 Home version. I wonder what to do with the Print Area? If I would like to have a dynamic/automatic Print Area set, and the page to print is using this new dynamic arrays, not sure if the Print Area accepts the spill # sign?
ExcelIsFun (7 days ago)
I had terrible trouble trying to get Insider at my work also - I had the same version as you. It took over a month of back and forth with a MS representative before they allowed us to have Insider at work... But at Home, i was like you, I just bought Home Office 365 and did it that way.
HARISH PAL DHIR (7 days ago)
AMAZINGLY GOOD SIR I ALWAYS WAIT FOR YOUR NEW VIDEOS LIKE THIS ONE 😇😇😇
ExcelIsFun (7 days ago)
Yes, HARISH!!!!! I will have a new videos each day this week : ) Thanks for your support ion each video that you watch : )
Thanks Mike
ExcelIsFun (7 days ago)
You are welcome, Syed!!!! : )
Sanju (7 days ago)
Since I don't have this version... For your conditional formatting please try this Let formula be initial one I e (f10<300) But in conditional formatting select apply area as f10#
ExcelIsFun (7 days ago)
I tried that and it did not work. But good idea, Sanju!!!!
Sanju (7 days ago)
Sir I changed my 365 account to insider but still I'm not getting any updates.. how can I have this insider build
Sanju (6 days ago)
+ExcelIsFun got it sir... Thank you for such a great education and knowledge sharing
ExcelIsFun (7 days ago)
MS did not say when all Insiders will get it, for example I got it three weeks after it was released. But you should get it soon.
ExcelIsFun (7 days ago)
Once you get Insider, it may take a few eeeks. But it will be better than waiting until next year : ) Keep updating and it should come soon.
Jonathan Cooper (7 days ago)
In conditional formatting dialog there is a ‘refers to’. Can you tell it that the format applies to the spill cell with the # sign?
ExcelIsFun (7 days ago)
I tried that, but it did not work : (
Bill Szysz (7 days ago)
It is a pity that we can not use CF with # notation....but, to wipe away the tears, we can do some charts based on # (with a little trick) Thanks for video, Mike :-)))
ExcelIsFun (7 days ago)
+Bill Szysz You are welcome, Bill Szysz, for the "Trying To Explain Things In A Story That Makes Complicated Things Easier" : )
ExcelIsFun (7 days ago)
+Bill Szysz O... The Excel Workbook is not the correct one!?!?!? It had INDEX and OFFSET creating Dynamic Ranges, rather than the Spilled Array Syntax inside a Defined Name. I will update file when I get to work in an hours or so...
Bill Szysz (7 days ago)
+ExcelIsFun I've just download your file with charts... If i understand correctly your way then my way is a bit different. I will send you a file with explanations of my solution. Thank you very much for your commitment and effort in explaining many difficult things in a simple way :-))))
Sal A (7 days ago)
Yeah it is a downside for now, but it goes to show how much can be done in Excel and how much Microsoft needs to consider before they release new functions. In my humble opinion MS released dynamic arrays too early just like Power Query and Power Pivot. A thorough testing should be done before releases.
ExcelIsFun (7 days ago)
You are welcome! Thanks for the Support#, O Poet Bill Szysz! I have a video about Charts coming on Tues . But in that video i had to refer to the # Spilled Array with a Defined Name. Is that the trick you used, or did you use a different trick ?
Richard Hay (7 days ago)
The comments to date suggest two frustrations by the commenting viewers--with MS for not apparently making conditional formatting work the way we would like it and with the fact that most of us do not yet have dynamic arrays. I share these frustrations. I was not going to comment at all and take up Mike's valuable time on my unresearched speculations (due to not having the new tools) but the discussion on pivot tables (which all of us have) has brought my fingers to the keyboard. I'm not going to get involved in the semantics of the PT comments by the other viewers. I will simply share an experience. I have a pivot table--regular, no Data Model, No DAX--which I created about a year ago. This particular PT originally had about 35 rows. Each month I add data and refresh (thank you Ctl+Alt+F5). It has 4 columns. There are 3 subtotal rows and a grand total row scatted throughout the Table. Initially I applied conditional formatting by formula to the 3 subtotal rows and a different rule to the grand total row. Each month the PT grows. It now has 52 rows. Each time I refresh all of the formatted rows move do to the nature of the data. It is a fact that the conditional format repositions and follows correctly the moving subtotal and grand total rows AND the formatting has automatically followed the expansion of the PT. For example, Row 52 (grand total)now has the unique formatting that Row 35 (grand total)had in the original version of the Table. Conditional formatting has followed the expanding Table--this is what we want with dynamic arrays and it works in my regular "old fashioned" Pivot Table. My only thought which I TIMIDLY offer is: Would it have made a difference if the data set and the unique array were converted to tables in the EMT under discussion? Probably not, but just a shot in the dark.
ExcelIsFun (7 days ago)
​+Richard Hay , No, I think you can do that, but it is complicated. EMT 1528 shows how to append two items vertically. We can use CHOOSE to append columns on the right, something like CHOOSE({1,2},SORT(UNIQUE(columns)),SORT(UNIQUE(columns))). I also saw a solution from Bill Szysz that takes three columns and appends them vertically. In this download file for up and coming videos, there is an example on the sheet named "3UNIQUE Lists": https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1530-1531.xlsx
Richard Hay (7 days ago)
I take it that we cannot use a spilled array within a larger data set (such as using SEQUENCE to fill a column) and then later convert the data set to a Table. Nor can we, I suppose, build a data set around a spilled array (such as using sort and unique to get the first column of dimension table) and completing it by manually adding for example a price column and converting it to a Table. Or am I taking the "No Table" limitation too far in this interpretation?
ExcelIsFun (7 days ago)
+Richard Hay , You are welcome! Great to comment-connect : )
Richard Hay (7 days ago)
Thanks, Mike :). It was helpful to learn (and I wasn't expecting to learn this) that Dynamic Arrays cannot be converted to tables! Thus we cannot necessarily expect table features generally in Dynamic Arrays. Add Convert to Table to the MS to-do list!
ExcelIsFun (7 days ago)
It would be great if the Dynamic Array could expand naturally with Conditional Formatting, like PivotTables do, and like your helpful example illustrates. Since, this is still in Beta, maybe MS will add this feature in future updates. We will see. The Dynamic Array can't be converted to a Table. Thanks for your comments and thoughts, Richard! Always great to hear what you and other Teammates think!!!!
Mohamed Chakroun (7 days ago)
Nice mike thankssssss :-)
ExcelIsFun (7 days ago)
You are welcommmmmmmmmmmmmmmmmmmme, Mohamed!!!!!
Omer Katzavian (7 days ago)
As always, you show the magic that Excel has to offer. You are a wizard But why do not use this formula? =AND(F10<$F$5,F10<>"")
ExcelIsFun (7 days ago)
I could have. ISBLANK on looks for empty cells, <>"" looks for empty cells or Zero-Length Text Strings. Thank you for your comment and support : )
Fabio Gambaro (8 days ago)
I really hope that spilled ranges will work with conditional formatting, as the latter is a very useful feature, and combining with spilled array will increase its power!
ExcelIsFun (7 days ago)
I agree, Fabio : )
Hassan Al-Ahdal (8 days ago)
You are an excel instructor to the world. Proud to be your student sir.
ExcelIsFun (8 days ago)
Thank you, Hassan!!!! I am proud to part of our Online Excel Team and to try and teach you and others : )
K B (8 days ago)
Thanks, Mike, another great tip :)
ExcelIsFun (8 days ago)
You are welcome, K B!!!! Thanks for the support!!! : )
Ali Mostafa (8 days ago)
Thanks Mike.. Awesome point as usual! I don't have the right version to try it. but did you try F10# in: (Applies to ) field under conditional formatting rules manager... I'm just wondering if excel can accept this kind of reference in this field
ExcelIsFun (8 days ago)
I tried selected the Spilled Array and used F10#<$F$5 in the "Format values where this formula is true" text box in the Conditional Formatting dialog box, but it did not seem to work. After reading your comment, I tried selecting just F10 (cell that houses the formula) and used F10#<$F$5 in the "Format values where this formula is true" text box in the Conditional Formatting dialog box, but it did not seem to work. Thanks for the comment and support, Ali : )
Hussein Korish (8 days ago)
Thanks Mike ...I think "dynamic array formulas" should inherent some of the excel tables features .... since condtional formatting is working fine there .... i donn't know how ... may be as an icon besides the las row or column of spilled array to update conditional formating ... microsoft already used that technique before
Hussein Korish (8 days ago)
Yes ..Exactly
ExcelIsFun (8 days ago)
Maybe... Maybe someday they will make it like DAX formulas that can have Number Formatting attahced and the formula can be used over and over, or like a PivotTable where we can attach Number Formatting to a Field in the Values area for that one PivotTable. Thanks for the comment and support, Hussein : )
Narendra rathore (8 days ago)
You are my great teacher, i like you sir👍
ExcelIsFun (8 days ago)
I like you too : ) And I am glad that the videos help. Thanks for the support, Narendra, with your comments, Thumbs Ups and Sub : )
Geert Delmulle (8 days ago)
That’s how I would expect it to work —for now. Conditional formatting is a per-cel function, and for now, not Dynamic Array aware. If MS were to attach (conditional) formatting to the spilled array rather than the cell, then this would simplify. But DAF is very new, so things may evolve further in the future. One thing is clear to all of us: DAF (Dynamic Array Formulas) are here to stay — the rest of the ‘old’ Excel will adapt.
Geert Delmulle (7 days ago)
TSSC swe Thanks for the clarification. Maybe it won’t be so hard for MS to add the option “spilled cells” to the standard Conditional Formatting dialogue box. Checking that tick box should be the default IMO.
TSSC swe (8 days ago)
Geert, I think you misunderstood me. What I was referring to is that conditional formatting applied to, for instance, a column in a table will “spill” to new rows. And in a pivot table the conditional formatting dialogue box gives you several options for which parts the rule shall be applied (e.g. “Selected cells”, “All cells showing ‘Sum of ...’ values”). Selections other than “Selected cells” are dynamic in that the actual range that the rule applies to is redefined when changes in the source data causes the pivot table to change, e.g. “spill” new data into previously unused cells.
Geert Delmulle (8 days ago)
Mike, I understand your grief and you’re right. Furthermore, on that page you linked, it sure sounds like they’re willing to be more collaborative then they eventually are. Here in Western Europe we don’t even get to see or speak to MS experts, we just see ‘overly assertive’ sales people from MS. As a local government we are too small for MS to matter (only some 10 to 20 K users) — and they make that very clear to us. OTOH: I swear at work I don’t have that [Insider] button; the only thing I see is the fact that “updates are managed by your administrator”, which means we don’t get any updates until ages after the facts... I agree: it’s a shame that MVPs like yourself don’t have the ear of MS because you contribute so much to the community. BTW: thanks for the advise.
ExcelIsFun (8 days ago)
+Geert Delmulle What I know is this: all the Office 365 have Excel, and Excel Office 365 is what you want. So get whatever Office 365 you want. After you buy Office 365, then you sign up for Insider. You can sign up for Insider by opening Excel Office 365, then go to File, Then go to Account, Then click on button for Insider. Also, here is a link to Insider, check it out: https://products.office.com/en/office-insider?tab=Windows-Desktop If you don't get Office Insider, Microsoft said by early next year, all Office 365 in the world will get the New Calc Engine and Dynamic Arrays. P.S. I am "running inpatient" with MS also, because their marketing has been so bad for so many years; and given that they have so many versions and different offers, it just makes it a mess. Then on top of that, given that I am an MVP who has significantly contributed to the understanding of Array Formulas for many years, and given that I spoke with MS about trying to have videos ready to be posted on the day Microsoft released the New Excel Calculation Engine and Spilled Arrays, when the new Excel Engine and Dynamic Arrays were deployed, I did not get the correct version until 3 weeks after it was debuted. Although we all LOVE Excel and are honored to be using it in our work and play to efficiently create solutions for calculations and data analysis, it is no fun to be a customer of Microsoft.
Geert Delmulle (8 days ago)
BTW Mike, I’m running inpatient with the update scheme at work, so I’m considering upgrading my home computer from O2010 to O365. Will the O365 Personal license suffice? Or will I need the more expensive Home license? Will it put me on the insider edition? Can you advise? Thanks! (BTW: all this is your fault!... LOL! ;-)
Hassan Al-Ahdal (8 days ago)
Great.... please log or advise MS to correct it in their office release.
ExcelIsFun (8 days ago)
I do not think it is an error. As Geert says above, I think they intended it this way. If a Spilled Formula item spills into a cell with formatting , it gets the formatting, otherwise it will not. DAX formulas are the only formulas that can have Number Formatting attached to them. Although we could use the TEXT Function to add Number Formatting a a Number, it would convert the result to text...
Amit Tiwari (8 days ago)
Good experiment with dynamic range will make us ready for practical use in daily working
ExcelIsFun (8 days ago)
And... a piratical use is coming up in Video EMT 1530 : ) Thanks for the support, Amit!!!!
John Borg (8 days ago)
Thanks Mike :) So Conditional Formatting does not recognize spilled arrays?
John Borg (2 days ago)
Geert: Thanks for the info. I appreciate your comments :)
Geert Delmulle (8 days ago)
John Borg I would like to summarize it as follows: “Conditional Formatting does not follow/recognize Dynamic Arrays (from the mother-cell to the spilled/destination cels)”... “...But destination cells with Conditional Formatting will recognize spilled cells from Dynamic Arrays” I myself would prefer it if the Conditional Formatting WOULD follow the spilled cells. But it does not: it is a property of a cell (spilled or not), not a property of the Dynamic Array.
ExcelIsFun (8 days ago)
+John Borg : )
John Borg (8 days ago)
Thank for the info :) :)
ExcelIsFun (8 days ago)
The only formulas that can get formatting are DAX : )

Would you like to comment?

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