Unconditional Excellence: Conditional Formatting in Excel

What is conditional formatting option?

One of the great leaps forward in successive versions of Microsoft Excel has been in clarifying information in immediate and attention grabbing ways. Excel’s calculation tools and charting skills have steadily improved with each update to the program yet other facilities have flourished alongside these more fundamental requirements that enable users to assimilate information in a more immediate and sensory fashion.
Chief amongst these features is conditional formatting. Sometimes the way in which data is presented highlights one particular aspect of the information but disregards another. The table presented may be perfectly correct in what it was created to show yet you may wish to go deeper into the data and discover additional patterns and trends that are hidden by the current format.

Conditional Formatting is a tool that allows us to apply formats to a cell or range of cells, and have that formatting change depending on the value of the cell or the value of a formula.  For example, we can have a cell appear bold only when the value of the cell is greater than 100.  When the value of the cell meets the format condition, the format we select is applied to the cell.  If the value of the cell does not meet the format condition, the cell’s default formatting is used. (By “default format”, I mean the formatting that we set up using the normal formatting tools, not necessarily the worksheet’s default font and font size.)

Simple Conditional Formatting:

·         Highlight cell rules:

The Excel Conditional Formatting Highlight Cells Rules allow us to apply formatting to highlight cells that satisfy one or more specific conditions. These conditions can relate to numeric values (e.g. greater than, between), dates (e.g. a date occurring…) or text values (e.g. text containing…). We can also use the Highlight Cells rules to highlight cells containing duplicate values.

Let us see how to apply conditional formatting highlight cell rules.

 

We will select the cells we want to add formatting to.

In the Home tab, we will click the Conditional Formatting command. A drop-down menu will appear where we will select Highlight Cells Rules. A menu will appear with several rules. Select the desired rule (Greater Than, for example).

From the dialog box, we will then enter a value in the space provided, if applicable. In this example, we want to format cells that are greater than 80, so we’ll enter 80 as our value. If we want, we can also enter a cell reference instead of a number. We will then select a formatting style from the drop-down menu and click on OK.

Result: Excel highlights the cells that are greater than 80.

Now if we will change the value of cells still the formatting condition will be checked. For example: Change the value of cell A1 to 81. Excel changes the format of cell A1 automatically.

Find Duplicates:

Select the range for example A1:C10.

Then on the Home tab, we will click on Conditional Formatting, then on Highlight Cells Rules and finally on Duplicate Values.

We will select a formatting style and click OK.

Result. Excel highlights the duplicate names.

·         TOP/BOTTOM RULES:

The Excel Conditional Formatting Top/Bottom Rules allow us to apply formatting to cells that satisfy a statistical condition in relation to other cells in the range (e.g. above average, within top 10%, etc.).These conditions will only be applied to cells containing numeric values.

Select the cells we want to add formatting to.

On the Home tab, we will click on Conditional Formatting then Top/Bottom Rules and finally select the desired option Above Average.

Then we will select a formatting style.

And then finally click on OK to get the desired result.

Excel has several presets we can use to quickly apply conditional formatting to our cells. They are grouped into three categories:

Data bars are horizontal bars added to each cell, much like a bar graph.

Color scales change the colour of each cell based on its value. Each colour scale uses a two- or three-color gradient. For example, in the Red-Yellow-Green color scale, the highest values are red, the average values are yellow, and the lowest values are green.

Icon sets add a specific icon to each cell based on its value.

Steps to use preset conditional formatting:

Firstly we have to select the cells we want to add formatting to.

In the Home tab, we will click on the Conditional Formatting command. A drop-down menu will appear.

Then we will select Data Bars or Color Scales or Icon Sets. Then select the desired preset and finally the desired result we will get.

Clear Rules:

To clear a conditional formatting rule, we need to execute the following steps.

We will select the cells that have conditional formatting.

In the Home tab we will click the Conditional Formatting command. A drop-down menu will appear from there we will select Clear Rules.

A menu will appear we can choose to clear rules from the Selected Cells, Entire Sheet, This Table or This PivotTable. In this example, we are clearing rules from the selected cells.

Important things to know about Conditional Formatting in Excel

  • Conditional formatting in volatile. It can lead to a slow workbook. So we should use it only when needed.
  • When we copy paste cells that contain conditional formatting, conditional formatting also gets copied.
  • If we apply multiple rules on the same set of cells, all rules remain active. In the case of any overlap, the rule applied last is given preference. We can, however, change the order by changing the order from the Manage Rules dialogue box

SOURCES:

http://www.excelfunctions.net

https://staff.brighton.ac.uk/is/Published%20Documents/Excel%20Conditional%20Formatting%20%28PC%29.pdf

About Megha:

Megha is a B.Tech (Instrumentation & Control Engineering). Currently She is working as Analyst Intern with NikhilGuru Consulting Analytics Service LLP (Nikhil Analytics), Bangalore.

Please follow and like us:
error

About the Author

dyuti
Dyuti is an Analytics Enthusiast. She is an MBA in Finance and B.E in Computer Science. She has years of experience in the field of Analytics and is also the Co-founder, CEO, Nikhil Analytics. She has prior worked with companies like HCL Technologies, Deutsche Bank ,WNS, Reliance Capital etc.

1 Comment on "Unconditional Excellence: Conditional Formatting in Excel"

  1. Purnima nirupam | March 30, 2017 at 1:32 pm | Reply

    Strategic information and skill presented in easy to follow steps. Well written.

Leave a comment

Your email address will not be published.


*


error

Subscribe for Data Analytics Edge Newsletter & Share..:-)

error: Content is protected !!