Why Is Excel Not Custom Formatting Properly Sometimes

Question

I am trying to use conditional formatting to make a cell say HOLIDAY when it is a certain date or specific text. I have three different files with three different results happening. I can't figure out why this only worked with one of the files. Please help!!

First scenario: Not working Properly

Second Scenario: Also Not Working Properly

![](https://learn-attachment.microsoft.com/api/attachments/1b4f525f-bcd7-4db0-a700-2501d5b3b9e7?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/68efcbdf-bf96-49d5-810a-37a004bda17d?platform=QnA" rel="ugc nofollow">

Why would one excel file work properly and not the others?

Answer 1

Hi Michael. This should do what you are wanting to do in C6 and C7. However, it wouldn't work in C4 because that is where your Lookup Value for the Match function is located. You will need to relocate the Lookup Value in order to enter the formula in C4.

=IFERROR(IF(MATCH(C4,$A$4:$A$17,0),"Home",""),"")

In the example I drug the formula down from E4 so that it referred to Column C for each row to show the results with the varying date values in Column C. Your Conditional Formatting for Contains: "Home" will work now to format the cell's color, font, font color, size, bold or italic, etc. for the cells that result in the word "Home".

Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

Rich~M

Answer 2

Use conditional formatting to highlight information

Conditional formatting can help make patterns and trends in your data more apparent. To use it, you create rules that determine the format of cells based on their values, you will find more informations about about these possibilities in the upper link.

Answer 3

I am using conditional formatting. Those screen shots were of the three different results I'm getting from three sperate excel files when trying to use the custom number formatting inside of the conditional formatting. Here are more screen shots of the conditional formats I have set up. They are color coordinated with the first screen shots I posted.

First Scenario: Not Working Properly

Second Scenario (opened brand new blank spreadsheet to try): Not Working Properly

![](https://learn-attachment.microsoft.com/api/attachments/bbe62a33-774e-4ddb-ab31-62a7afe249be?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/bc426d2a-796f-4801-9863-9f994e2f5a17?platform=QnA" rel="ugc nofollow">![](https://learn-attachment.microsoft.com/api/attachments/bc426d2a-796f-4801-9863-9f994e2f5a17?platform=QnA

Answer 4

Hi Michael. I am an Excel user like you.

The reason that only the third example works is that you are using Number Formatting. You can only apply Number Formatting to a number.

So, in your first example cell C4 is empty so there is no number there to change the format on. The formatting formula for that cell is working because it is turning the cell yellow. Your second and third rules in that example have quotation marks around the formula so they won't work regardless until the rule is edited and the quotation marks are removed.

In the second example and the fourth and fifth rules from example 1, the cells that result in a TRUE value contain text which cannot have Number Formatting applied to them. The color fill shows that the rule is working correctly, but there is no number to format in those cells.

Your third example is working because the cell in which the Conditional Formatting is applied actually contains a number value (the date) to which a Number Format can be applied.

Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

Rich~M

Answer 5

Hi Rich,

Thank you very much for that explanation! So if I'm understanding correctly, even though conditional formatting allows you to create a rule for when specific text is in the cell, when trying to select the custom list, and adding "HOLIDAY" in that list, that is still only for a cell with a number in it and won't work for a cell with text in it or if it's blank?

I tried to work around this text vs number issue by using IF or IFS statements combined with the MATCH statement, because ultimately the cell I'd like to set this up in will be blank. However, those are giving me #N/A when the date doesn't match (second screenshot below) because the formula creates an error message due to a not available match. Is there another formula or way to have it show either the date when the value matches my list of specific not sequential dates or just a blank cell when it does not (instead of the error message)?

Here's an example. Cells C6 and C7 are the ones I'm looking at now. I don't want the #N/A when the date is not on the list. The formulas shown next to C6 and C7 are what I have in each of those cells.