Tips

Excel 2013 Conditional Formatting

How to change a row colour based on a number in a single cell

Say, you have a table of your company orders like this:
Source data - a table of the company's orders

You may want to shade the rows in different colours based on the cell value in the Qty. column to see the most important orders at a glance. This can be easily done using Excel Conditional Formatting.

1. Start with selecting the cells the background colour of which you want to change.

2. Create a new formatting rule by clicking Conditional Formatting > New Rule… on the Home tab.
On the Home tab, click Conditional Formatting > New Rule...

3. In the “New Formatting Rule” dialogue window that opens, choose the option “Use a formula to determine which cells to format” and enter the following formula in the “Format values where this formula is true” field: =$C2>4
Open the 'New Formatting Rule' dialog and enter the needed formula.

Instead of C2, you enter a cell that contains the value you want to check in your table and put the number you need instead of 4. And naturally, you can use the less (<) or equality (=) sign so that your formulas will read =$C2<4 and =$C2=4, respectively.

Also, pay attention to the dollar sign before the cell’s address, you need to use it to keep the column letter the same when the formula gets copied across the row. Actually, it is what makes the trick and applies formatting to the whole row based on a value in a given cell.

4. Click the “Format…” button and switch to Fill tab to choose the background colour. If the default colours do not suffice, click the “More Colours…” button to pick the one to your liking, and then click OK twice.
Click the 'More Colors...' button to choose the color to your liking.

You can also use any other formatting options, such as the font colour or cells border on the other tabs of the Format Cells dialogue.

4. The preview of your formatting rule will look similar to this:
The preview of your formatting rule

5. If this is how you wanted it and you are happy with the colour, click OK to see your new formatting in effect.

Now, if the value in the Qty. column is greater than 4, the entire rows in your Excel table will turn blue.
The background color of entire rows is changed based on value in the specified cell.

As you can see, changing the row’s colour based on a number in a single cell is pretty easy in Excel. Further on, you will find more formula examples and a couple of tips for more complex scenarios.