Cara conditional formatting di spreadsheet

Watch Video – How to Copy Conditional Formatting using Format Painter

Conditional Formatting is an amazing feature that allows us to format cells (highlight cells) based on the value in the cell.

For example, if you have the scores of all the students in a column, you can use conditional formatting to quickly highlight all the scores that are less than 35

This saves time and also makes the data easy to read and more meaningful.

It’s easy to apply conditional formatting to a range of cells, and it’s even easier to copy conditional formatting in Google Sheets.

In this tutorial, I will show you how to copy conditional formatting in Google Sheets from one cell to another in the same sheet, in different sheets in the same document, and in separate Google Sheets documents.

Let’s get to it!

• Copy Conditional Formatting in the Same Sheet (or different sheets)
  • Using Paste Special
  • Using Paint Format tool
• Copy Conditional Formatting in different Google Sheets file

Copy Conditional Formatting in the Same Sheet (or different sheets)

Suppose you have a dataset as shown below where I have applied conditional formatting to column B (which has the Physics score for the students) so that all the cells with a score less than 35 gets highlighted in red.

And now, I want to apply the same formatting to Column B, which has the Math score.

This can be done using two methods – Paste Special and the Paint Format.

Let’s have a look at both of these methods.

Using Paste Special

Below are the steps to copy conditional formatting from one cell to another in Google Sheets using Paste Special:

1. Select the cell or range of cells from which you want to copy the formatting
2. Right-click and then click on Copy (or use the keyboard shortcut Control + C)
3. Select the range of cells where you want to copy the copied conditional formatting
4. Right-click on any of the selected cells
5. Go to Paste Special and then in the options that appear, click on ‘Paste format only’ option (or use the keyboard shortcut CONTROL + ALT + V)

The above steps would instantly copy and paste the formatting from column B to column C.

In case you want to copy the formatting to multiple ranges, you can repeat the steps 3 to 5. You don’t need to copy the cells that have the formatting again.

Also, these same steps would also work when you want to copy the conditional formatting to cells in another sheet in the same document.

When you copy conditional formatting from one cell to another in the same sheet, it doesn’t create a new rule for the cells where it’s pasted. Instead, Google Sheets simply extends the formatting to include this new range into the current formatting rule (as shown below).

On the contrary, when you copy conditional formatting from a cell to another one in a different sheet, a new rule will be created for the second sheet.

One thing to know when copying conditional formatting using Paste Special method is that it will copy any other formatting as well. For example, if you have given a border to the cell or changed the font size or made it bold/italics, even these changes would also be copied to the destination cells/ranges.

Using Paint Format tool

Just like the paste special method, you can also use the Paint format method.

Paint format (called format painter in Excel) is a simple tool that copies the format from one cell and can be pasted to other cells or a range of cells.

Below are the steps to use Paint format tool to copy conditional formatting in Google Sheets:

1. Select the cell (or range of cells) from which you want to copy the conditional formatting
2. With the cells selected, click on the Paint format tool in the toolbar. This will activate the Paint format tool
3. Use the mouse to select the cell where you want to paste the formatting

You can also use the same steps when you want to copy the conditional formatting to cells in a different worksheet in the same Google Sheets document.

Unlike Excel, you can not use the Paint format tool multiple times. When you activate it, you can only use to copy formatting on the selected range of cells once. If you want to do it for multiple ranges, you will have to select the cells that have the formatting and activate the paint format tool again.

While it’s quite easy to copy formatting from one cell/range to another in the same Google Sheets file, the same won’t work when you want to copy the formatting to a different Google Sheets document.

But there is a simple workaround.

Copy Conditional Formatting in different Google Sheets file

Since you can easily copy formatting from one sheet to another (but not on to a different Google Sheet file), here is a simple workaround:

Move a copy of the sheet (that has the conditional formatting that you want to copy) to the other Google Sheet file and then use any of the above methods to copy formatting to other sheets.

Below are the steps to create a copy of a worksheet in a different Google Sheets file:

1. Right-click on the sheet tab that has the formatting that you want to copy
2. Hover the cursor over ‘Copy to’
3. Click on ‘Existing spreadsheet’
4. In the dialog box that opens, navigate and select the file to which you want to copy this sheet (or if you have it open, you can also copy and paste the URL of the file)
5. Click on Select

The above steps would copy the sheet to the other Google Sheets file.

Now you can use paste special or Paint format to copy conditional formatting from a cell to other cells in other sheets.

I hope you found this tutorial useful!

Other Google Sheets tutorials you may find useful:

• How to Clear Formatting in Google Sheets (Step-by-Step)
• Apply Conditional Formatting based on Another Cell Value in Google Sheets
• How to Merge Cells In Google Sheets
• Formula to Highlight Duplicates in Google Sheets