Seemanto Saha
Conditional Formatting is used to set predefined conditions and highlight the relevant data. Different color formats of this tool help us reading and analyzing the data efficiently. While using this tool, Conditional Formatting is often needed to be copied to other cells, worksheets, and even different workbooks. In this article, I will show you several methods on how to copy any Conditional Formatting to other cells, worksheets, and workbooks while using Google Sheets.
A Sample of Practice Spreadsheet
4 Easy Ways to Copy Conditional Formatting to Other Cells in Google Sheets
First, let’s know about our datasheet. We have taken a sales record of 5 employees for 4 quarters of a year. The sales values for 1st quarter have a Conditional Format, as shown in the image below. Now let’s go through the methods.
1. Use Paste Special
We can copy the Conditional Format and then use Paste Special to paste the Conditional Format into other cells.
Steps:
- Select cells where Conditional Formatting is present (array C6:C10) and right-click on your Mouse. From the pop-up options click on Copy (or, simply type CTRL+C).
- Now, select the cells where you want to copy the Conditional Format (array D6:F10) and right-click on your Mouse. From the pop-up options, select Paste Special and then click on Conditional Formatting Only.
- The Conditional Formatting has been copied to the array D6:F10.
2. Apply Format Painter
The easiest solution to the problem “How to copy Conditional Formatting to other cells in Google Sheets” is to apply Format Painter. We can do it just by dragging over another range.
Steps:
- Select cells where Conditional Formatting is present (array C6:C10) and click on the Format Painter option from Clipboard.
- Finally, select the cells or drag over the range where you want to copy the Conditional Format (array D6:F10). The array D6:F10 now contains Conditional Formatting.
3. Expand the Conditional Formatting Rule
We can expand the array of Conditional Formatting if we require copying the format to adjacent cells of currently formatted cells.
- Select cells where Conditional Formatting is present (array C6:C10). Then go to the Format ribbon and select Conditional Formatting.
- A sidebar like the following will pop up. Click on the Conditional Format Rules that you want to copy.
- Apply to range will be set as C6:C10 (where the current Conditional Format is).
- Set the Apply to range as C6:F10 and then click on Done.
- And we have expanded the Conditional Formatting up to the required Cell.
4. Duplicate the Conditional Formatting Rule
This method is useful if you want to skip a few columns or rows while copying the Conditional Format.
Steps:
- Use the first 3 steps of method 3 to pop up the sidebar like the following. Click on “+Add another rule”.
- Click on Done to duplicate the rule.
- Click on the duplicated rule from the sidebar.
- Set Apply to range as E6:F10 in the duplicate rule and then click on Done.
- The Conditional Format is now copied to cells of Column E and Column F, while the cells of Column D are skipped.
How to Copy Conditional Formatting from One Google Sheet to Another
We can also copy Conditional Format from one worksheet to another. To show you the process for that, I have taken two worksheets. The first worksheet is titled “Worksheet 1” and contains a Conditional Format in array C6:F10. This Conditional Format will be copied to another worksheet titled “Worksheet 2”.
Worksheet 2 contains a similar type of data as Worksheet 1. Although, the array size of Worksheet 1 and Worksheet 2 doesn’t necessarily have to be similar. You can use method 1 or 2 from the previous section to copy the Conditional Format. I will be using method 2 (Apply Format Painter).
Steps:
- In Worksheet 1, select the array where the Conditional Formatting is present (C6:F10) and click on Format Painter from the Clipboard.
- Now, go to Worksheet 2 and select the array where you want to copy the Conditional Format (C6:F10). Worksheet 2 now contains the required Conditional Formatting.
How to Copy Conditional Formatting from One Workbook to Another in Google Sheets
There is no direct way to copy Conditional Formatting from one workbook to another. But we can easily do it by applying some tricks. Let’s assume, we will be copying our Conditional Format from the current workbook titled “Copy Conditional Formatting” to another workbook titled “Sample Workbook”. Now, follow the steps below.
Steps:
- In the workbook “Copy Conditional Formatting”, take your Cursor above the worksheet title and then right-click on your mouse. From the pop-up options, select Copy to and then click on Existing Spreadsheet.
- In the popped-out window, click on the workbook where you want to copy the Conditional Formatting (Sample Workbook) and then click on Select.
- Another pop-out window like the following will appear. Click on OK.
- A copy of the Worksheet (from where you want to copy the conditional format) from the Workbook “Copy Conditional Formatting” is created in the “Sample Workbook”. As copying from one sheet to another in the same file is possible, so now we can simply copy the conditional formatting.
- Follow the steps from the previous section to copy the Conditional Formatting from this worksheet to the worksheet where you want to copy the formatting.
How to Copy Conditional Formatting Color to Another Cell in Google Sheets
The Conditional Formatting Color will be copied with the Conditional Format. You can’t copy the Conditional Formatting color only.
Conclusion
This concludes our article to learn how to copy Conditional Formatting to other cells in Google Sheets. I hope the mentioned methods were able to fulfill your requirements. Feel free to leave any advice or comments in the comment box.
