What is conditional formatting ???

From one of the great leaps forward in succeeding versions of Microsoft Excel it has been in clarifying information in instant and awareness grabbing ways. Excel’s different tools and charting options have improved with different updates and yet other facilities have come alongside these more different basic requirements that enable users to get information in a more easy and stunning fashion.
Chief among all of these features is one of the feature conditional formatting. Mostly the way data is presented  or highlights different aspect of the information but ignore another. The table that is presented may be in a correct way of what it was created to show yet people may feel like to go deeper into it and discover different additional informations and trends that are not shown by the current existing format.

Here Conditional Formatting is a tool that allows user to apply different formats to a cell or range of cells, and have the formatting changed depending on the value in the cell or the value derived from a formula.  For example, we can have a cell appear bold, Italic or in any text format only when the value of the cell is greater than 1000.  When the value of the cell meets the condition given, it will format the cells that fulfills the required condition.  If the the condition of the cell is not met, the cell’s default formatting is be there.

Simple Conditional Formatting:

·         Highlight cell rules:

In Highlight Cells Rules it allow us to apply different formatting to highlight the cells that satisfy the given conditions. These conditions can relate to different numeric values (e.g. less than, equal to, between, etc), dates (e.g. a date between or equal to…) or text values (e.g. text in…). We can also use the Highlight Cells rules to highlight duplicate values from a cell.

Now Let us see how to apply highlight cell rules.

First we will select the cells in which we want to add formatting.

Go to Home tab, click the Conditional Formatting command.

In drop-down menu select Highlight Cells Rules.

In the menu there will be several rules. Select the desired rule (for example: Greater Than).

Then from the dialog box, A value in the space provided will be entered by the user(if applicable).

For Example, here cells that are greater than 80 needs to be formatted, so we’ll enter 80 as value.

If we want, we can also give a cell reference in-place of a number. Then we will select the formatting style from the drop-down menu and click finally on OK.

Finally we will get the Result as shown in below :Excel will highlights the cells which satisfy the given condition i.e. that are greater than 80.

Now if user want to change the value of cells but still the condition for formatting will be checked.

For example: Cell A1 value is changed to 81. Then the format of cell A1 automatically changes.

How to find Find Duplicates:

step 1: Select the range of the cells.

Step 2: Then go to Home tab

Step 3: Click on Conditional Formatting

Step 4: Then click on Highlight Cells Rules

Step 5: Finally click on Duplicate Values.

Step 6: User can select any formatting style

Step 7: Finally click OK.

Final result will be as shown below: Excel will highlights the duplicate values.

·         TOP/BOTTOM RULES:

In Top/Bottom Rules it allow user to apply different formatting to cells that satisfy given statistical condition in comparison to other cells in the range (e.g. below average, within top 50%, etc.).

These are only applied to the cells having numeric values.

Select the cells in which format needs to be added.

Go to Home tab, then click on Conditional Formatting option after that click on Top/Bottom Rules and then finally select the option that needs to be applied for example here we will apply the option Above Average.

Select the desired formatting style.

And finally click on OK.

Excel has different presets which can be used to apply quick conditional formatting to desired cells.

It has been grouped into three categories:

1. First is Data bars which are horizontal bars added to the cells, mostly like a bar graph.

2. Second is Color scales which changes the colour of each cell based on contained value. Each colour the scale uses two- or three-color gradient. For example, in the Red-Yellow-Green color scale, the highest values is red, the average values will be yellow, and the lowest values is green.

3. Third one is the Icon sets which adds a specific icon to each cell based on the contained value.

Below are the Steps to use preset conditional formatting:

Firstly user will select the cells they want to add formatting to.

Go to Home tab, click on Conditional Formatting.

Then the drop-down menu appears.

Here select Data Bars or Color Scales or Icon Sets.

Select the desired preset.

Finally the user will get the desired result.

Clear Rules:

To clear a conditional formatting rule in the applied cells, user need to execute the below steps.

Select the cells that already have conditional formatting.

Go to Home tab and click on Conditional Formatting command.

In the drop-down menu select Clear Rules option.

In the menu choose the option to clear rules from the Selected Cells, Entire Sheet, This Table or This PivotTable.

For example, we will be clearing rules from the selected cells.

Important things to know about Conditional Formatting in Excel

• Conditional formatting is volatile. Due to this workbook will get slow. So we should use only when we need it.
• When user will copy and paste the cells which contains conditional formatting, then the formatting will also get copied.

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.