PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

CONTENTS

HOW TO USE CONDITIONAL FORMATTING IN EXCEL?

INTRODUCTION

To have the knowledge of Conditional formatting is very important if we want to apply Excel in our day to day jobs.

Conditional formatting is the arrangement and formatting of data as per its value of the content of the cells.

It is one of the most versatile functions present in Excel and very easy to apply and learn.

It makes our task so easy which you can understand from the following example.

Suppose we have a group of 100 entries and we need to identify the entries between a very small range say 70 to 85. So conditional formatting lets us, set up a rule to highlight such cells with some color to immediately take our notice of those cells. Not just this but we can put a rule for ranges to highlight the cells with different colors. It can be applied to thousands of cells.

Let us lean the way to apply conditional formatting to our data.

BUTTON LOCATION:CONDITIONAL FORMATTING

STEPS:

  • Go to the HOME TAB( Conditional formatting option is present under HOME TAB).
  • Conditional button can be found on the “HOME TAB” under STYLES SECTION. The location is marked in the following picture.
BUTTON LOCATION CONDITIONAL FORMATTING
BUTTON LOCATION: CONDITIONAL FORMATTING

EXAMPLE:DATA SAMPLE

The following picture shows the sample data we will be using in the example.

EXAMPLE CONDITIONAL FORMATTING
DATA SAMPLE: CONDITIONAL FORMATTING

STEPS TO START CONDITIONAL FORMATTING:

CLICK on CONDITIONAL FORMATTING BUTTON. The following menu will open.

CONDITIONAL FORMATTING MENU
CONDITIONAL FORMATTING LOCATION: MENU

CONDITIONAL FORMATTING:DIFFERENT AVAILABLE OPTIONS

HIGHLIGHT CELL RULES

Select the data and choose any action under highlight cell rules and it’ll highlight(COLOR THE CELLS TO MAKE THEM DIFFERENT FROM OTHER CELLS) those particular cells where the value satisfies your given condition.

CONDITIONAL FORMATTING: OPTIONS
CONDITIONAL FORMATTING OPTIONS

FOR THE EXAMPLE Lets choose ” LESS THAN” formatting and see the result. Put the value as “30”in the dialog box.

CONDITIONAL FORMATTING CHOOSE FORMULA
CONDITIONAL FORMATTING CHOOSE OPTION
In this example, we can see that we want the values which are LESS THAN 30. So it highlighted the values which are less than 30 with a color.

In this DEMO, we can see that we want the values which are LESS THAN 30. So Excel highlighted the values which are less than 30 with a color.

Similarly we can choose any formatting from the menu. All the names given are self explanatory easy to use. Just put the value in the dialog box and click ok, Excel will do it for you.

Some other available options are given below.

 

ADDITIONAL HIGHLIGHT CELL RULES

In addition to the above rule of LESS THAN, there are many other options available. Let us have a look at them.

  1.  GREATER THAN-Highlighting all the numbers greater than a particular value.
  2. LESS THAN- Highlighting all the number less than a particular value.
  3. BETWEEN- Highlighting all the numbers between two particular values.
  4. EQUAL TO-Highliting all the numbers equal to a given value.
  5. TEXT THAT CONTAINS- Highlighting the cells which contain the user given letter sequence.
  6. A DATE OCCURRING – Highlighting the cells which contain the date as per the selected conditions like yesterday, last 7 days, coming 7 days etc.
  7. DUPLICATE VALUES- Highlights all the repeated values.
 

TOP BOTTOM RULES

The next options after the highlighting rules is the TOP/BOTTOM rules.

These rules contains READY TO USE functions for TOP OR BOTTOM VALUES. A brief description is given below.

  1.  TOP 10 ITEMS-Highlight top 10 values of the data.
  2. TOP 10 %- Highlight top 10% values of the data.
  3. BOTTOM 10 ITEMS-Highlight bottom 10 values of the data.
  4. BOTTOM 10 %-Highlight bottom 10% values of the data.
  5. ABOVE AVERAGE-Highlight the values which are above the average of the data.
  6. BELOW AVERAGE-Highlight the values which are below the average of the data.

CONDITIONAL FORMATTING:DATA BARS

It shows the bars in each cell as per the value in the cell(More the value, more filled is the bar) .

The way of using the bars is also the same as that of the highlighting the cell rules. Same is with the color scales. Color scales chooses the color for a particular range.

CONDITIONAL FORMATTING DATA BARS
CONDITIONAL FORMATTING:DATA BARS OPTION

The following picture shows the use of DATA BARS. More the value, more filled is the cell.

CONDITIONAL FORMATTING:DATA BARS
CONDITIONAL FORMATTING:DATA BARS DEMO

CONDITIONAL FORMATTING:COLOR SCALES

Like the data bars, we have color scales option also which shows different colors for different values. e.g. green for higher values and red for lower whereas yellow for the middle.

COLOR SCALES IN CONDITIONAL FORMATTING
CONDITIONAL FORMATTING:COLOR SCALES OPTION

CONDITIONAL FORMATTING:NEW RULE[custom]

New rule is the option given for you to make any custom rule (Self Defined) ,if you want.

Some already listed options are also shown in NEW RULE dialog box .

Let us see what we can do with Custom Rule of conditional formatting.

In this option, you can use the various formatting options as per your choice. The answer will appear in a format as per your definition set through the FORMAT button in the lower right corner.

CONDITIONAL FORMATTING:
CONDITIONAL FORMATTING:NEW RULE DIALOG BOX

Let us give it a try.

Suppose we want that any cell with value more than 65 should be highlighted.

For this, we need to put formula for one cell (first cell at the left upper corner) as rest of the formula will be relative.

CONDITIONAL FORMATTING:NEW RULE EXAMPLE 1

We will highlight those numbers whose values are greater than 65.

For that, as per the example , we’ll put formula for the Cell H6 and then extend it to others. The formula used in this section should always return “true or false” only.

CONDITIONAL FORMATTING:NEW RULE FIXATION
CONDITIONAL FORMATTING:NEW RULE FIXATION

Select the range or all the cells on which the conditions is to be applied. Now click CONDITIONAL FORMATTING BUTTON> NEW RULE.

Put the formula as “=condition for the first cell”. Choose the format as I have chosen a fill color for highlighting. Please mind that formula must start with “=”.
In this formula we are putting a AND statement and its checking whether h6 is number and whether h6>65. Both will be multiplied as (AND function multiplies the values) return true or false).

Now change the format as you want the output.

Click OK.

The cells fulfilling the conditions will be highlighted.

CONDITIONAL FORMATTING:NEW RULE EXAMPLE OUTPUT
CONDITIONAL FORMATTING:CUSTOM RULE EXAMPLE OUTPUT

USER DEFINED FORMULA :EXAMPLE 2

Let us try one more example of Custom Formula or User defined formula for better understanding.

We’ll find out a mid range from given data.

Suppose we have a data of maximum temperatures of any city. We’ll find out the days, on which the temperature was within a certain range.

 

TEMPERATURE OF CITY A (15 DAYS)
DAYTEMPERATURE (°C)
125
228
329
432
528
630
733
835
937
1034
1133
1232
1330
1431
1530

We will find out the temperatures greater than 30 upto 40 degree celcius. So let us try to apply custom formula for formatting on this table. Look at the following picture.

CONDITIONAL FORMATTING:CUSTOM FORMULA EXAMPLE

We select the cells where we want to apply the conditions.

Go to the New formula option.

Put the formula as

=AND(J6>30,J6<=40)

which will return a true for the cells where temperature is greater than 30 and upto 40.

The format can be chosen as per choice. Any color or font can be set.

Press Enter

and we have the ecel highlight the cells which fulfill our conditions.

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS CONDITIONAL FORMATTING, HOW TO COLOR TEXT WHEN ANY CONDITION IS NOT MET, HOW TO DIFFERENTIATE THE VALUES WHICH DOESN’T FULFILL THE CONDITION,conditional formatting excel

LEARN

  • EXCEL
  • HTML
  • JAVASCRIPT
  • MORE TO COME…

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

%d bloggers like this: