What is Conditional Formatting in Excel?

Conditional formatting is an Excel feature that enables you to customize the look of cells based on particular conditions. For instance, you can use conditional formatting to highlight all the cells with values higher than 10. Excel’s conditional formatting feature is a simple yet effective approach to viewing data. Here, you will get an overview of the conditional formatting tool in Excel.

Application of Conditional Formatting

First, select your targeted data range. I’ve chosen the values from cells C2 to C10 from the Price column (column C).

Secondly, select the Home tab.

Now from the Styles group, click the Conditional Formatting drop-down menu.

Find the Conditional Formatting Feature in Excel

You will notice that Conditional Formatting offers various options to highlight.

1. Highlight Cells Rules

The Highlight Cells Rules option has a drop-down list to highlight cells depending on diverse conditions.

For instance, if you go for the Equal To command It will find values equal to your input.

To see how the Equal To command works, click on Equal To.

Find 'Equal To' feature to Highlight Equal Values with Conditional Formatting in Excel

The Equal To window will pop up on the screen.

Then, set an input inside the ‘Format cells that are EQUAL To’ box.

I input $44.

If you like, change the highlight color from the right-side drop box.

I picked the coloring option Light Red Fill with Dark Red Text.

Finally, hit the OK button.

Use 'Equal To' to Highlight Values with Conditional Formatting in Excel

Final Result

The cells containing $44 are highlighted with red color like the image below:

You can also find a value greater than a specific number, or less than a particular number using the Greater Than and Less Than commands, respectively from Conditional Formatting.

2. Find and Highlight Texts

To find a text and highlight it with Conditional Formatting, first, select the cell range.

I’ve selected the Author name (column B) to find the author, Collen Hoover.

Then, hit the Conditional Formatting drop-down menu.

Then take the cursor on the Highlight Cells Rules crop-down list.

Now hit the Text That Contains command.

The Text That Contains window will come along.

Use Text That Contains to Highlight with Conditional Formatting in Excel

In the Format cells that contain the text box, input your target text.

I typed Colleen.

Change your preferred color.

Finally, hit OK.

Use Text That Contains to Highlight texts with Conditional Formatting in Excel

Final Result

You will notice the cells containing the name Collen Hoover are highlighted with a light green color.

Other Features of Conditional Formatting in Excel

You can also explore the other options of Conditional Formatting by yourself, to name a few, Top/Bottom Rules, Data Bars, Icon Sets, etc.

Another useful feature of Conditional Formatting is the Color Scales command.

Choose any of the Color Scales themes. I’ve chosen the first one.

Final Result

The Color Scale created an ordering style where the lowest values are colored with red cells, the medium values are yellow and the highest values are colored green.

Cool, isn’t it?

Using Color Scales to Highlight with Conditional Formatting in Excel

Remove Conditional Formatting in Excel

Now if you want to remove the highlighted cells from your Excel sheet, you can remove the Conditional Formatting by following these steps:

First, hit on the Home tab.

Then select the Conditional Formatting menu from the Styles group.

After that, take the mouse cursor on the Clear Rules menu.

Now if you have applied multiple Conditional Formatting in your sheet and you want to remove a particular Conditional Formatting style, select Clear Rules from Selected Cells command. In that case, select the target cells at the beginning.

Or, if you want to remove the Conditional Formatting from the entire Excel sheet, hit Clear Rules from Entire Sheet command.

Remove Highlights of Conditional Formatting in Excel

Conclusion

Conditional Formatting can be a lifesaver making your data sheet presentable and well organized. It can enrich the quality of your documents a hundred times more than your co-workers. I hope you get an overall idea about how to highlight Conditional Formatting in Excel and its applications. Also, visit your Blog page for more beneficial articles. Have a good day!


Excel Glossary | Autosum | Fill Handle | Merge & Center | Wrap Text | Flash Fill | Find & Replace | Freeze Panes | Format Painter | Format Cells | Sort & Filter | Advanced Filter | Text to Columns | Go To Special | Calculation Options | Name Manager | Excel Table | Slicer | Power Query | Add-ins

Rate this post

Leave a Reply

Your email address will not be published. Required fields are marked *