I need to do the following in Excel 2010

Colour a column based on another columns value

For example,

Status      Balance
Green       0
Red         25
Red         60
Green       0

The 'Balance' is populated using SUM(). I could do it for a single cell, ie., for the first row. How can I set the rules for the rest of records ?

  • Please provide some more detail about how the data is arranged in the worksheet; "Status Balance Green 0 Red 25 Red 60 Green 0" doesn't explain it very well. Is the condition based on the SUM of the column or just a single cell value? – CharlieRB Aug 8 '12 at 11:33
  • if you are going to change the values in future, you should write a macro for this purpose – tumchaaditya Aug 10 '12 at 11:26

(I have only a german version available but I will try to explain everything with screenshots)

  1. Mark all Cells that should get the colour
  2. Activate "Start" tab
  3. Click on that button:bedingte Formatierung
  4. Click on "New Rule"
  5. Select the last option and enter your formula: enter formula and set up formatting
  6. set up your formatting and klick OK.

My example shows how to colour all selected cells green when A1 contains "true" without quotes. You can change that formula to your needs. Make sure your formula starts with "=".

  • Dear, I could do it for single cell. Can you please read the last sentense of my question? I need to set the same rule for all records of a column :) – Rauf Aug 8 '12 at 12:32
  • 2
    Then select ALL cells that should have that rule and then create it. That's why my first step is called "Mark all cells that should get the colour". I've tested that, it works. – wullxz Aug 8 '12 at 14:08
  • Very unclear answer. – Pedro77 Oct 16 '14 at 13:26
  • The upvotes on my answer suggest it's not unclear. Maybe you'd want to tell me what you think is unclear? I'd be happy to improve my answer. – wullxz Oct 16 '14 at 13:36

If you want to base the color off another cell's value, then you should use an absolute reference or the INDIRECT formula.

Normally, we might want to color the cell itself (e.g. if the value is greater than 20). We could use a formula =A1>20. This is a relative reference: if you applied it to several columns, then it would treat each cell as A1 and do the comparison.

Suppose that we want to color an entire row if a value in Column B is greater than 20. In that case, we'd select the appropriate columns to apply the conditional formatting and use the formula =$B1>20. The $B means it only looks at the value in Column B. The 1 is still relative, so it will look at each row's value in Column B.

If you wanted to do something a bit more complicated, like color a row based off a value two rows higher, then you'll need to use INDIRECT and ROW in your formula.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.