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.
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.
- First, select the cells (D5:D10) where we want to add color.
- Next, go to the Home tab and select the Fill color dropdown.
- 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.
- In the beginning, select the cells with color (D5:D10).
- Now go to Home > Fill color dropdown.
- 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.
- First, select the dataset (B5:E10).
- Go to Home > Conditional Formatting > New Rule.
- 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:
- Click on the Format.
- 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.
- Select the cell range B4:E10.
- Now go to Home > Conditional Formatting > New Rule.
- 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.
- 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.
- 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.
- 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.
- First, right-click on the sheet from the sheet tab.
- Select View code.
- 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.
- Select any cell (E12) below the dataset.
- Now type the formula:
- Hit Enter to see the result.
➥ 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.
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.