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

In this video learn about Spilled Dynamic Arrays & Conditional Formatting.
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)

[Al-Ahdal]

You are an excel instructor to the world. Proud to be your student sir.

[nadermounir]

I faced the same issue with conditional formatting. I am glad I solved it like u did before I watched your video. Thanks to u Mike, cuz of u I am now able to find excel solutions.

[wayneedmondson]

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!

[hniali]

You are the best, simple and easy to understand
Thanks a lot

[amit]

Good experiment with dynamic range will make us ready for practical use in daily working

[narendrarathore]

You are my great teacher, i like you sir👍

[anthonyverdin]

Great video and you definitely answered my question that I didn't know I had. I am currently working on a project that I want to conditional format a spilled range. Take care.

[harishpaldhir]

AMAZINGLY GOOD SIR

I ALWAYS WAIT FOR YOUR NEW VIDEOS LIKE THIS ONE 😇😇😇

[HusseinKorish]

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

[GeertDelmulle]

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.

[FabioGambaro]

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!

[ismailismaili]

magic is happening hhhh thank you Mr. Mike

[richardhay]

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.

[BillSzysz]

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 :-)))

[Ali.Mostafa]

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

[masterof]

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?

[vida]

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.

[spilledgraphics]

Hi Mike awesomee stuff as always!, question has there been any update about this? Conditional formatting & Dynamic Arrays.

[gvitullib]

Hi Mike. I tried F10<$F$5 but just selecting the range under the column (SR / Product) and for that range seems to work fine. If the range is expanded including the Sales column, then this column all is filled, but the SR/Product Column still working. I'm using Office 2021 LTSC.

[johnborg]

Thanks Mike :) So Conditional Formatting does not recognize spilled arrays?