How to Color Code Cells in Excel (3 Efficient Methods)

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.

how to color code cells in excel

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.

Using Conditional Formatting to Color Code Cells

  • 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.

Using Conditional Formatting to Color Code Cells

  • 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

Using Conditional Formatting to Color Code Cells

  • 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.


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+","")

Color Coding Cells Containing Formulas

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.

Color Coding Cells Containing Formulas

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.

Color Code Cells in Excel Based on Text

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.

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo