We may format cells depending on the cell’s value using the wonderful feature of conditional formatting. For a different group of cells that are the same as the previous one, you can always create a new conditional formatting rule in Google Sheets. But why put forth the additional effort? In this article, we will show you 6 simple methods to copy conditional formatting rules in Google Sheets.
A Sample of Practice Spreadsheet
You can download the free Google Sheet from here and practice independently.
6 Simple Methods to Copy Conditional Formatting Rules in Google Sheets
The dataset below will be used to demonstrate the example of copying conditional formatting rules in Google Sheets. The dataset contains the results of a classroom’s yearly test. Anyone who received fewer than 60 points on the Physics exam is highlighted. We want to copy the conditional formatting rules to Chemistry and Math exams.
1. Applying Keyboard Shortcut
Using the Keyboard Shortcut is one of the quickest ways in Google Sheets to copy conditional formatting rules. You may use this technique to paste the formatting on the workbook’s same sheet or a separate one.
- Select a cell or cell range you wish to copy the conditional formatting rules. We selected Cell C5:C10. Press the Ctrl+C button to copy.
- Now select the entire dataset you want to paste the conditional formatting rules. We selected Cell D5:E10.
- Then press the Ctrl+Alt+V button. It will paste the formatting style of the cell you previously copied.
- Now see, we got the same conditional formatting in the new range.
2. Using Format Painter
Google Sheets has a built-in feature to copy the format of the cell. One can quickly copy the formatting rules and paste them into the same sheet or another sheet in the workbook using the Format Painter feature in Google Sheets.
- Choose the cell or range of cells you want to copy the conditional formatting rules. We chose Cell C5:C10. Now, click the Paint format option in the toolbar.
- Now select the full dataset that you wish to paste the conditional formatting rules. We selected Cell D5:E10.
- Once you have selected the cells, Google Sheets will automatically paste the conditional formatting rules to the selected cells.
3. Applying Paste Special Command
One of the simplest methods to copy conditional formatting rules in Google Sheets is to use the Paste Special command. This method may be used to paste the formatting on either the same sheet as the workbook or a different one.
- To copy the conditional formatting rules for a cell or range of cells, select that cell or range of cells. We selected Cell C5:C10.
- Select all the cells where you wish to paste the conditional formatting rules and press the right-click button. Now, go to the Paste special option and then select the Format only option.
- Or, you can also choose Conditional formatting only from the Paste Special option.
- After a while, you will get the following result.
4. Duplicating Conditional Format Rules
You may quickly copy conditional formatting rules in Google Sheets by using the duplicating Conditional Format Rules method.
- First, select a cell whose conditional format rules you want to copy. In the Conditional format rules dialog box, select the conditional format rule of that cell.
- The format rule box has been shown up. Click on the Add another rule option.
- The duplicate rule of the same cell range has been created automatically. Now, you may wish to change the data range. Click on the Select data range icon, to do so.
- Select the data range where you want the same conditional format rule. We selected Cell D5:E10. Press OK.
- The selected cells are subjected to the same conditional format rule.
5. Expanding Range in Conditional Format Rule
Sometimes, you may wish to update the conditional formatting rule to incorporate a new cell range. If you want the formatting in the same sheet but for cells that are not next to those already in the rule, this is a suitable alternative.
- Choose a cell whose conditional format rules you wish to copy. Select the conditional format rule for that cell in the Conditional format rules dialog box.
- The format rule box has appeared. To expand the data range, click the Select data range icon.
- A dialog box named Select a data range has appeared. Click on the Add another range option.
- Select the data range where you wish to paste the conditional format rule and press OK.
- You will obtain the desired outcome.
6. Employing Copy to Command
Sometimes you may wish to copy the conditional formatting rule of the existing spreadsheet to a new spreadsheet. You can easily do this by employing the Copy to command in Google Sheets. Basically, it will copy the whole sheet, so the conditional formatting will be copied too along with the whole dataset.
- First, right-click on the existing worksheet in the below sheet bar. Go to the Copy to option and then select the New spreadsheet option.
- A dialog box named Sheet copied successfully has appeared. Click on the Open spreadsheet option.
- It will launch a new spreadsheet with the same sheet. The sheet name, conditional format rules, and cells are all copied.
This article will demonstrate six simple methods for copying conditional formatting rules in Google Sheets. After reading this article, I hope you can simply copy the conditional format rule and paste it into the existing sheet or another sheet in the existing worksheet; you can even copy the conditional format rules to a new worksheet. Please feel free to ask questions or make suggestions in the comments section below. Visit Officewheel.com to explore more.