I like the way that Excel 2007 handles conditional formatting for
overlapping ranges and such, but sometimes I have to save files
in .xls format. Invariably I get a warning that the conditional
formatting will not render correctly in older versions of Excel.
Is there a version of "Paste Values" for
conditional formats that
applies the formatting directly to the cells? It would not longer keep
up with changes, but for presentation purposes it would work just
For example, I have a table of about 2200 rows by 50 columns. Going
down a particular column, most of the cells are zero, and the nonzero
values tend to occur in clumps. Using number formatting 0.0_);(0.0);;@
I blanked out the zero values. Using conditional formatting, I was
able to highlight the first cell of a cluster one color, the middle
cells a second color, and the last cell (before going back to zero) a
third color. I also have some conditional formatting that marks
certain rows, and lots of other stuff going on to help with data
visualization. In all there are 8 conditional formats, and it looks
just the way I want it to look.
I'd like to be able to save this as an Excel 2003 file and keep the
formatting. I know that Excel 2003 can't handle all these conditions,
but I would like a way of just applying the formatting-as-displayed to
the cells before saving. Something analogous to how "Paste Values"
will give you the value-as-displayed without the underlying formula.