Conditional Formatting – an introduction

Hello all. Let’s look at an introduction to conditional formatting. Conditional formatting is an excellent way to visualize data set in Excel. There are some pre-defined rules that Excel provides to visualize. However you can manually create rules, using formulas, to visualize data.

Pre-Defined Rules

Let’s look at the below data set – we need to highlight all the duplicate entries in column A (name). We have A7,A9,A11,A13 as duplicate entries

Select the name section (A1:A13) -> under the Home ribbon, click conditional formatting -> Highlight Cell rules -> Duplicate values – > customize the fill and text color how you need to visualize the duplicate values.

Collage of all the above steps

Next, let’s highlight values in column B which has value more than 40. Select values in column B (B2:B13) -> Conditional Formatting -> Highlight cell Rules -> Greater than -> Enter 40 and customize how the visualization has to be

Collage depicting all the above steps

Next, let’s highlight top 10% of values in column B. Again, select B2:B13 -> Conditional formatting -> Top/Bottom rules -> Top 10% of -> customize the visualization

Next let’s depict the data within column B (matches), in a simple data bar. Note: we are not creating a graph, but just visualizing the data as data bars. Select B2:B13 -> conditional formatting -> Data bars -> Choose either Gradient fill or Solid fill

Let’s add a heat map to depict top – bottom values of the scores (column C). Select C2:C13 -> conditional formatting -> Color Scales -> Red Yellow Green Scale

Using Manual formulas

In the same data set, let’s highlight cells in column A where the value in cell is “Sharma”. Select A2:A13 – Conditional formatting -> New Rule -> Format only cells that contain -> Equal to -> enter the value as “Sharma” -> hit format -> choose format color and fill color to customize the visualization.

All this while we were highlighting a particular cell – let’s look at how to highlight the entire row based on a condition in one cell. Let’s highlight the entire row when the value in column A is sharma

Select the entire data set -> Conditional formatting -> New rule -> Use a formula to determine which cells to format -> Enter the formula as $A1 = “Sharma” – Format how your customization should be

Clearing Conditional Formatting

We can clear the added conditional formatting in two ways. Either all the formatting that exist in the entire sheet or only in the selected cells. Conditional formatting -> Clear rules -> Select the option you want

Editing conditional formatting

If needed, we can edit the existing conditional formatting applied in the sheet. Select the data -> Conditional formatting -> Manage Rules -> Select the rule you need to edit and edit accordingly.

This was just an introduction to conditional formatting. I hope you liked this section. Thanks for reading!

Video tutorial

Leave a Reply

%d bloggers like this: