Marking cells Good-Bad-Neutral

Sometimes when you are reviewing a file against a printed report, you may need to identify when cell values are correct, wrong or close.

Consider using the built-in Styles Good/Bad/Neutral on the Home ribbon tab – see below.

These can be quicker than using the usual fill colour icon.

Remember they may not be useful for colour blind readers if you are sharing with other people.

Don’t forget once applied if you want to apply the same format to another cell press the F4 function key.

Once formatted you can sort or filter by colour – see right click options below.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

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

10 thoughts on “Marking cells Good-Bad-Neutral

  1. Hello, I was wondering if there was a way to check if a cell had one of these conditions. If a cell is bad, I want to do a certain thing to it. How would this be done?

    • No you can’t check these formats in a formula. You can in a filter, but not a format. You need to use a cell entry and then a formula to identify the condition. Eg enter 0, 1 and 2 in cell next to the cell involved and then use an IF function to do a different thing.

      You could use a filter on the range for a colour and then use CTR + Enter to enter the same value in a range of visible cells next to the coloured cell.

    • Cell color is a cell property, so you can refer to it in a logical statement in VBA.

      However, unless you are picking “bad” results arbitrarily, it is usually easier to us a formula to check if the cell is “bad,” then use conditional formatting to color it that way. The “bad” format is actually the default style for conditional formatting checks.

      • Hi Jonathon
        Yes, if a conditional formatting rule can be used it is better to use that. But if you are checking a sheet against an external source it might not be possible. This post is about a manual solution that can then be used with the colour filter feature.
        Regards
        Neale

  2. Great tip!
    I want the ‘good/bad/neutral’ coloumn to display in the commands section inherently instead of having to go to Cell Styles>Good/Bad/Neutral>select option.

    Is there a way to do that?
    Cheers!

  3. Hi Neale,
    is there a way that you can add all the bad and the neutrals and as you change the bad to a neautral that it automatically add it to the neutrals?

    • Hi Bonita
      To add up the same colour you can use filters and SUBTOTAL.
      SUBTOTAL only adds up the visible cell in a filtered list. So if you filter the list by the neutral cell it will only add up those visible cells.
      If you change a colour you will need to re-apply the filter.
      There is no built-in function to add up based on a colour.
      Regards
      Neale