While performing data analysis, you may need to find out particular values favorable to your needs. Highlighting them by changing cell color is one way to find this out from a big pool of data. In this article, I am going to show you how you can change cell color based on the value in Excel.
Change Cell Color Based on Value in Excel: 5 Ways
Here, I will be demonstrating 5 methods you can use to change cell color for particular conditions. For an easily digestible demonstration, I am going to use two different datasets- one with numerical values to classify them into ranges and one with text values.
1. Dynamically Change Cell Color Based on a Value
With this method, your cell color won’t be permanent. The color of the cell will change according to the rule you have set if you change the value in it. For this, I will be using conditional formatting.
To demonstrate the procedure, I will be using the following dataset.
I am going to divide the population numbers into 3 categories- above 20 million, below 5 million, and one in between.
- Select the range of cells you want to format.
- Then select Conditional Formatting under the Home Select New Rule from the drop-down list.
- In the New Formatting Rule box, select Format only cells that contain under Select a Rule Type. In the Rule Description choose the condition greater than or equal to and put in the value 20.
- Now, click on the Format to fill in the cell with colors. For this, go to the Fill tab in the Format Cells box and pick a background color. I am selecting red for this example.
- After that, click on OK on both the Format Cells and New Format Rule. The cells with values higher than 20 will turn red now.
- You can follow the same procedure from the start and put between as condition and 5 and 20 as values.
- Do the same for lower than or equal to 5 and you will have your cell color changed according to the values for the full range.
If you use this method and change the value of a red box to something under 5, it will change to green.
2. Change Cell Color Based on a Value of Another Cell
Let’s say, we want to change the colors of cells based on the value from another cell. If the value of that particular cell changes, the color of the main range will change accordingly.
For this, I have selected two values in cells F5 and F6 as a source to customize from.
- Select the range of cells you want to format.
- Then select Conditional Formatting under the Home tab. Select New Rule from the drop-down list.
- In the New Formatting Rule box, select Format only cells that contain under Select a Rule Type. In the Rule Description choose the condition to be greater than and put the following:
- Click on Format and in the Fill tab, select the background color.
- Click on OK on both Format Cells and the New Formula Rule. You will have your cells’ color changed.
- Repeat the same procedure for changing color for values less than 5 by referencing it from cell F6 by selecting less than as the condition and the following as the value in the New Formatting Rulebox.
You will have your whole range’s color changed based on the value of reference cells.
Now, if the values in either cell F5 or F6 change the colors from the range of cells C5:C16 will change accordingly.
3. Using Quick Formatting Option to Change Cell Color in Excel
For quicker, but less flexible formatting options, you can use quick formatting to change cell color based on value. Just follow these steps.
- Select the cell and hover your mouse cursor in the lower portion of the selected range. A Quick Analysis Toolbar Icon will appear.
- Click on it. In the Formatting tab, select Greater Than.
- In the Greater Than tab, select the value above which the cells within the range will change color. I have put it 20 here.
- You can also change the color. After that, click OK.
After that, you will have your cell colors changed which will be above 20.
You can also select the Color Scale option in the Formatting tab from the Quick Access Toolbar Icon to have a different range of colors for the column.
You will have a wide range of color cells based on the percentiles- red for the lowest, to white, to green for the highest.
4. Change Cell Color Permanently Based on a Value
There is also another method you can use to change colors in an Excel column by using Find and Replace. But this color change will not be dynamic and the color will remain the same if you change the value within the cell until you change it manually again.
For demonstration, I have selected the following dataset with recurring text values for easier demonstration. You can also do the same for numerical values.
Here, in this example, I have three values as positions and I will show you how to have three different colors for QB, LB, and WR.
- Select the range of cells you want to modify.
- In the Home tab, select Find & Select from the Editing section.
- Then, from the drop-down list, select Replace.
- In the Find and Replace box, put QB in the Find what box.
- Put QB in the Replace with box and change the format here.
- Select Replace All and you will have all the boxes with QB as the value will change to this color.
- Without closing the Find and Replace box you can keep changing colors for cells with different values.
- After changing colors for all three values, close the box. You will have your cells formatted in the range.
5. Change Cell Color Based on a Value Using Excel VBA
You can use Microsoft Visual Basic for Applications(VBA), for intermediate and advanced users. First of all, you need to enable the Developer tab to change cell color based on value.
If you have the Developer tab in your ribbon, you can follow these easy steps.
- First, name your range. To do that select your cells and go to the Formulas tab, and select Define Name under the Defined Names group.
- Name your range in the Edit Name I will be using “Positions” here. I suggest you name it the same if you want to copy the VBA code.
- Click on OK.
- Go to the Developers tab and select Visual Basic from there.
- In the VBA window select Insert, then Module.
- Now, in this new module, type the following code.
Dim cell_value As Range
Dim stat_value As String
Dim rng As Range
Set rng = Range("Position")
For Each cell_value In rng
stat_value = cell_value.Value
Select Case stat_value
cell_value.Interior.Color = RGB(0, 255, 0)
cell_value.Interior.Color = RGB(255, 255, 0)
cell_value.Interior.Color = RGB(255, 0, 0)
- Save your code.
- Now, go to Macros under the Developers tab.
- In the Macro box, select the code you have just created and run.
Your cell color will now change depending on the value you have in that range.
Download Practice Workbook
You can download the notebook used to demonstrate in this article with the examples included both with and without the colors changed from below.
These were the different methods you can use to change cell color based on value in Excel. Hope this guide was informative and helpful for you. If you have any questions or recommendations, feel free to let us know down below.
- How to Color Code Cells in Excel
- Excel Formula to Change Cell Color Based on Text
- How to Fill Color in Cell Using Formula in Excel
- How to Fill Cell with Color Based on Percentage in Excel
- Excel Formula to Color Cell If It Has Specific Value
- How to Change Text Color with Formula in Excel
- How to Apply Formula Based on Cell Color in Excel