conditional formattingmicrosoft excel
I have a table of 3 columns as follows:
People test1 test2
A 90 80
B 70 60
C 50 40
D 30 50
I want to apply a conditional formatting rule on the table: given a person, if his score of test2 is higher than the score of test1, then we make the background colour of his name red.
Does anyone know if we could do this in Excel? It seems to me that the conditional formatting always applies to one cell based on its own value.
To add to mischab1's answer: Using Excel 2007, getting to the formula is very simple, but also easy to miss:
1) Click "Manage Rules"
2) Click "Edit Rule"
3) In the rule, you probably have :
Format only cells with Cell Value =$A$1
Delete the $ in front of the column # - so that only the column value (A) is absolute:
Format only cells with Cell Value =$A1
4) Select OK
5) Now use format painter to apply that rule to the rest of the sheet
This is one of the more basic things with conditional formatting. Click Conditional Formatting on the Home tab, Highlight Cells Rules, Greater Than and either select a cell to compare it to or type the cell reference in. Repeat for less than.
It's a little more complicated to apply such a rule across a row/block.
I'll be using the first row as an example.
Select a block. The 'active cell' is the white cell in the selection. This is important later.
Click Conditional Formatting on the Home tab, Highlight Cells Rules, Greater Than
Select the cell you want to compare the active cell to. The other highlighted cells will automagically be compared to the cell shifted according to the relative position to the active cell. In this example, the selected cell is one column to the left of the active cell, so each cell in your selection will be compared to the cell one column to the left of the cell to be formatted. (I'm bad at explaining that, feel free to comment asking for clarification.)
Select the desired colour.
Make sure the formula has no dollar ($) signs. They mean it is an absolute reference, which means all cells in the selection will be compared to the specified cell, not the relative one "one column left". We don't want that, we want a relative reference instead, so remove the dollar signs.
Press OK.
Go back to step 2, but use Less Than this time.
There is no need to set a colour for "equal value", since default is blank. If you wish to set a different colour, there's an Equal To option too.
End result:
Best Answer