Forum Discussion
Conditional formatting in Excel
I have a workbook saved for members of our racing team (each person on a row) to signify their availability for each week's racing during the season (columns). I've set it up with a data validation (for yes/no/no response) and once they make their choice, I'd like the conditional formatting to kick in and make it easier to see how many are in for a given week, so I was thinking a YES entry would -> green fill/dark green text, NO-> red fill/dark red text, no response would stay with no fill. We have 20 people/rows, 22 weeks of racing/columns, and I want to try and do something once that would apply to all. When I went to conditional formatting, I couldn't see how I set multiple options or specify the range of cells to which I want it to apply. Can someone help? Thanks in advance.
Go to Conditional Formatting in the "Home" tab. Select New Rule. Then, select "Use a formula to determine which cells to format". Once there, you should type, =A1="Yes" and then go to "Format" in the lower right corner and select which options you would like to apply when "Yes" is entered in cell A1.
Do the same procedure for "No" and use =A1="" for the option with the cell being blank.
Once you have it all, make sure to use the formula as =A1="Yes" and not =$A$1="Yes". That is, use relative references, not absolute references. Once you have it all set for one cell and you make sure it works, then go to "Format Painter" on the left side of the "Home" tab and then drag the cell to cover all weeks. That will "move" your conditional formatting to all desired cells.
Go to Conditional Formatting in the "Home" tab. Select New Rule. Then, select "Use a formula to determine which cells to format". Once there, you should type, =A1="Yes" and then go to "Format" in the lower right corner and select which options you would like to apply when "Yes" is entered in cell A1.
Do the same procedure for "No" and use =A1="" for the option with the cell being blank.
Once you have it all, make sure to use the formula as =A1="Yes" and not =$A$1="Yes". That is, use relative references, not absolute references. Once you have it all set for one cell and you make sure it works, then go to "Format Painter" on the left side of the "Home" tab and then drag the cell to cover all weeks. That will "move" your conditional formatting to all desired cells.
- Very cool, thanks! The only thing I did differently was after setting the three rules on a single cell, I went to 'Manage Rules' under Conditional Formatting (because I wanted to make sure I'd deleted my earlier failures) and saw that I could specify a range of cells, so I just edited there and it applied the rule to the entire team/season without using the format painter.
