Sometimes you want to highlight a row in a table depending on say, a category in the B-column.
- Add a new conditional formatting rule to the table
- Select “Use a formula to determine which cells to format”
- Enter the formula =IF(INDEX($B:$B;ROW();1)=”Misc”;TRUE;FALSE)
Note: You might have to use a colon instead of a semi-colon as a separator, depending on your Excel version.
- Click Format and select your color of choice
What the formula does is use the INDEX-function (the arguments are: array, row, column) to lookup the value on the current row number (as returned by the ROW-function) in the B-column (using number 1, because the array is just 1 column wide). The IF-function just makes sure TRUE or FALSE are returned so the rule knows when to apply the color.
Then you just add more rules to the table for the different categories you need to color.