The limitations of conditional formatting

There is no way to perform analysis based on formatting. For example, you cannot count red cells (at least not easily). CONDITION → FORMAT is the end of the road... you cannot do any analysis on FORMAT.

If you really need to do analysis based on formatting, try this:

  1. Create a range that performs the conditional analysis and returns a result.
  2. Base the conditional formatting on that conditional analysis range.

For example, a cell can use the IF() function to decide if a value is high or low, and can return a value "RED" or "GREEN". The conditional format can then apply green formatting when the value is "GREEN" and red formatting when the value is "RED."

If you need to count red cells, you simply count cells in the condition analysis range that are "RED."

  • If you want to be fancy, you can hide the conditional range, and it will appear as if you're actually counting color formats.

The idea is that instead of CONDITION ? FORMAT, at which point you cannot do any analysis, CONDITION ? CONTENT ? (both FORMAT and ANALYSIS). So the formatting is still determined by a condition, but the CONTENT can be analyzed appropriately.

To shade every other row: Press CTRL+A then choose Format ? Conditional Formatting and click Formula Is then enter the formula:

=MOD(ROW(),2)=1

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish