Excel Dynamic Arrays & Conditional Formatting for Spilled Arrays (Excel Magic Trick 1525)
Вставка
- Опубліковано 12 вер 2024
- Download Excel File: excelisfun.net...
In this video learn about Spilled Dynamic Arrays & Conditional Formatting.
Entire page with all Excel Files for All Videos: excelisfun.net...
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)
You are an excel instructor to the world. Proud to be your student sir.
Thank you, Hassan!!!! I am proud to part of our Online Excel Team and to try and teach you and others : )
Good experiment with dynamic range will make us ready for practical use in daily working
And... a piratical use is coming up in Video EMT 1530 : ) Thanks for the support, Amit!!!!
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.
You are the best , simple and easy to understand
Thanks a lot
You are welcome, hany!!! Thanks for your kind words and for your support : )
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
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 : )
Yes ..Exactly
You are my great teacher, i like you sir👍
I like you too : ) And I am glad that the videos help. Thanks for the support, Narendra, with your comments, Thumbs Ups and Sub : )
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.
Thanks, Mike, another great tip :)
You are welcome, K B!!!! Thanks for the support!!! : )
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 Well, ”per-cell” is not entirely true … think about tables (ListObject) and pivot tables.
TSSC swe I’m talking about the Conditional Formatting functionality (and the functionality we can access through its interface), not functionality that is part of another complex function such as Pivot Tables, as you mentioned. I totally get that you can configure formatting-related properties in pivot tables, entire rows or even arrays (data field) at a time. Heck, you can even inherit the data format from the Data Model without configuring anything in the subsequent pivot table... But that is not what this exercise is all about: this is about the Conditional Formatting function,... since no formatting functionalty inherent to Dynamic Arrays exists. Yet. :-)
MS would be wise following channels like this one, because good. Inspiration comes from people like Mike who test these new features and find ways to improve them further.
Geert, As I said below: I think Microsoft 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... Thanks as always Geert, for being part of our awesome Online Excel team : )
Well Mike, I would like to see it changed. As [TSSC swe] notes: some multi-cell objects like Pivot Tables can be custom formatted (like the data field - you know). So it is possible - in an object like that. So, IMHO it would make more sense that the entire spilled array would inherit the Conditional Formatting from the parent cell, not the destination/spill cells. But in the mean time we’ll do it like you demonstrated.
And now I’m gonna read the other comments. ;-)
@@GeertDelmulle Yes I agree - it would be VERY cool if we could add formatting to the parent cell !!!!
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!
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
I tried selected the Spilled Array and used F10#
AMAZINGLY GOOD SIR
I ALWAYS WAIT FOR YOUR NEW VIDEOS LIKE THIS ONE 😇😇😇
Yes, HARISH!!!!! I will have a new videos each day this week : ) Thanks for your support ion each video that you watch : )
Great video Mike, keep them coming!
Okay! I will keep them coming - at least one each day this week : )
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!
I agree, Fabio : )
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 :-)))
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 ?
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 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 :-))))
@@BillSzysz1 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...
@@BillSzysz1 You are welcome, Bill Szysz, for the "Trying To Explain Things In A Story That Makes Complicated Things Easier" : )
Thanks Mike
You are welcome, Syed!!!! : )
Great video, thanks so much!
You are welcome!
@@excelisfun I tried to hide/block formula in array, but only applies to first cell. Is there a way to hide formula seen in the complete array?
Thanks Mike :) So Conditional Formatting does not recognize spilled arrays?
Conditional Formatting recognizes what is spilled in the cell, so if the cell has the formatting, the spilled item gets the formatting. But if the Spilled Array spills into a cell that does not have formatting, it will not be formatted. You are welcome for the video, John : ) Thanks for the support!
The only formulas that can get formatting are DAX : )
Thank for the info :) :)
@@johnborg6005 : )
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.
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.
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!!!!
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!
@@richardhay645 , You are welcome! Great to comment-connect : )
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?
@@richardhay645 , 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": people.highline.edu/mgirvin/UA-camExcelIsFun/EMT1530-1531.xlsx
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.
Hi Mike awesomee stuff as always!, question has there been any update about this? Conditional formatting & Dynamic Arrays.
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?
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.
Hi Mike. I tried F10
Since I don't have this version...
For your conditional formatting please try this
Let formula be initial one I e (f10
I tried that and it did not work. But good idea, Sanju!!!!
Great video Mike, could you do one on losing values when an Array spills? For example if a user accidentally drags across a spilled range the Spill error appears (expected behaviour), but I loose the ability to refer to the spilled range via the # cell reference too? From my testing this impacts other formulas and references?
magic is happening hhhh thank you Mr. Mike
Ugh. You got my hopes up. I absolutley love dynamic arrays but without ease of dynamically formatting I have to limit when and how I use it until formatting becomes truly dynamic. Selecting a finite number of rows in an environment that is ever growing simply does not work. Sadly I have modified pivot tables to look like tables simply because of the way it handles conditional formatting. If tables would do the same thing then all would be good in my Excel world.
PS. Please consider changing Sort of! to Not really! :) Thanks for your videos though.
I have no power over Microsoft. I hope they will add an ability.
Perfect 👍
: ) : )
Great.... please log or advise MS to correct it in their office release.
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...
As always, you show the magic that Excel has to offer.
You are a wizard
But why do not use this formula?
=AND(F10
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 : )
In conditional formatting dialog there is a ‘refers to’. Can you tell it that the format applies to the spill cell with the # sign?
I tried that, but it did not work : (
Nice mike thankssssss :-)
You are welcommmmmmmmmmmmmmmmmmmme, Mohamed!!!!!
Sir I changed my 365 account to insider but still I'm not getting any updates.. how can I have this insider build
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.
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 got it sir...
Thank you for such a great education and knowledge sharing
It looks like I'm going to keep my highlighter next to me for an emergency? ... :-)
It doesn't help anybody to spend 2/3 of the time telling us ways that don't work.
It does imo, excel is problem solving, he's teaching you ways to think (or avoid?) while using Excel.