How to Apply Conditional Formatting to the Selected Cells in Excel

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 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. Here, we will demonstrate a gif for a better understanding of how to apply conditional formatting.


Download the Practice Workbook

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


6 Easy 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.

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


Method 1:  Using Highlight Cells Rules to Apply Conditional Formatting to the Selected Cells

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:

Steps:

  • Firstly, select the cells where you want to apply this.

Sample Data Set for Highlight Cells Rules

  • Then, go to Home Conditional Formatting Highlight Cells Rules.
  • After that, under the Highlight Cells Rules, you will find a bunch of options as follows:
  • So, 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.

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

  • Besides,  Insert $2000 within the box.
  • Then hit Ok.
  • This will highlight all the cells containing values greater than $2000 as follows:

  • Finally, you will get the following output.

Showing Results by Using Highlight Cells Rules to Apply Conditional Formatting to the Selected Cells

The other available options such as,

  1. Less Than
    Highlights all the cells that contain values less than an inserted value.
  1. Between
    Highlights all the cells that contain values in between two inserted values.
  1. Equal To
    Highlights all the cells that contain values equal to an inserted value.
  1. Text that Contains
    This command highlights all the cells that match the inserted text within the dialog box.
  1. A Date Occurring
    This highlights records that occur on a specific date.
  1. Duplicate Values
    This command highlights all the cells that contain duplicate values.

Read More: How to Highlight Row Using Conditional Formatting


Method 2:  Using Top/Bottom Rules to Apply Conditional Formatting to the Selected Cells

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,

Steps:

  • Firstly, choose a range of cells.

Sample Data Set for Top/Bottom Rules to Apply Conditional Formatting

  • Secondly,  Go to Home Conditional Formatting Top/Bottom Rules.
  • Under this command you will find a bundle of other commands as follows:
  • Then, choose the Top 10 Items option.

Using Top/Bottom Rules

  • Therefore, you will see a dialog will appear here.

  • Finally, the Top 10 Items command will highlight the first 10 items from the select cells as follows.

Showing Results by Using Top/Bottom Rules to Apply Conditional Formatting to the Selected Cells

Other options such as

  1. Top 10%
    This command will highlight the first 10% of items from the range of selected cells.
  1. Bottom 10 Items
    It will highlight 10 items from the bottom side of the selected range of cells.
  1. Bottom 10%
    This command will highlight 10% of cells with colors from the bottom of the selected cells.
  1. Above Average
    This highlights all the cells containing values above the average.
  1. Below Average
    This highlights all the cells containing values below the average.

Read More: How to Highlight Highest Value in Excel


Method 3: Utilizing Data Bars to Apply Conditional Formatting to the Selected Cells

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, follow the below steps accordingly,

Steps:

  • Firstly, 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 options offer bars with a variety of colors.

Utilizing Data Bars to Apply Conditional Formatting to the Selected Cells

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

Showing Results by Utilizing Data Bars to Apply Conditional Formatting to the Selected Cells


Similar Readings


Method 4:  Employing Color Scales to Apply Conditional Formatting to the Selected Cells

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 can follow the below steps accordingly.

Steps:

  • To begin with, Select the range of cells first.
  • Then navigate to Home Conditional Formatting Color Scales.
  • After hitting the Color Scales option, you will have a bundle of choices like this:
  • 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. Therefore, we select this color scale on a range of cells then the highest value will be marked by the green color.

Employing Color Scales

  • Finally, you will get the following result.

Showing Results by Employing Color Scales to Apply Conditional Formatting to the Selected Cells


Method 5: Utilizing Icon Sets to Apply Conditional Formatting to the Selected Cells

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, follow the below steps accordingly.

Steps:

  • To start with, Select the range of cells.
  • Then,  Go to Home Conditional Formatting Icon Sets.
  • After hitting the Icon Sets option, you will see a list of options as follows:

Utilizing Icon Sets

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

  1. Directional
  2. Shapes
  3. Indicators
  4. Ratings
  • From the list, you can choose any of the options. For example, if we choose to start from the Rating category, we will see the result like the picture below.

Showing Results by Utilizing Icon Sets to Apply Conditional Formatting to the Selected Cells

  • In this picture, we can see that there are 3 product ids that start with MTT. Within these 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.

Method 6: Using New Rule to Apply the Conditional Formatting to the Selected Cells

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

Steps:

  • Firstly, select the range of cells.
  • Secondly,  go to Home Conditional Formatting New Rule.

Using New Rule

  • 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 the box to insert the formula within. In that box insert the formula to highlight all the cells having values greater than 20 in the quantity column. After that hit the Ok button.
=$C5>20

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

Showing Results by Using New Rule to Apply the Conditional Formatting to the Selected Cells


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.


Related Articles

Mrinmoy Roy

Mrinmoy Roy

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

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo