The article will provide you with three different methods on how to use color code cells in Excel. Coloring a cell helps us to highlight the data and we may need to find out a specific value or a range of values sometimes while working on an Excel sheet. We can identify a range, name, or duplicates by color-coding the corresponding cells. So it’s important to know how to code an Excel cell with color and let’s move on to the following section of the article to see the use of this application.
In the dataset, you will see the obtained marks of some students and their corresponding grades.
We will be coding some ranges of this worksheet with colors later in this article.
1. Using Conditional Formatting to Color Code Cells in Excel
Suppose, you want to code the Marks column with colors. The marks above 80 will be filled with Green, marks between 60 and 80 will be filled with Blue, and the rest of the data will be filled with Red. Let’s go through the procedure below to get a better understanding.
Steps:
- First, select the range of Marks and then go to Home >> Conditional Formatting >> Highlight Cell Rules >> Greater Than.
- After that, a dialog box will show up. Insert 80 in the Format cells that are GREATER THAN section and then select Custom Format.
- Next, select Fill >> Fill Color from the Format Cells Here I chose Green.
- Later, click OK.
- In addition, you may format the font and font style. Let’s choose the Italic Font style and white as the font color.
- After that, click OK. It will return you to the dialog box. Click OK.
- Thereafter, you will see the Marks greater than 80 are filled with Green color with Italic Font style.
- To code the marks between 60 and 80, open the Between dialog box from the Highlight Cells Rules option.
- After that, insert the values like the following picture.
- Next, the Format Cells window will show up again. Choose formatting options as we did previously. This time, I chose Blue color as the cell background.
- Similarly, you can format the rest of the cells using the Less Than command from the Highlight Cells Rules option.
- Insert the condition and formatting as we did previously. As we want to format the rest of the data, we should set the Less Than value to 61. I chose Red color as the cell background.
If you change the value of a cell to another that belongs to a different range, the color will change accordingly. Here I changed Rooney’s number to 55 and the background color became Red.
Thus you can color code cells in Excel by Conditional Formatting.
2. Color Coding Cells Containing Excel Formulas
You can also identify a cell if it contains formula by color coding. In the dataset, I used some handy formulas in the Grade column. Let’s have a look.
=IF(AND(C6<=100,C6>=80),"A+","")
The formula uses combined IF and AND functions to show the grade. I’ll show you how to highlight the cells that contain formulas in the later description.
Steps:
- First, select the range D5:D14 and then go to Home >> Conditional Formatting >> New Rule.
- After that, the New Formatting Rule window will appear in front of you. Select the Use a formula to determine which cells to format option and type the following formula in the Formula box:
=ISFORMULA(D5)
- Thereafter, click on Format.
The formula uses the ISFORMULA function to address a formatting rule based on the formula.
- Next, choose a background color from the Fill option and click OK.
- Later, the command will take you to the New Formatting Rule window with a preview of how your formatted cells will look like. Just click OK.
- After that, you will see the cells that contain formulas are filled with Blue.
You can notice that, if you apply a formula in another cell of that range, the cell also becomes Blue.
Thus you can color code cells based on formulas.
3. Applying Color Code to Cells Based on Text
We can also color code cells based on the text they contain. Say we want to highlight the grades A+. Let’s go through the process below to see how we can do this.
Steps:
- First, select the Grade range and open the New Formatting Rule using the procedure of Method 2.
- Next, type the following formula in the Formula section of the New Formatting Rule.
=$D5=”A+”
- Click on the Format button and choose a background color from the Format Cells window using the steps of Method 1 or 2.
After that, cells that contain the Grade A+ are now filled with colors.
Thus you can color code cells in Excel based on texts.
Download Practice Workbook
Conclusion
In the end, we can consider that you achieved some basic ideas on how to color code cells in Excel after reading this article. If you have any better suggestions or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.
Related Articles
- Uses of CELL Color A1 in Excel
- VBA to Change Cell Color Based on Value in Excel
- How to Change Cell Color Based on a Value in Excel
- 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
