CONDITIONAL FORMATTING

CONDITIONAL FORMATTING IN EXCEL.

Here we learn about CONDITIONAL FORMATTING in EXCEL. CONDITIONAL FORMATTING EXCEL has a very good FEATURE. In this, we can put the given CONDITION in our data and can do our work easily.

01. HIGHLIGHT CELLS RULES.

02. TOP / BUTTOM RULES.

03. DATA BARS.

04. COLOR SCALES.

05. ICON SETS.

1 - HIGHLGHT CELLS RULES

HIGHLIGHT CELLS RULES 

In this, we can get our data HIGHLIGHT according to our CONDITION. In this we can HIGHLIGHT our data by applying CONDITION of GREATER THAN or LESS THAN or BETWEEN or EQUAL.

For example here we understand GREATER THAN. In this, if we want the data or the VALUE or any number larger than this, then we will put our LIMIT in it by putting this CONDITION and we get all the numbers above it.

Here we have written numbers from 100 to 1000, in which we want to HIGHLIGHT any number of more than 500.

CONDITIONAL FORMATTING

STEP -1 # First of all we will select all the sales and after that we will click on CONDITIONAL FORMATTING in STYLES BLOCK of HOME TAB.

STEP -2  # After this, we see the option of HIGHLIGHT CELLS RULES, we will CLICK on it.

CONDITIONAL FORMATTING

STEP -3 # After this, we see the option of GREATER THAN, we will CLICK on it.

CONDITIONAL FORMATTING

STEP -4 # After this, we see an option in which we will write the number above which we have to HIGHLIGHT VALUE. Here we want all VALUE above 500, so we will write 500 here and click OK.

CONDITIONAL FORMATTING

All CLUE VALUE above 500 becomes HIGHLIGHT as soon as CLICK on OK.

CONDITIONAL FORMATTING

So this is how we use HIGHLIGHT CELLS RULES.

Similarly, we use all the OPTIONS of the rest of HIGHLIGHT CELLS RULES.

In which we use LESS THAN, BETWEEN, EQUAL TO, TEXT THAT CONTAINS, A DATA OCCURRING, OR DUPLICATE VALUES.

02. TOP/BUTTOM RULES.

In this, we use TOP and BUTTOM data to FIND any data.

In the example above we will FIND TOP 2 VALUE.

STEP -1 # First of all we will SELECT all the CELL and then click on CONDITIONAL FORMATTING and then go to TOP / BUTTOM RULES.

CONDITIONAL FORMATTING

STEP -2 # After that we will CLICK on TOP 10 ITEMS.

CONDITIONAL FORMATTING

STEP -3 # After that we see an OPTION in which we have to select how many VALUE up to the top. Here we have to HIGHLIGHT the top 2 VALUE out of 10 VALUE, so we will write 2 here and click OK.

CONDITIONAL FORMATTING

As soon as CLICK on OK, we get 2 VALUE HIGHLIGHT of the top.

CONDITIONAL FORMATTING

Similarly, we can use all other OPTIONs like TOP 10 ITMES, TOP 10%, BOTTOM 10 ITMES, BOTTOM 10%, ABOVE AVERAGE AND BELOW AVERAGE etc.

03. DATA BARS.

Using this OPTION we can INSERT or add BAR to our data or our own CELL.

STEP -1 # First of all we will SELECT all the CELL and then click on CONDITIONAL FORMATTING and then go to DATA BARS.

CONDITIONAL FORMATTING

STEP -2 # After that, we see OPTIONS of different COLOR, CLICK on one of them.

CONDITIONAL FORMATTING

As soon as CLICK, we see BAR according to VALUE in our CELL.

And similarly we can take our COLOR of our choice and use it and once we use it, if we change the VALUE of our DATA then our BAR also changes according to DATA.

04 . COLOR SCALES.

After using it our DATA’s COLOR changes according to its VALUE. We will do the STEPS below to use it.

STEP -1 # First we will SELECT all CELLs and then click on CONDITIONAL FORMATTING and then go to COLOR SCALES.

CONDITIONAL FORMATTING

STEP -2 #  After that we see OPTIONS of different COLOR, CLICK on one of them.

CONDITIONAL FORMATTING

As soon as CLICK, we see COLOR according to VALUE in our CELL. Which goes towards GREEN COLOR if VALUE decreases and RED increases in VALUE.

CONDITIONAL FORMATTING

And similarly we can take our COLOR of our choice and use it and once we use it, if we change the VALUE of our DATA then our COLOR also changes according to the DATA.

05. ICON SETS.

After using it, ICON is added to the CELL according to the VALUE of our DATA. We will do the STEPS below to use it.

STEP -1 # First we will SELECT all the CELLs and then click on CONDITIONAL FORMATTING and then go to ICON SETS.

CONDITIONAL FORMATTING

STEP -2 # After that we see OPONS to ICONs of different COLORs, one of them will CLICK on OPTION.

On CLICK, we see ICONS in our CELL according to VALUE. Which VALUE is reduced to RED ARROW and VALUE increases to GREEN COLOR ARROW.

And in the same way, we can take and use our COLOR ICON of our choice and once we use it, if we change the VALUE of our DATA, then ICONS also changes according to our DATA.

For more information see VIDEO -

Leave a Reply

© 2020 NtOffice.in         ।     All Rights Reserved.

ABOUT US    ।    CONTACT   US   ।    TERMS  OF  USES   ।  DISCLAIMER   ।  PRIVACY  POLICY

WhatsApp
Google+
Facebook
Telegram