Excel: Format cells based on content

As a union steward, one of the many things I do on a regular basis is evaluate key indicators of the company financials. I do this by plotting a few numbers from the accounting reports into Excel, and then run them through some standardised analysis to identify the performance of key indicators. These indicators are evaluated on a five-point scale ranging from “unsatisfactory” to “very good”, and I have set up a total of seven rules using Conditional Formatting to allow me to read the indicators at a glance. Here’s how it’s set up:

Screenshot of the Conditional Formatting Rules Manager, showing seven rules.
  • The first rule ensures that the header cell is not formatted
  • The second rule ensures that blank cells are not formatted
  • The third rule applies to anything equal to or above the threshold value (in this case 2), and applies a dark green fill color
  • The fourth, fifth, and sixth rules apply to specified values and apply light green, yellow, or orange fill colors, respectively
  • The seventh, and final, rule applies to anything below the threshold value (in this case 0,5) and applies a red fill color

It takes a moment to set up, but once done you don’t need to do anything to it unless the threshold values change (and these threshold values have been the same for decades, so I think I’m pretty safe).






By posting a comment, you consent to our collecting the information you enter. See privacy policy for more information.

This site uses Akismet to reduce spam. Learn how your comment data is processed.