Excel Cell Color: Add, Edit, Use & Remove

Sometimes we need to color our Excel cell to make the dataset unique, highlighted, beautiful, and easy to visualize. In this article, we will know how to color excel cells with multiple criteria, explanations, and examples.


Practice Workbook

Download the following workbook and exercise.


7 Quick Ways to Use Excel Cell Color

1. Use of Fill Color to Add Color in Excel Cell

To add or change the background cell color, we can use the Fill Color option. Assuming we have a dataset (B4:E10) of customers’ payment history. We are going to add color to the cells of the payment type.

Use of Fill Color to Add Color in Excel Cell

STEPS:

  • First, select the cells (D5:D10) where we want to add color.
  • Next, go to the Home tab and select the Fill color dropdown.

Use of Fill Color to Add Color in Excel Cell

  • Now, in the dropdown, we can see all the colors. Pick any color from the Theme Colors or Standard Colors. We can also get our customized color by clicking the More Colors option.

  • After selecting the dataset looks like below.

NOTE: For applying the recently used color, we can simply click on the Fill Color icon.


2. Use of Fill Color to Remove Color in Excel Cell

Often we need to remove the background color of a cell. Here we have a dataset of employees’ payment history and its payment type is highlighted with color. We are going to remove the color of each cell.

Use of Fill Color to Remove Color in Excel Cell

STEPS:

  • In the beginning, select the cells with color (D5:D10).
  • Now go to Home > Fill color dropdown.

Use of Fill Color to Remove Color in Excel Cell

  • Then click on the No Fill option.

  • Finally, the colors of the cells are removed.


3. Alternate Excel Cell Color

Alternating excel cell color is important to make a dataset organized. It’s very useful for analyzing a complex dataset. Let’s say we have a dataset (B4:E10). We are going to alternate the cell of the rows with the help of Color Banding & Conditional Formatting.

Alternate Excel Cell Color

STEPS:

  • First, select the dataset (B5:E10).
  • Go to Home > Conditional Formatting > New Rule.

Alternate Excel Cell Color

  • In the New Formatting Rule window, select ‘Use a formula to determine which cells to format’.
  • Now in the Format values box, type the Color Banded Rows formula which will highlight every alternate row with color. The formula is:
=MOD(ROW(),2)=0
  • Click on the Format.

Alternate Excel Cell Color

  • A Format Cells window pops up.
  • After that, go to the Fill option and select any background color.
  • Then press OK.

  • We can see the color preview in the Preview box.
  • Select OK.

  • Finally, it’s done. We can see the dataset like the below one.


4. Change Background Color of Empty Cells in Excel

To change the background color of the empty cells dynamically, we are going to use the Conditional Formatting option. Here we have a dataset. We are going to indicate the empty cells with color.

Change Background Color of Empty Cells in Excel

STEPS:

  • Select the cell range B4:E10.
  • Now go to Home > Conditional Formatting > New Rule.

Change Background Color of Empty Cells in Excel

  • In the New Formatting Rule window, select the ‘Format only cells that contain’ option.
  • Input Blank from the ‘Format only cells with’ dropdown.
  • Then click on Format.

Change Background Color of Empty Cells in Excel

  • From the Format Cells window, go to the Fill option to select the color we want.
  • Now click OK.

 

  • Again click OK to see the result.


5. Dynamically Change Excel Cell’s Color Based on Value

We can dynamically change the excel cell’s background color based on a specific value. Here from the below dataset (B4:E10), we are going to indicate the values which are less than 400.

Dynamically Change Excel Cell’s Color Based on Value

STEPS:

  • Select the range B5:E10B.
  • Then go to Home > Conditional Formatting > New Rule.
  • The New Formatting Rule window pops up.
  • Now select ‘Format only cells that contain’.
  • In the ‘Format only cells with’ box, we can see multiple dropdown options.
  • Select Cell Value, less than, 400 from there.
  • Click on Format.

Dynamically Change Excel Cell’s Color Based on Value

  • After that, in the Format Cells window, go to the Fill section.
  • Select the Background Color.
  • Click OK.

  • Again click OK.
  • At last, we can see all the required cells are highlighted.


6. Excel Color Change When the Cell Is Clicked with VBA

We can change Excel cell color by double-clicking or right-clicking on it. For that, we have input a VBA code. Assuming we have a dataset.

Excel Color Change When the Cell Is Clicked with VBA

STEPS:

  • First, right-click on the sheet from the sheet tab.
  • Select View code.

Excel Color Change When the Cell Is Clicked with VBA

  • A VBA Module window pop up.
  • Now type the code:
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.color = vbYellow
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.color = vbGreen
End Sub

  • Then press Alt+Q and go to the worksheet.
  • Right-click or double-click on it and see the result.


7. Count Excel Cell Color

Sometimes in a big dataset, we have to count the colored cells. By using the Filter option with the SUBTOTAL function, we can easily sort this out. Here we have a dataset with colored cells.

Count Excel Cell Color

STEPS:

  • Select any cell (E12) below the dataset.
  • Now type the formula:
=SUBTOTAL(102,E4:E10)

Count Excel Cell Color

  • Hit Enter to see the result.

Count Excel Cell Color

NOTE: The SUBTOTAL function will count the visible cells in range E4:E10. It will use 102 as the first argument.

  • Next, select the headers of the dataset.
  • Go to the Data tab.
  • From Sort & Filter option, select Filter.

  • We can see that the filter is applied to the headers.
  • Click on any filter dropdown of the header.
  • Select ‘Filter by Color’.
  • Then click on the color based on which we want to count.

  • Finally, we can see the filtered cells with their quantity in Cell E12.


Conclusion

By using these methods, we can easily color cells in Excel with multiple criteria. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.


Related Readings

How to Use Format Painter in Excel for Multiple Cells (6 Quick Ways)

Count Specific Characters in Excel Cell (4 Quick Ways)

Excel Showing Formula Instead of Result (8 Reasons)

How to Insert Picture into Excel Cell (3 Methods)

Excel INDEX-MATCH If Cell Contains Text

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. Here I will post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo