Mrinmoy
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.
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.
Under the Highlight Cells Rules, you will find a bunch of options as follows:
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.
Under this command you will find a bundle of other commands as follows:
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.
If you select Gradient Fill, it will highlight cells with a gradient color of bars as the following picture:
But if you decide to select Solid Fill, the result will look like this:
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
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.
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. 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.
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
- Directional
- Shapes
- Indicators
- 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.
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
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.
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.
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.
