Excel Magic Trick 1465: Conditional Formatting Holiday Dates with Red Fill & Word "Holiday"

Download Files: excelisfun.net/files/EMT1465....
Entire page with all Excel Files for All Videos: excelisfun.net/
Learn about how to apply Conditional Formatting with a Logical Formula so that dates in a list that are holiday lists show up with the word “Holiday” and with red fill and white font. See the Excel Spreadsheet Function: the MATCH function. Also learn about how to use Custom Number Formatting to show words or text instead of the actual date.

[@LeilaGharani]

Love the custom conditional combo - also the use of match to get the true/false outcomes. Thank you :)

[@excelisfun]

You are welcome : )

[@JeremyMcMahan]

That "put text in the custom formatting box" trick was worth the price of admission all by itself! (Great trick(s) in this one. Thanks!!)

[@excelisfun]

Yes, that is a useful trick! Glad you like the video and thanks for the support : )

[@mehdihammadi6145]

Very useful, just applied it. Thank you for sharing. I appreciated the trick for hiding the date and replacing it with a fixed text.

[@irmanorminton8157]

Works like a CHARM!! Thank you!

[@excelisfun]

Glad it was a charm!

[@RobMichaels1]

Your "tricks" are great. I enjoy them every week. Thanks for sharing them!

[@excelisfun]

You are welcome!

[@cmosdriver]

DUDE!!!! THAT WAS FRIKN SUPERCALIFRAGILISTICEXPIALIDOCIOUS AWSUM!!! Man, don't EVER stop posting these videos! Pleez!!

[@excelisfun]

You got it!!!! : ) I won't stop posting if you won't stop supporting with Thumbs Up, comments and Subs : )

[@DougHExcel]

Excel-lent tip on conditional formatting!

[@chrism9037]

Awesome tip! You're the master

[@krn14242]

Thanks Mike. Great trick with the Number Formatting.

[@excelisfun]

Glad you like it, WRH!!!!

[@SyedMuzammilMahasanShahi]

EXCELlent video. Thanks Mike

[@takdanaicheepsaritdipong9381]

Nice content. I learn new trick of custom formatting and great shortcut keys love your vids :)

[@excelisfun]

Glad you like them : ) Thanks for your support!!!

[@pmsocho]

Awesome as always! Thumbs up!

[@excelisfun]

Thanks for the comment and Thumbs up : )

[@redhaakhund1271]

Really love your videos.. God bless you👍👍👍👍👍

[@excelisfun]

Glad you like them!

[@mohamedchakroun4973]

If it was possible, i put 1000 thumbs up for the custom formatting in conditional formatting wwawwww great job

[@karljolivet5991]

This trick is so cool. Thank you for showing me

[@excelisfun]

You are so welcome! Thanks for your support : )

[@johnborg5419]

Amazing Mike :) What's nice about it is that since it is Custom Formatting representing a number, the word "Holiday" is aligned to the right although it shows Text. Simply Beautiful!!!!

[@excelisfun]

Glad you like it : ) : )

[@defikpl]

great video!

[@excelisfun]

Glad you like it!

[@padmasirisrinarayana2293]

Nice explanation Thanks

[@h.1899]

Awesome learnt some new shortcuts and neat new trick :-)

[@excelisfun]

Glad it was awesome!!! Thanks for your support with your comment, Thumbs Up and Sub : )

[@yuvrajpatil55]

Nice..im always waiting for your new videos

[@excelisfun]

Glad you like them!

[@petevichy9068]

Thank you, Mike :-)

[@excelisfun]

You are welcome!!!!

[@raulnardone7062]

Great! Thumbs up!

[@excelisfun]

Glad you like it : ) Thanks for your support!!!

[@maheshprasad-yk8bh]

I got to know new trick..I always like video..👍

[@excelisfun]

Thanks for your support!!!!

[@palakodetibangarurayudu9686]

Nice one. Thank you

[@excelisfun]

You are welcome! Thanks for your support : )

[@barttitulaerexcelbart9400]

Thank you Mike, I especially liked the way you were so carefull in selecting the range to be formatted. I very often have to correct the formulas or applied range later. By the way: do you know the difference between the "APPLY" button and "OK" ??. I never figured out.

[@bagnon]

Apply allows you to see the change while keeping the menu open.

[@excelisfun]

As far as I know, Apply applies and leaves dialog box open and OK applies and closes dialog box. Where did you see Apply button in this video? I don't remember using it!?!?!

[@barttitulaerexcelbart9400]

That is correct Mike, you did not use it. But you always explain so well, also details. And I was just wondering. Bagdon is right.

[@entertainmentgalaxy971]

super duper trick .. thanks for sharing

[@excelisfun]

You are welcome for sharing!!! Thanks for the comment, Sub and Thumbs Up : )

[@stopdrinkingleftskoolaid6510]

Hello, I tried to apply the same formula and it works for just one column or one row. When I tried to apply it to several rows (4 rows) I kept getting a #N/A error. I have three separate criteria and the only one I cant get to work is the match function that has to look up a value in 4 rows. thanks

[@darlenemartinez7270]

Awesome!

[@excelisfun]

Glad it was awesome for you!!! Thanks for your support!!!

[@MohamedAlyCLAY]

I Like this Trick to use match in conditional formatting

[@excelisfun]

Glad you like it : )

[@aligh76]

great ... thanks

[@excelisfun]

You are welcome!

[@warchilduk6406]

thanks but i have a table with weekends and this rule is only applying in first row, can you help me to do formatting with weekend to all rows? thanks

[@stevereed5776]

Hi Mike, just what I needed. However, is there a better way to do this in Microsoft 365?

[@KaranKumar-vt6jr]

Best tutorials in youtube... Can anyone tell me what is mis reporting, I know excel well but I am hesitating in applying for the job as I don't know should I apply for mis position or a data entry operator? Can anyone tell me it would be great help for me

[@jaehyi2009]

Another great video! A question, though. Does the custom formatting work only with numbers or dates? When I tried it out with text values, it would not fill in the word "Holiday," but as soon as I switch to numbers (even the dates are numbers, right?), it works again. What gives?

[@excelisfun]

There are 4 arguments in Custom Number Formatting, each separated by semi-colons. The 4th is for text. This Custom Number Formatting: "Holiday";"Holiday";"Holiday";"Holiday" would show Holiday even for text. Thank you for your support.

[@jaehyi2009]

ExcelIsFun thank you! Learned something new again!

[@excelisfun]

Thanks as always for your comment, Thumbs Up and Sub : )

[@MySpreadsheetLab]

Automatic thumbs up!

[@excelisfun]

Thank you O Rad Kevin!!! : )

[@MySpreadsheetLab]

Custom Formatting inside of Conditional Formatting!! Awesome! I never realized that was even possible!!!

[@excelisfun]

Anything sis possible in Excel!!! : )

[@ScottPerley]

Awesome video, Mike! Could you do the same with Table names instead of row/column ranges? I can create the formula but it won't let me apply it as the formula for the conditional formatting... I made each set of data a table and used this formula in the Topics column: =MATCH([@Dates],Table2[Holidays],0)

[@excelisfun]

That is strange. I did not know that a table name in a formula would not work in a Function like MATCH if it was used in the Conditional Formatting dialog box!?!? If it does not work, just create the tables (to get the dynamic ranges) then type regular cell ranges and that should retain the dynamic ranges...

[@excelisfun]

Oaky, this is a known issue with table names in some dialog boxes... When I create the Excel Tables, then create the formula from scratch in the Conditional Formatting dialog box, the actual cell references are inserted by the dialog box and the Table Formula Nomenclature is not inserted. Then it works. But when I create the formula in the cells(like I did in the video) I create the formula: MATCH(Table2[@Dates],Holidays[Holidays],0) . then when I paste it in the dialog box, it does not work. So I would just use a formula like: =MATCH(B4,$G$4:$G$9,0), or you could use the know fix for Table Formula Nomenclature (I actually did a video on this about 5 years ago) and that is to use the INDIRECT Function like this: =MATCH(B4,INDIRECT("Holidays[Holidays]"),0), but that is a real hassel...

[@excelisfun]

I just added a new sheet to the downloadable file that shows the silly INDIRECT Function formula...

[@FabioGambaro]

Another option is to define a name corresponding to the table column and use the name in the formula - this will expand automatically as the table rows increase and it works in conditional formatting...

[@ScottPerley]

Thanks Mike! Cool stuff. I've never used Indirect before.

[@jsbibra]

how do i send across a worksheet for a solution -- i have dates related to leave taken from date 1 apr 2017 to 27 apr 2017-- so my calendar should have some conditional formatting

[@15071982]

Super

[@excelisfun]

Glad you like it : )

[@rpsingh4553]

Good morning sir, Sir I have a problem with conditional formatting. The problem is that I have a list of products in column "A" , opening quantity in column "b" and sold quality in column "c". Now issue is how to use conditional formatting if I enter sold quantity more than opening for any particular product excel highlight that cell. Please help me

[@johnpoe3791]

This video is much more complex than what you're asking. If you got the Home tab / Styles Group / Conditional formatting you will see a "greater than" selection. The formula will initially have "$"s. This locks it to the one cell. Remove the dollar signs. Complete the transaction. You can change the values in this first row to confirm it works. Copy/Paste-special formatting to the other cells in this column. This should give you what you want. To get more information on conditional formatting, connect to the internet. Highlight the conditional formatting command and hit function key F1. This will open the help concerning conditional formattingl

[@rpsingh4553]

Thanks u lot sir for this assistant

[@pounro]

I am assuming you use COUNTIFS too?

[@excelisfun]

What do you mean? Do you mean you could use COUNTIFS in place of MATCH? If that is what you meant, then yes : ) There are always many ways to have fun in Excel !!!

[@pounro]

Yes, also similarly to a comment below, thanks for the "text in custom format" tip - that's amazing

[@excelisfun]

Cool! : ) Glad that you liked it!!

[@yuvrajpatil55]

Please make one video that how to calculate with multiple range and criteria... please solve below example ...if in the data table.. date (1 jan to 31 jan )has horizontal in first row (B1:AE1)and product name has vertical in first column (a2:a50) .. product sale has in range of B2:AE31..this table has recorded.. In the sheet2 .. Product name has in first column„ first date has in b column, second date has in C column, so how to calculated (A column)product sale between B column and C column date range in D column......this is very critical example but i have solved my way...please share your way

[@stevejez]

YS, this will work =SUM(INDEX(Sheet1!$A$1:$AE$51,MATCH(Sheet2!A2,Sheet1!$A$1:$A$51,0),MATCH(Sheet2!B2,Sheet1!$A$1:$AE$1,0)):INDEX(Sheet1!$A$1:$AE$51,MATCH(Sheet2!A2,Sheet1!$A$1:$A$51,0),MATCH(Sheet2!C2,Sheet1!$A$1:$AE$1,0))) Hope it helps

[@excelisfun]

Try posting question to this great Excel question site: mrexcel.com/forum

[@markpodesta4605]

It would be more complicated if there were more options needed.

[@excelisfun]

That is true.

[@planxlsm]

4:06