How to change a cell's color based on value in Excel dynamically
The background color will change dependent on the cell's value.
Task: You have a table or range of data, and you want to change the background color of cells based on cell values. Also, you want the color to change dynamically reflecting the data changes.
Solution: You need to use Excel conditional formatting to highlight the values greater than X, less than Y or between X and Y.
Suppose you have a list of gasoline prices in different states and you want the prices greater than USD 3.7 to be of the color red and equal to or less than USD 3.45 to be of the color green.
Note: The screenshots for this example were captured in Excel 2010, however the buttons, dialogs and settings are the same or nearly the same in Excel 2016 and Excel 2013.
Okay, here is what you do step-by-step:
- Select the table or range where you want to change the background color of cells. In this example, we've selected $B$2:$H$10 (the column names and the first column listing the state names are excluded from the selection).
- Navigate to the Home tab, Styles group, and choose Conditional Formatting > New Rule….
- In the New Formatting Rule dialog box, select "Format only cells that contain" under "Select a Rule Type" box in the upper part of the dialog box.
- In the lower part of the dialog box under "Format Only Cells with section", set the rule conditions. We choose to format only cells with a Cell Value - greater than - 3.7, as you can see in the screenshot below.
Then click the Format… button to choose what background color to apply when the above condition is met.
- In the Format Cells dialog box, switch to the Fill tab and select the color of your choice, the reddish color in our case, and click OK.
- Now you are back to the New Formatting Rule window and the preview of your format changes is displayed in the Preview box. If everything is Okay, click the OK button.
The result of your formatting will look similar to this:
Since we need to apply one more condition, i.e. change the background of cells with values equal to or less than 3.45 to the green color, click the New Rule button again and repeat steps 3 - 6 setting the required condition. Here is the Preview of our second conditional formatting rule:
When you are done, click the OK button. What you have now is a nicely formatted table that lets you see the highest and lowest gas prices across different states at a glance. Lucky they are in Texas :)
Tip: You can use the same method to change the font color based on the cell's value. To do this, simply switch to the Font tab in the Format Cells dialog box that we discussed in step 5 and choose your preferred font color.