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