Microsoft Excel is a powerful Microsoft application that business owners are utilizing today. This application combines simplicity with efficiency to increase business productivity. With so many tasks that you can perform using this application, there's room for you to learn new skills each day.
The main reason why many people choose this feature of conditional formatting is to make sure clients and partners can easily spot these cells. Let`s get to Business and show you how to copy conditional formatting in Excel.
Copy and paste conditional formatting to another cell
Conditional formatting will give you a chance to format the cells to another cell or even a range of cells. The good thing is that you can copy the conditional formatting rule that has applied to one cell to another cell or even a range of cells. Here are the different approaches that you can use for this purpose.
Simple copy and paste
This is the easiest way to copy the conditional formatting to another cell. You can do this with simple copy-paste to the target cell or even a range of cells if you want the rule to apply to many cells. The first step is to highlight the cell that you want to copy and eventually hit the copy button. This button can easily be found in the Home Tab or even by clicking Control +C. After this, head to the target cell and press Control +V or right-click to paste what you have copied.
Using the format Pointer
The other approach that can help you copy conditional formatting in excel is Format Painter. This approach is the best because it will only copy the conditional formatting rule only and not the rule plus the values. What this means is that using this rule, will not impact your dataset.
This feature is available directly on the clipboard section in the Home tab. The feature will only copy the formatting applied on one cell to the other cell.
To apply the feature, you are simply supposed to click on the cell to copy the formatting rule. After that, click on the Format Painter icon and you will see the pointer changes to the paintbrush. After that, drag the brush to the range of cells or the cell where you want the rule copied. After you are done using the brush, you can press Esc to stop using it.
After you choose to select the rule form copying and applying it to another cell, let`s say D3:f7, you will have to click on the Format Painter and change the pointer to brush. After that drag, it across the range from D3 to F7. Leave the mouse there and you will see that the rule has already been applied, after that click ESC to discard the brush as shown.
Using The Paste Special Feature
You can use the Paste Special feature to copy the conditional formatting of one column to another column in Excel. For example, if you have a dataset containing English and Math columns as Columns C and D respectively, you can apply the feature to copy the conditional formatting of column C to column D through these simple steps:
Select any cell within column C, say cell C9, and right-click on it.
1. When the menu appears, select the Copy option on it to copy the cell values. You can also press the Ctrl + C buttons altogether to copy the cell content.
2. Go to the Math column (column D), select all cells, and right-click on them.
3. When the next menu appears, click on Paste Special option.
4. A new Paste Special window will appear where you will select the Formats option.
5. Click on OK at the bottom of the menu.
You will notice that all the cells in column D that adhere to the condition set in column C are highlighted. For example, if you wanted to apply conditional formatting to highlight all marks above 80, you will realize that all cells with above 80 marks in column D are highlighted.
Setting Up A New Rule To Copy Conditional Formatting With Text
Suppose the dataset has another column with texts. In this case, you will need to add a condition to format the texts alongside numbers and copy the condition to another column. For example, let’s say you have columns with Chemistry marks (Column C), remarks (Column D), and Biology marks (Column E). If you want to generate similar remarks, such as Great for Biology marks in Column F for any marks above 80, you can copy conditional formatting for Chemistry remarks to Biology remarks using these simple steps:
1. Select the first remark column (Column D) without the header. Go to the ribbon and select the Conditional Formatting menu.
2. When the dropdown menu opens, click on the New Rule option.
3. A new dialogue box called New Formatting Rule will emerge. Under the Select a Rule Type box, select Use a formula to determine which cells to format.
4. Next, in the Format the values where this formula is true box, write the following formula:
=IF(D5=”Great”,True,False)
The formula will check all cells that contain Great as a remark in column D. If the condition is true, the formula will format the cells. But if the condition is false, there will be no change in the cells.
5. Click on the Format button at the bottom of the box.
6. When the new Format Cells box appears, pick any formatting option you prefer and click OK. The step will check and review everything.
7. Click OK again to condition the first remark column. You will now realize all cells containing Great in the first remark column are highlighted.
8. You can now select any cell from the first remark column (Column D) with Great remark and click on the Format Painter.
9. Copy the condition to the second remark column (Column F).
You will see all marks above 80 have Great remarks.
Using Conditional Formatting Rules Manager
The Conditional Formatting Rules Manager keeps track of all rules you had previously set up in your Excel worksheet. It can also duplicate rules and edit them slightly to allow them to fit in other cells. This also allows you to copy conditional formatting easily.
Steps:
1. Go to the Home tab and click on the Conditional Formatting drop-down arrow.
2. Scroll down and select the Manage Rules option.
3. When the Conditional Formatting Rules Manager dialogue box opens, click on the drop-down arrow in the Show formatting rules for checkbox and select This Worksheet. You can also select a different sheet if the rule you want to duplicate is not on your current worksheet.
4. Next, choose the rule to copy at the bottom and click Duplicate Rule. A copy of the rule will appear.
5. You can move to the Applies To box and change the cell range to apply and copy the rule. Alternatively, you can select the sheet and cells with your cursor to generate another box.
6. Finish by clicking Apply to apply the rule to the new cells. After that, you can close the Rules Manager window.
