HOW TO COPY 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.

The ways of using CONDITIONAL FORMATTING in GOOGLE SHEETS is already discussed in the article

HOW TO USE CONDITIONAL FORMATTING IN GOOGLE SHEETS?. [ CLICK HERE TO VISIT THE ARTICLE ].

A situation may arise when we need  to simply copy the already applied conditional formatting rules for some new data. For this situation , we’ll discuss the various ways using which we can copy the conditional formatting rules easily from one data to other without affecting its efficacy.

DIFFERENT WAYS TO COPY CONDITIONAL FORMATTING IN GOOGLE SHEETS

YOU ARE SUPPOSED TO BE HAVING THE NECESSARY KNOWLEDGE ABOUT THE APPLICATION OF CONDITIONAL FORMATTING IN GOOGLE SHEETS. IF NOT CLICK HERE.

THE CONDITIONAL FORMATTING CAN BE COPIED DIRECTLY IN TWO WAYS

1. Using the CONDITIONAL FORMATTING COPY option.

2. Editing the  APPLY TO RANGE data directly.         

Let us take an example for learning the ways to copy conditional formatting.

Let us take attendance of two different classes of students.

We’ll highlight the week when the attendance was between 90 to 95 percent.

We’ll copy the CONDITIONAL FORMATTING rules for CLASS 2 directly using our two methods.

EXAMPLE DATA: ATTENDANCE OF TWO CLASSES FOR 17 WEEK

HIGHLIGHTING THE ATTENDANCE BETWEEN 90 AND 95 PERCENT

The first step is to apply the CONDITIONAL FORMATTING and highlight the Week with attendance percentage between 90 and 95.

STEPS TO APPLY CONDITIONAL FORMATTING ON THE DATA OF CLASS 1.

  • Select the cells containing attendance cells of CLASS 1.
  • Go to FORMAT MENU and choose CONDITIONAL FORMATTING.
  • The CONDITIONAL FORMATTING RULES box will open on the right.
  •  The APPLY TO field will be filled already as we selected the range already.
  • Go to FORMULA RULES and choose IS BETWEEN from the drop down list.
  • Enter the minimum value in the first field as 90 and 95 in the second field.
  • Select the FORMATTING for the fill color as GREEN.
  • Click DONE.
  • All the week with attendance between 90 and 95 will be highlighted with a green color.

The following picture shows all the settings.

CLICK HERE TO LEARN THE BASICS OF CONDITIONAL FORMATTING IN GOOGLE SHEETS

APPLY CONDITIONAL FORMATTING TO THE DATA OF CLASS 1

1. USING CONDITIONAL FORMATTING COPY OPTION

We have already applied conditional formatting to the data of CLASS 1 and highlighted all the attendance between 90 pc and 95 pc.Now let us try to copy the conditional formatting directly to CLASS 2 without applying it individually to CLASS 2 using all the lengthy process.

STEPS TO COPY CONDITIONAL FORMATTING USING DIRECT PROVIDED OPTION

  • Select all or any cell from the cells where the conditional formatting is applied. [ Conditional formatting rules which we want to copy ].
  • Press CTRL+C or RIGHT CLICK> COPY.

The following picture depicts the process.

COPY THE CELLS WITH CONDITIONAL FORMATTING

  • Now, select all the cells where we want to apply the copied conditional formatting rules.
  • RIGHT CLICK > Choose PASTE SPECIAL > PASTE CONDITIONAL FORMATTING ONLY.
  • The conditional formatting will be applied to the selected cells.

The following picture depicts the process of pasting the conditional formatting.The following picture depicts the process.

PASTING THE CONDITIONAL FORMATTING ON THE NEW CELLS

The conditional formatting is applied to the new data as well.Final picture is shown below.

FINAL OUTPUT OF CONDITIONAL FORMATTING COPYING PROCEDURE USING DIRECT OPTION

2. EDITING THE ” APPLY TO ” RANGE DIRECTLY TO COPY CONDITIONAL FORMATTING

After we have already copied the conditional formatting successfully using the direct pasting option , now let us try a simpler solution to the same problem. When we copy and paste the conditional formatting, it simply adds the Address of the Additional range in the format rules.If we do that directly, we need not to copy and paste the conditional formatting from the cells. 

STEPS TO COPY CONDITIONAL FORMATTING by editing the apply to range

  • Select any cell on which the conditional formatting has been applied which we want to copy.
  • If CONDITIONAL FORMATTING RULES box is not open, go to FORMAT MENU and choose CONDITIONAL FORMATTING.
  • The Conditional Formatting Rules option box will open.
OPEN CONDITIONAL FORMATTING RULES BOX

  • Click the RULE which we want to copy. In this example , we have a single rule which will appear in the box.
  • The Rule details will open.
  • Go to APPLY TO RANGE and add a comma [ , ] after the already present data there and write the additional range in which you want to apply the rule. FOR OUR EXAMPLE, WE WANT TO COPY THE CONDITIONAL FORMATTING ON THE CELLS F6 TO F22 SO WE ADD THIS RANGE TO THE ” APPLY TO RANGE” FIELD AS SHOWN IN THE PICTURE BELOW.
  • There is no need to do anything else.
  • Click DONE.
  • The rule will be applied to the new table as well.
DATA SAMPLE: CONDITIONAL FORMATTING

  • Click DONE.
  • The rule will be applied to the new table as well.

THE FINAL TABLE IS SHOWN IN THE PICTURE BELOW.

FINAL OUTPUT AFTER CONDITIONAL FORMATTING AFTER EDITING THE RANGE