Another video about Conditional Formatting and Dynamic Spilled Arrays: ua-cam.com/video/iH3_kt3oKao/v-deo.html From 2 years ago, where I show some of my thought processes about attaching formatting to dynamic spilled arrays..
We regulars to this channel knew this for some time now. I can only refer to previous conversations where we agreed that it would be nice that apart from the formula (and thus the results) also the formatting would automatically spill from the first cell. That would be a good thing, it still didn’t happen and we can still only hope... In the mean time, we do exactly this trick and it works just fine. :-)
PS: just finished creating an EV-calculator for the local photo club. No big deal: just some PQ-M, quite a few Excel tables, some (column) formulas, a bit of XLOOKUP, depending drop-down lists, conditional formatting,... The usual suspects. A lot of Names (ranges, dynamic formulas) and one erroneous formula on Wikipedia. Added some prediction of good values for camerasettings in order to get a good exposure. Allowed for modifiers as well: ND-filters, Polar, exposure compensation. BTW: did it as bit of a competition with a fellow club member: that was the first time I saw someone ‘simulate’ a VLOOKUP function (Excel 2007!) using a nested IF construct for quite a large table. He was quite proud of his crazy complex formula. May have been a little disappointed when in Teams I assisted him writing a short VLOOKUP that did the same thing. :-)
@@GeertDelmulle It sounds like you are just plain having fun with Excel when building this photo club project. Which of course all advanced users have on a regular basis.
Here's the key: Each cell must get a TRUE for Format or FALSE for no format, then the formula you create, is as if, it were in the cell. From that: BOOM, it should be more intuitive : )
I LOVE to hear that! No doubt: if you can master that Excel Basics (not really basics, I just call it that, it is really solid foundation for everything in Excel), you can do anything! Congrats on the new job, teegala!!!!
Without a doubt they should think about adding a formatting element to spilled arrays. But I think it is unlikely, since all of history has required formulas to be manually formatted. But we can hope the future is different than the historical past.
Great video Mike, but is there a way to do conditional formatting dynamically, thereby not having to highlight cells prior to entering a formula in the conditional format rule?
Actually there is: select just the cell that will spill (let's say E5) and add the formatting. Then edit the formatting and in Applies To, type =$E$5#. It will automatically spill and will keep spilling the formatting as you add more stuff
Thanks Mike. I used this in my wages distribution extracting the names below. All names extracted with conditional formating i do it in this way. I even use yr green color too :) :) :) Great video !!!!
Thank You very much Mike :), I faced the problem how to make format follow the spilled array formula yestreday at my work, your solution is simple and beautiful :)
I love dynamic arrays and use conditional formatting to add a table look. I just wish conditional formatting would follow dynamic array itself instead of us highlighting more cells.
@@excelisfun select just the cell that will spill (let's say E5) and add the formatting. Then edit the formatting and in Applies To, type =$E$5#. It will automatically spill and will keep spilling the formatting as you add more stuff
I'm looking for this trick but it seems when you add first new data in table, it works. But when I tried 3rd one, it doesn't work. I'm using Excel 2021 version.
Yep - Microsoft has 100% dropped the ball. There is a major bug that prevents conditional formatting to accurately format Spilled arrays. I have been tearing my hair out trying to figure out how to get it to work, but no luck. They really need to fix the bug for conditional formatting and allows spilled arrays in charts. I have communicated these issues to Microsoft, but as is typical, they did not reply. It is so frustrating... : (
I would not, unless you will fill the whole column up. Conditional formatting is volatile and updates often, so it is better to highlight just the amount that is more than you will use. It takes some planning up front, but it is worth it.
Sir I am little bit confused, can you help me ? Actually your UA-cam channel have lots of contents and playlist too, so I can't understand where to start. I have basic knowledge about excel and working on financial's content so kindly guide me . Thankyou in advance 🙏
Excel Basics is THE best place to start. I call it basics, but this class sets the foundation for everything else: ua-cam.com/play/PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k.html Then for you, I have a complete 110 video finance playlist with ALL the important formulas and functions for cash flow analysis: ua-cam.com/play/PL90E1F26C7B85E78F.html The finance class is 10 years old, but the formulas and functions, like PMT, FV, PV, RATE, NPER, NPV, XNPV, IRR, XIRR and more are all still the same : )
@@JonathanExcels I guess you could, but... you have to use the same "highlight more cells than you will ever have" trick for the defined name. At least as far as I know.
off topic question. is there a way to create incel dropping suggestion that shrinks down as you type, like a google search. suggestion comes from another workbook. i axplane in book1 i have dinamic table with column "item" with list of items in book2 i have dinamic table with column "item" both books are in onedrive in same folder, but book2 changes its location after ´save as´. but stil in onedrive so, when in book2 from its new location you are typing item "abc" you want to have a dropping down suggestionlist from all items that have "abc" in its name in book1. and this list gets narrower and narrower as you keep typing. thanks for any help. if there is any souse where i have to look for, will appretiate any info.
Another video about Conditional Formatting and Dynamic Spilled Arrays: ua-cam.com/video/iH3_kt3oKao/v-deo.html
From 2 years ago, where I show some of my thought processes about attaching formatting to dynamic spilled arrays..
I've been using Excel for 28 years & still learning new tricks! Thanks
Thanks is what is so cool about Excel, always new things to learn : ) You are welcome, 28 year Excel pro Don!!!!
The best Teacher of Excel, HANDS DOWN. Thank you Mike ! Go TEAM !
Thank you Teammate From Down South : ) : ) : ) Go Team!!!
Just what I needed. Good weekend!
Glad it helps!
We regulars to this channel knew this for some time now.
I can only refer to previous conversations where we agreed that it would be nice that apart from the formula (and thus the results) also the formatting would automatically spill from the first cell.
That would be a good thing, it still didn’t happen and we can still only hope...
In the mean time, we do exactly this trick and it works just fine. :-)
PS: just finished creating an EV-calculator for the local photo club.
No big deal: just some PQ-M, quite a few Excel tables, some (column) formulas, a bit of XLOOKUP, depending drop-down lists, conditional formatting,... The usual suspects. A lot of Names (ranges, dynamic formulas) and one erroneous formula on Wikipedia.
Added some prediction of good values for camerasettings in order to get a good exposure. Allowed for modifiers as well: ND-filters, Polar, exposure compensation.
BTW: did it as bit of a competition with a fellow club member:
that was the first time I saw someone ‘simulate’ a VLOOKUP function (Excel 2007!) using a nested IF construct for quite a large table.
He was quite proud of his crazy complex formula. May have been a little disappointed when in Teams I assisted him writing a short VLOOKUP that did the same thing. :-)
And it works just fine. I mean when we build any formula report, we have to do formatting anyway : )
@@GeertDelmulle It sounds like you are just plain having fun with Excel when building this photo club project. Which of course all advanced users have on a regular basis.
@@GeertDelmulle That is always a happy moment, when you show other dramatically easier methods : )
Love the intro, Dude!!!!!!
Love the way it all comes together. Thanks Mike for this EXCELlent video.
All coming together is fun, Fellow teacher Syed MM : ) : )
Thanks Mike for the great video! I have never seen the NOT(ISBLANK used like that- so simple, but so useful!
It the new ISEMPTY : )
Great tricks Mike. Conditional formatting is powerful but I don't find it to be very intuitive. Thanks for sharing and have a great weekend...
Here's the key: Each cell must get a TRUE for Format or FALSE for no format, then the formula you create, is as if, it were in the cell. From that: BOOM, it should be more intuitive : )
This trick is what many have been waiting for. Great stuff
Yes, the wait id over lol
I always wait for those bonus tricks ... Thanks Mike
You are welcome for the tricks and bonus tricks : )
Hello Mike your MS excel basics playlist has helped me to get a job . Thank you and keep up the good work!
I LOVE to hear that! No doubt: if you can master that Excel Basics (not really basics, I just call it that, it is really solid foundation for everything in Excel), you can do anything! Congrats on the new job, teegala!!!!
@@excelisfun Thank You Mike
Boom!Super Cool Conditional Formatting Tricks Happy Days...Thank You Mike :)
Boom You Boom Are Boom Welcome, darryl : ) : )
Thanks a lot for every information you provide for us
It's my pleasure!!
Love your new Intro and Extro! Nice! Of course your content is nice too!
Teammate Geert came up with the intros : ) Go Team!!!!
Nice one Mike! You never fail to surprise and make us happy! 😊👍👌
Glad to help you have fun with Excel : )
This open the way to think for Microsoft to work on spilled formulas
Without a doubt they should think about adding a formatting element to spilled arrays. But I think it is unlikely, since all of history has required formulas to be manually formatted. But we can hope the future is different than the historical past.
Great video Mike, but is there a way to do conditional formatting dynamically, thereby not having to highlight cells prior to entering a formula in the conditional format rule?
Not that I know of.
Actually there is: select just the cell that will spill (let's say E5) and add the formatting. Then edit the formatting and in Applies To, type =$E$5#. It will automatically spill and will keep spilling the formatting as you add more stuff
Thanks Mike. I used this in my wages distribution extracting the names below. All names extracted with conditional formating i do it in this way. I even use yr green color too :) :) :)
Great video !!!!
Green is good! Thanks, Formula Guy : ) : )
The most Awaited trick
I am glad that the wait is over, ANAND!!!
Thank You very much Mike :), I faced the problem how to make format follow the spilled array formula yestreday at my work, your solution is simple and beautiful :)
Yes!!! LOVE to hear that it helps : ) Good to see you in the comments, nimrodzik! It has been a while : )
Excellent trick
Glad you liked the EXCELlent trick : )
Thumbs up as always!
Thanks, pmsocho : ) : )
Thank you Mike! You know your stuff.
Yes, Mark, I know some stuff so I can teach and helps others to have more fun with Excel ; )
Very nice Mike. Thank you for sharing this video
You are welcome for the share, Solex!!!
Thank you for this video Mike!
You are welcome, Ivan!!!!
Thank very much Mike, that was fantastic 😀
You are welcome, Gilberto!!!!
Wow.most useful.
Glad it is useful, Finance Excel Teammate : )
Outstanding, thanks Mike!
Thanks, Chris : ) : )
Liked and Thumbs Up Sirji 👍
Thanks, Mahesh!!!!
Great Mike! ❤️
Glad it is great, usman!!!
I love dynamic arrays and use conditional formatting to add a table look. I just wish conditional formatting would follow dynamic array itself instead of us highlighting more cells.
Me too. We all do. I wonder if Microsoft will give it to us some day. I hope!
@@excelisfun select just the cell that will spill (let's say E5) and add the formatting. Then edit the formatting and in Applies To, type =$E$5#. It will automatically spill and will keep spilling the formatting as you add more stuff
Thanks Mike 😃😃
You are welcome, Mayank !!!!!
Thanks for the great trick. Also didn’t know that "" works on blank cells!
Empty cells and zero length text strings : )
Thanks Mike
You are welcome, Anthony!!!
superb 👍🏻👍🏻👍🏻👍🏻👍🏻
Thanks for the massive thumbs ups, Vijay : ) : ) : ) : ) : )
Great trick. Can we somehow make it so that the rows are banded in the same way as the official excel table?
If top left cell is D4, then =MOD(ROWS(D$4:D4),2) should be the conditional format formula : )
@@excelisfun thanks. And for odd rows just subtract one, right?
@@Defaktorix Odd is: =MOD(ROWS(D$4:D4),2)=1
I'm looking for this trick but it seems when you add first new data in table, it works. But when I tried 3rd one, it doesn't work. I'm using Excel 2021 version.
Yep - Microsoft has 100% dropped the ball. There is a major bug that prevents conditional formatting to accurately format Spilled arrays. I have been tearing my hair out trying to figure out how to get it to work, but no luck. They really need to fix the bug for conditional formatting and allows spilled arrays in charts. I have communicated these issues to Microsoft, but as is typical, they did not reply. It is so frustrating... : (
Can we Highlight whole column in Conditional formatting???
I would not, unless you will fill the whole column up. Conditional formatting is volatile and updates often, so it is better to highlight just the amount that is more than you will use. It takes some planning up front, but it is worth it.
@@excelisfun Ok that sounds logical.
@@simfinso858 : )
Thumbs up!
Thanks, Malina : )
Great video!!!!
You know what?...you are right!!!! , now will have the ISEMPTY for good
ISEMPTY(a)=LAMBDA(a,ISBLANK(a)) 😂😂😂
For good, or forever, whichever is longer lol
Thank you sir
You are welcome, Rahul!!!
Sir I am little bit confused, can you help me ? Actually your UA-cam channel have lots of contents and playlist too, so I can't understand where to start. I have basic knowledge about excel and working on financial's content so kindly guide me . Thankyou in advance 🙏
Excel Basics is THE best place to start. I call it basics, but this class sets the foundation for everything else: ua-cam.com/play/PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k.html
Then for you, I have a complete 110 video finance playlist with ALL the important formulas and functions for cash flow analysis: ua-cam.com/play/PL90E1F26C7B85E78F.html
The finance class is 10 years old, but the formulas and functions, like PMT, FV, PV, RATE, NPER, NPV, XNPV, IRR, XIRR and more are all still the same : )
@@excelisfun thanks sir 🙏
@@robinghosh3326 You are welcome!
I’ve never tried it but perhaps you could use a dynamic range name?
I am not sure how it helps, though? Any thoughts?
@@excelisfun sorry, I guess I wasn’t clear. I wondered if you could use a dynamic range name to define the cells that need conditional formatting.
@@JonathanExcels I guess you could, but... you have to use the same "highlight more cells than you will ever have" trick for the defined name. At least as far as I know.
off topic question.
is there a way to create incel dropping suggestion that shrinks down as you type, like a google search. suggestion comes from another workbook. i axplane
in book1 i have dinamic table with column "item" with list of items
in book2 i have dinamic table with column "item"
both books are in onedrive in same folder, but book2 changes its location after ´save as´. but stil in onedrive
so, when in book2 from its new location you are typing item "abc" you want to have a dropping down suggestionlist from all items that have "abc" in its name in book1. and this list gets narrower and narrower as you keep typing.
thanks for any help. if there is any souse where i have to look for, will appretiate any info.
Not sure. For back and forth dialog to get Excel solutions try: mrexcel.com/forum