Conditional formatting - keep original conditional formatting but mask number as text

Question

I have a set of rates I have applied conditional formatting to in excel 2016, see screenshot below:

On top of this, I also have a conditional format that formats blank cells as gray.

While these are rates, additionally I would like to mask rates in cases where their denominator is below 10 and replace the displayed value with "I" to indicate insufficient data, but still keep the original conditional formatted color displayed. Is there an efficient way to do this in excel 2016?

Not really. Since conditional formatting is base on the value in the cell, if the value changes, so does the formatting. There could be some elaborate workaround but it really won't be nice — CallumDA
that was my gut feeling. i was thinking i might just have to manually do everything in Illustrator, how would you do it? — rachel.passer
Could you use a second condition to format the text the same colour as the cell? It does leave the value in the cell, but it "looks" blank — cybernetic.nomad
i can hide values by formatting them as ;;; (custom format), but i wanted to take it 1 step further by formatting them as "I" to serve as a mask. — rachel.passer

rachel.passer rachel.passer · Accepted Answer · 2018-04-03T20:25:05

There is a simple workaround I discovered with the help of my coworkers.

Take the table with conditional formatting in Excel
Copy it all
Open up a blank Microsoft Word document
Paste the Excel table into Microsoft Word
Select the whole table in Word and copy it
Paste the table from Word back into Excel

The formatting might look a little clunky at first, but my conditional formatting was then locked in as the format and no longer conditional. This worked for my purposes; I was then able to apply additional conditional formatting on top of this table without issue.

Conditional formatting - keep original conditional formatting but mask number as text

1 Answers