Meraz Al Nahian
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.
Download Practice Workbook
3 Magnificent Ways to Color Code Cells in Excel
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
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
- 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
- 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 becomes Red.
Thus you can color code cells in Excel by Conditional Formatting.
Similar Readings
- How to Fix Formula in Excel (9 Easy Methods)
- Remove Last Modified By in Excel (3 Ways)
- How to Move Data from Row to Column in Excel (4 Easy Ways)
- [Fixed!] CTRL C Not Working in Excel
- If a Value Lies Between Two Numbers Then Return Expected Output in Excel
2. Color Coding Cells Containing 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
=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. Color Code Cells in Excel 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.
Practice Section
Here, I’m giving you the dataset of this article so that you can practice these methods on your own.
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. For more queries, kindly visit our website ExcelDemy.
Related Articles
- How to Check If Value Is Between 10 and 20 in Excel
- Show All Text in an Excel Cell (2 Easy Ways)
- How to Mark Workbook as Final in Excel (with Easy Steps)
- Search Text in Multiple Excel Files (2 Easy Ways)
- How to Show Coordinates in Excel Graph (2 Easy Ways)
- [Fixed!] Excel Links Not Working Unless Source Workbook Is Open
- How to Set Intervals on Excel Charts (2 Suitable Examples)
