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

Contents

HOW TO USE CONDITIONAL FORMATTING IN GOOGLE SHEETS?

INTRODUCTION

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

Conditional formatting is the  formatting [ font, color, fill color , size etc. ] of data as per its value of the content of the cells.

It is one of the most versatile functions present in GOOGLE SHEETS 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.

WHERE DO YOU FIND CONDITIONAL FORMATTING OPTION IN GOOGLE SHEETS

CONDITIONAL FORMATTING OPTION can be found under the INSERT TAB.

STEPS TO OPEN :

  • Go to the INSERT MENU.
  • Choose CONDITIONAL FORMATTING.
  • The options will appear on the right side of the screen.
HOW TO USE CONDITIONAL FORMATTING IN GOOGLE SHEETS?
BUTTON LOCATION: CONDITIONAL FORMATTING

EXAMPLE 1:DATA SAMPLE

The following picture shows the sample data we will be using in the example. Let us learn conditional formatting using a simple example.

We have data with different numbers . Let us highlight the numbers less than 100.

HIGHLIGHT THE NUMBERS LESS THAN 100 IN THE GIVEN DATA

The data used is shown below.

HOW TO USE CONDITIONAL FORMATTING IN GOOGLE SHEETS?
DATA SAMPLE: CONDITIONAL FORMATTING

STEPS TO HIGHLIGHT NUMBERS LESS THAN 100 USING CONDITIONAL FORMATTING

STEPS TO APPLY CONDITIONAL FORMATTING:

  • Select the data. [ONLY THE CELLS WITH THE DATA ON WHICH WE WANT TO APPLY CONDITIONS].
  • Go to FORMAT MENU> CONDITIONAL FORMATTING.
HOW TO USE CONDITIONAL FORMATTING IN GOOGLE SHEETS?
SELECT THE TABLE AND CHOOSE CONDITIONAL FORMATTING
  • After clicking the CONDITIONAL FORMATTING OPTION, The screen will be like the one shown above.
  • On the right is the conditional formatting options.
  • CLICK THE CONDITIONAL FORMATTING RULES on the right which shows the Range of the selected cells.
  • As soon as we click it, complete CONDITIONAL FORMATTING RULES box will open on the right.

HIGHLIGHT CELL RULES

In the CONDITIONAL FORMAT RULES , we have following options.

  • APPLY TO RANGE: Put the range on which we want to apply the conditional formatting rules.
  • FORMAT RULES: Choose LESS THAN from the given list of rules.
  • FORMATTING STYLE: Choose font, font color etc. formatting for the data which will satisfy the values.

All these are shown in the picture below.

HOW TO USE CONDITIONAL FORMATTING IN GOOGLE SHEETS?
CHOOSE APPROPRIATE DATA AND RULE FOR CONDITIONAL FORMATTING
When we choose the Format Rules as LESS THAN, a new field opens up to enter the value LESSER THAN WHICH , all the values will be highlighted.
  • Enter 100 in this field as per the requirement.
  • Go to Formatting STYLE.
  • Choose format of your choice.
  • For our example, we have just changed the background color of cell to orange.
  • Click OK.
HOW TO USE CONDITIONAL FORMATTING IN GOOGLE SHEETS WITH EXAMPLES?
CHOOSE LESS THAN 100 AS THE RULE AND CLICK DONE.
After CLICKING THE DONE, All the numbers less than 100 are highlighted.
The final table is shown below.
This was a simple example of using HIGHLIGHT TEXT USING CONDITIONAL FORMATTING.
HOW TO USE CONDITIONAL FORMATTING IN GOOGLE SHEETS?
DATA HIGHLIGHTING ALL THE NUMBERS LESS THAN 100

EXAMPLE 2:DATA SAMPLE

The following picture shows the sample data we will be using in the example. Let us learn conditional formatting using a simple example.

HIGHLIGHT THE NUMBERS BETWEEN 50 AND 500  IN THE GIVEN DATA

The data used is shown below.

HOW TO USE CONDITIONAL FORMATTING IN GOOGLE SHEETS?
DATA SAMPLE: CONDITIONAL FORMATTING

STEPS TO HIGHLIGHT NUMBERS BETWEEN 300 AND 500 USING CONDITIONAL FORMATTING

STEPS TO HIGHLIGHT NUMBERS BETWEEN 30 AND 500 USING CONDITIONAL FORMATTING IN GOOGLE SHEETS:

  •  Select the data where we want to apply the conditional formatting.
  • Go to FORMAT>CONDITIONAL FORMATTING.
  • The Conditional Formatting control box will appear on the right.
HOW TO USE CONDITIONAL FORMATTING IN GOOGLE SHEETS WITH EXAMPLES?
EXAMPLE 2: HIGHLIGHTING THE NUMBERS BETWEN 300 AND 500
  • APPLY TO RANGE field will be shown filled as we had already selected the cells to be conditionally formatted.
  • Change FORMULA RULES to IS BETWEEN from the drop down.
  • Change the Formatting for the resultant cells. We chose a GREEN FILL COLOR for our resultant cells.
  • Click DONE.
  • All the cells having data between 300 and 500 will be highlighted. [ Values from 301 to 499 ]
HOW TO USE CONDITIONAL FORMATTING IN GOOGLE SHEETS WITH EXAMPLES?
FINAL TABLE FOR THE EXAMPLE

HOW TO REMOVE CONDITIONAL FORMATTING FROM THE CELLS IN GOOGLE SHEETS

Sometimes, we might need to remove all types of conditional formatting from our sheet.

It can be done using two ways

  1. Removing the applied rules one by one.
  2. Directly removing all the formatting.
REMOVING THE CONDITIONAL FORMATTING SEQUENTIALLY.
  •  When we apply multiple rules on the same data, all the applied rules are shown in the CONDITIONAL FORMATTING RULES box on the right side of the screen.
  • Remove the rules one by one.
HOW TO USE CONDITIONAL FORMATTING IN GOOGLE SHEETS WITH EXAMPLES?
CONDITIONAL FORMATTING RULES LIST

2. DIRECTLY REMOVING ALL THE FORMATTING

  • Select all the cells in which we want to remove the conditional formatting.
  • Go to FORMAT MENU  and choose REMOVE CONDITIONAL FORMATTING.
  • All the conditional formatting will be removed.
HOW TO USE CONDITIONAL FORMATTING IN GOOGLE SHEETS WITH EXAMPLES?
REMOVE CONDITIONAL FORMATTING OPTION IN GOOGLE SHEETS

Leave a Reply

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

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]

Share this:

Like this:

Like Loading...

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

  • PICTURES ARE TOO SMALL?? Don’t worry, Click them, and pinch zoom to see them properly. All pictures are in high resolution.
  • KINDLY LIKE OUR FACEBOOK PAGE BY CLICKING HERE  AND IF WE NEED TO IMPROVE, KINDLY SEND US THE FEEDBACK ON [email protected]
  • Didn’t get what you were looking for?? Just put your question in the chat box present in the Right Lower corner. Name and email id [No spamming, just for the answer of your requirement] are needed.
*CLICK ANYWHERE ELSE TO CLOSE THIS WINDOW
%d bloggers like this: