Mrinmoy Roy
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.
We will be using a Product Price List data table to demonstrate all the methods, to sum up, colored cells in Excel.
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 in Excel
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.
- 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.
- 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.
The other available options such as,
- Less Than
Highlights all the cells that contain values less than an inserted value.
- Between
Highlights all the cells that contain values in between two inserted values.
- Equal To
Highlights all the cells that contain values equal to an inserted value.
- Text that Contains
This command highlights all the cells that match the inserted text within the dialog box.
- A Date Occurring
This highlights records that occur on a specific date.
- 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.
- 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.
- 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.
Other options such as
- Top 10%
This command will highlight the first 10% of items from the range of selected cells.
- Bottom 10 Items
It will highlight 10 items from the bottom side of the selected range of cells.
- Bottom 10%
This command will highlight 10% of cells with colors from the bottom of the selected cells.
- Above Average
This highlights all the cells containing values above the average.
- Below Average
This highlights all the cells containing values below the average.
Read More: How to Highlight Highest Value in Excel
Method 3: Using Data Bars to Apply Excel 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.
- If you select Gradient Fill, it will highlight cells with a gradient color of bars as in the following picture.
Similar Readings
- How to Highlight Lowest Value in Excel (11 Ways)
- Excel Alternating Row Color with Conditional Formatting [Video]
- How to Make Negative Numbers Red in Excel (3 Ways)
- Change a Row Color Based on a Text Value in a Cell in Excel
Method 4: Inserting Color Scales to Conditionally Format the Selected Cells in Excel
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.
- Finally, you will get the following result.
Method 5: Using Icon Sets to Apply Excel 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:
There are different types of icons under 4 categories. These are
- Directional
- Shapes
- Indicators
- 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.
- 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: Applying New Rule to Conditionally Format 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.
- 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:
Things to Remember
📌 Always select the cells before applying the Conditional Formatting command.
📌 Press  CTRL+Z to undo the Conditional Formatting command.
Download the Practice Workbook
You are recommended to download the Excel file and practice along with it.
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.
