Apply Conditional Formatting to the Selected Cells in Excel (6 Ways)

Conditional Formatting is a versatile and flexible tool embedded in Excel that enables us to modify and format cells based on various conditions. There are many of the conditions based on which we can format cells in many ways. To guide you, in this article, we will discuss 6 different ways that you can use to apply conditional formatting to the selected cells in Excel.


Download the Practice Workbook

You are recommended to download the Excel file and practice along with it.


6 Ways to Apply Conditional Formatting to the Selected Cells

We will be using a Product Price List data table to demonstrate all the methods, to sum up, colored cells in Excel.

dataset for 6 Ways to Apply Conditional Formatting to the Selected Cells

So, without having any further discussion let’s get into all the methods one by one.


1. Apply Conditional Formatting to the Selected Cells Using Highlight Cells Rules

We can highlight cells with colors based on different conditions using the Highlight Cells Rules command. To apply this command, all you need to do is:

❶ Select the cells where you want to apply this.

❷ Then go to Home Conditional Formatting Highlight Cells Rules.

Apply Conditional Formatting to the Selected Cells Using Highlight Cells Rules

Under the Highlight Cells Rules, you will find a bunch of options as follows:

highlight cells rules excel Apply Conditional Formatting to the Selected Cells

You can use any of the commands from the list as per your requirement. For example, Greater Than command will highlight all the values greater than a value that you set as a criterion. If you select Greater Than from the list a dialog box will appear. Now,

❶ Insert $2000 within the box.

❷ Then hit Ok.

This will highlight all the cells containing values greater than $2000 as follows:

The other available options such as,

1. Less Than

Highlights all the cells that contain values less than an inserted value.

2. Between

Highlights all the cells that contain values in between two inserted values.

3. Equal To

Highlights all the cells that contain values equal to an inserted value.

4. Text that Contains

This command highlights all the cells that match the inserted text within the dialog box.

5. A Date Occurring

This highlights records that occur on a specific date.

6. Duplicate Values

This command highlights all the cells that contain duplicate values.

Read more: How to Highlight Row Using Conditional Formatting


2. Use Conditional Formatting to the Selected Cells Using Top/Bottom Rules

Top/Bottom Rules enable us to highlight a specific number of cells from the top or bottom of a range of items. To apply this command,

❶ Select a range of cells.

❷ Go to Home Conditional Formatting Top/Bottom Rules.

Use Conditional Formatting to the Selected Cells Using Top/Bottom Rules

Under this command you will find a bundle of other commands as follows:

top bottom rules excel Apply Conditional Formatting to the Selected Cells

By selecting the Top 10 Items command will highlight the first 10 items from the select cells as follows:

Other options such as

1. Top 10%

This command will highlight the first 10% items from the range of selected cells.

2. Bottom 10 Items

It will highlight 10 items from the bottom side of the selected range of cells.

3. Bottom 10%

This command will highlight 10% of cells with colors from the bottom of the selected cells.

4. Above Average

This highlights all the cells containing values above the average.

5. Below Average

This highlights all the cells containing values below the average.

You will see a dialog box appear after hitting each of the commands. From the dialog box, you can insert values as per your requirement. For example, if you want to watch the first 5 items from the top in lieu of the first 10 items, then you need to insert the number 5 instead of 10 within the dialog box as follows:

Read more: How to Highlight Highest Value in Excel


3. Implement Conditional Formatting to the Selected Cells Using Data Bars

Data Bars is an interesting tool, that highlights cells with bars of colors in sync with the values that the cells contain. For example, a cell containing a higher value will be highlighted with a longer bar of colors compared to a cell with a lower value within it.

To use this feature,

❶ Select the range of cells first.

❷ Then go to Home Conditional Formatting Data Bars.

After reaching the Data Bars you will find two options available. One is Gradient Fill and another is Solid Fill. And both the options offer bars with a variety of colors.

Implement Conditional Formatting to the Selected Cells Using Data Bars

If you select Gradient Fill, it will highlight cells with a gradient color of bars as the following picture:

data bars excel Apply Conditional Formatting to the Selected Cells

But if you decide to select Solid Fill, the result will look like this:


Similar Readings:


4. Employ Conditional Formatting to the Selected Cells Using Color Scales

If you want to highlight cells with different colors based on their values, then you can use the Color Scales command. Because this command will enable you to highlight the cells with various colors each for a different value. To use this feature, you have to

❶ Select the range of cells first.

❷ Then navigate to Home Conditional Formatting Color Scales.

Employ Conditional Formatting to the Selected Cells Using Color Scales

After hitting the Color Scales option, you will have a bundle of choices like this:

color scales excel

Now if we put the mouse cursor on the very first choice, a hint text appears. According to that, it’s called Green-Yellow-Red Color Scale. If we select this color scale on a range of cells then the highest value will be marked by the green color, then the following will be marked by Yellow and Red colors.

As we’ve selected the first color scale, the result looks like this:

You can choose any of the color scales as per your requirement and choice.


5. Execute Conditional Formatting to the Selected Cells Using Icon Sets

Icon Sets command assigns icons to cells based on their values. It’s an interesting way to represent data in Excel worksheets. To apply this feature,

❶ Select the range of cells.

❷ Go to Home Conditional Formatting Icon Sets.

Execute Conditional Formatting to the Selected Cells Using Icon Sets

After hitting the Icon Sets option, you will see a list of options as follows:

There are different types of icons under 4 categories. Which are

  1. Directional
  2. Shapes
  3. Indicators
  4. Ratings

From the list, you can choose any of the options. For example, if we choose starts from the Rating category, we will see the result like the picture below:

In this picture, we can see that there are 3 product ids that start with MTT. Within this 3 ids star is assigned according to the number of quantities. The highest quantity is marked with a full star, the lowest is with an empty star and the in-between is with a half-filled star.


6. Use New Rule to Apply the Conditional Formatting to the Selected Cells

If you need more options than the options been discussed above then you can use New Rule to facilitate more options to format cells. To use this feature,

❶ Select the range of cells.

❷ Go to Home Conditional Formatting New Rule.

Use New Rule to Apply the Conditional Formatting to the Selected Cells

As you are done with all the previous steps, you will see the below dialog box appear. Where you can find a few other options that you can use to format cells as per your requirement. For example, If we select Use a formula to determine which cells to format, you will get to box to insert formula within. In that box insert the formula:

=$C5>20
 to highlight all the cells having values greater than 20 in the quantity column. After that hit the Ok button.

Now you will see all the cells containing values of more than 20 is being highlighted with color like the image below:


Clear Rules

After you have applied all the formatting within the cell, cases may arise that you want to remove those cell formatting the selected cells in your Excel workbook, to remove the formatting from the cells, you can follow the steps below:

❶ Select the cells where you’ve already applied cell formatting.

❷ Go to Home Conditional Formatting Clear Rules Clear Rules from Selected Cells.

Clear Rules excel conditional formatting

That’s it.


Manage Rules

If you want to update, create, or delete any formatting that you’ve already applied to a range of cells, then you can use the Manage Rules command to implement them easily. To apply this command,

❶ Select the cells where you’ve applied formatting.

❷ Go to Home Conditional Formatting Manage Rules.

Manage Rules

After hitting the Manage Rules a dialog box will pop up. From where you can easily create, update, or delete any rules that you’ve already created.


Things to Remember

📌 Always select the cells before applying the Conditional Formatting command.

📌 Press CTRL + Z to undo the Conditional Formatting command.


Conclusion

To wrap up, we have illustrated 6 different methods, to apply conditional formatting to the selected cells in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap.


Read More

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo