Conditional Formatting is a very useful tool that allows you to format a specific cell or a group of cells. With colored or bold texts or using the highlights, Conditional formatting helps to attract attention to certain values and texts. For example, if you wish to highlight numbers between a certain range, say 1000-2000. When you apply the format, the cells with values between 1000-2000 would be highlighted.
More often than not, when you are working on a large file, you need to apply the same format to your worksheet or new spreadsheet. Applying the format, again and again, can take a long time. This is where I come to your rescue. I’ll show you different ways to copy conditional formatting in Microsoft Excel and apply it elsewhere in your spreadsheet or another worksheet.
You can use three different methods to copy conditional formatting in Excel. To make it easy to understand, the methods have been given in a detailed manner below.
Copy Formatting Using Paste Special
While working on Excel, you must have copied and pasted data from one cell to another. In the same manner, you can copy Conditional Formatting from one cell to another. However, you need to ensure to copy and paste only the Conditional Formatting rules and not the entire cell.
To only copy and paste the conditional formatting, you need to use the Paste Special.
Follow the steps given below to Copy Conditional Formatting Using Paste Special.
- Select the cell with the Conditional Formatting rule applied to it.
- In the Ribbon, Select Home>Clipboard>Copy.
As seen in the picture below, I have selected the cells B3 to B7 and highlighted the numbers between 1000-4000. To copy the same format to cells C3 to C7, you need to follow the second step
- Now, Highlight the cell/cells where you want to copy the formatting rule.
- In the Ribbon, select Home>Paste>Other Paste Options> Formatting.
Here, I have selected the cells C3 to C7. Then followed the fourth step of pasting the format by going to the other paste option and then selecting the ‘Formatting’ option.
Now you can see the conditional formatting is applied to the cells C3 to C7.
Copy Formatting Using Format Painter
The next method to apply Conditional Formatting to another cell is using the Format Painter. It is a simple but powerful tool that can help you save time when developing complicated worksheets.
Follow the steps given below to Copy Conditional Formatting Using Format Painter:
- Select the cell with the conditional formatting rule applied to it. Here I’ve taken the cells B3 to B7 and highlighted the cells with values between 1000-4000.
- Now if I want to apply the same format to cell C3 to C7, I need to go to the Ribbon, Select Home>Clipboard>Format Painter.
3. The cursor will change to a plus sign with a paintbrush.
4. Select the cells you want to apply the same rule.
Your target cell will now have the new formatting.
Copy Formatting Using the Conditional Formatting Rules Manager
The third method to copy the formatting of a cell is to use the Conditional Formatting Rules Manager. This method is very useful to keep track of the rules you’ve already applied to your worksheet. It also aids in copying formatting by creating a duplicate rule and then slightly altering it to fit other cells.
Follow the steps given below to use Conditional Formatting Rules Manager:
- In the Ribbon, Select Home>Conditional Formatting.
- A dropdown menu will appear.
- Select ‘Manage Rules’
- The Conditional Formatting Rules Manager will open.
- Select ‘This Worksheet’ in the drop-down box at the top.
- Select the rule you want to copy and click ‘Duplicate Rule.’
- You can now change the cell range in the ‘Applies to’ box on the right for the new cells.
- Click ‘Apply’ to apply the rule to the new cells.
Wrapping Up
Conditional Formatting is a great way to ensure that certain data in your sheet stands out. But if not done properly, it can be time-consuming. So next time you decide to apply the same format to another part of your worksheet, just copy it. It will save you a lot of time!
