How to Filter by Color Using Conditional Formatting in Excel (3 Steps)

We have the Marks of a school’s 10th-grade students, and we want to filter the Total Marks based on 2 criteria. They are as follows.

• Total > 299 → Format cell with a Green fill.
• Total < 300→ Format cell with a Red fill.

Step 1: Apply Conditional Formatting

• Select the cells of the Total column as marked in the following image.
• Go to the Home tab from Ribbon.
• Choose the Conditional Formatting option from the Styles group.
• Select the Highlight Cells Rules option from the drop-down.

• Choose the Greater Than option, as shown in the image below.

As a result, the Greater Than dialog box will open.

• In the Greater Than dialog box, type 299 in the marked field.
• Click on the drop-down icon and choose the Custom Format option from the drop-down.

The Format Cells dialog box will be visible on your worksheet.

• In the Format Cells dialog box, go to the Fill tab.
• Choose your preferred color.
• Click on OK.

• As a result, you will be redirected to the Greater Than dialog box.
• Click OK.

You will have the cells formatted with your preferred color, as shown in the following picture.

• Select the cells of the Total column as marked in the following image.
• Go to the Home tab from Ribbon.
• Select the Conditional Formatting option from the Styles group.
• Choose the Highlight Cells Rules option from the drop-down.
• Click on the Less Than option.

As a result, you will have the Less Than dialog box on your worksheet.

• In the Less Than dialog box, type 300 in the marked field.
• Click on the drop-down icon and choose the Custom Format option from the drop-down.

• In the Format Cells dialog box, go to the Fill tab.
• Choose your preferred color and click on OK.

• You will be redirected to the Less Than dialog box.
• Click on OK.

You will have the Total column formatted with your preferred colors.

Step 2: Enable the Filter Option

• Select your data along with the headers.
• Go to the Data tab from Ribbon.
• Choose the Filter option from the Sort & Filter group.

As a result, filtering options will be enabled, as marked in the following image.

Note: You can also use the keyboard shortcut CTRL + SHIFT + L after selecting the data to enable the Filter option.

Step 3: Filter by Color

• Click on the drop-down icon, as marked in the following picture.
• Select the Filter by Color option from the drop-down.
• Choose the color you want. Here, we chose the Green color.

You will see the following output on your worksheet.

You can also choose Red, and you will get the following output on your worksheet, as shown in the following image.

How to Use Advanced Filter Feature to Filter by Color in Excel

Advanced Filter is a handy feature of Excel. Using It, we can filter out data based on provided criteria within the same worksheet. Let’s say we want to filter our dataset based on the following two criteria.

• Students who got more than 299 Total Marks.
• Students who got less than 300 Total Marks.

Steps:

• Create a new column named Total and type in >299 in the cell below it.

Note: Here, the name of the newly created column must match the dataset’s Total column.

• Go to the Data tab from Ribbon.
• Choose the Advanced option from the Sort & Filter group.

As a result, the Advanced Filter dialog box will open on your worksheet.

• In the Advanced Filter dialog box, select the Copy to another location option under the Action field.
• Click on the List range field and select the dataset and the headers marked in the following picture.

• Click on the Criteria range field and choose the newly created column, as shown in the image below.
• Click on the Copy to field and select cell B18. This is where your Filtered Data will be displayed.
• Click on OK.

You will have the list of students with more than 299 Total Marks.

You can also change the condition to <300. By following the same steps, you will get the following output for the new condition, as demonstrated in the following picture.

How to Solve If Filter by Color Option Is Not Showing in Excel

Steps:

• Select the dataset along with the headers and go to the Data tab from Ribbon.
• Choose the Filter option from the Sort & Filter group.

As a result, the option to filter will be added, as shown in the image below.

• Click on the drop-down icon beside the Total column.

You can see that the Filter by Color option is Grey. You can’t select it. Let’s solve this issue now.

• Use the steps mentioned in Step 01 of the 1st method to apply conditional formatting to the dataset and get the following output.

• Click on the drop-down icon beside the Total column.
• Choose the Filter by Color option.
• Choose the color you want. In this case, we chose Green.

You will have the filtered output based on color, as shown in the following image.

Download the Practice Workbook

<< Go Back to Color Filter | Filter in Excel | Learn Excel