For example, below I have an example where I have student’s scores and I have used conditional formatting to highlight all the scores that are above 80. If you’re interested in learning all the amazing things you can do with conditional formatting, check out this tutorial where I show some useful conditional formatting examples. Once you have set the conditional formatting rules for a cell or range of cells, you can easily copy it to other cells on the same sheet or other sheets. In this tutorial, I will show you how to copy conditional formatting from one cell to another cell in Excel. I will cover multiple ways to do this – such as simple copy-paste, copy and paste conditional formatting only, and using the format painter. So let’s get started!
Copy Conditional Formatting Using Paste Special
Just like you can copy and paste cells in the same sheet or even across sheets or workbooks, you can also copy and paste the conditional formatting from one cell to another. Note that you can not just copy and paste the cell. You have to make sure that you copy a cell but only paste the conditional formatting rules in that cell (and not everything else such as the value or the formula). And to make sure you only copy and paste the conditional formatting, you need to use Paste Special. Suppose you have a dataset as shown below where I have conditional formatting applied to column B (the Math score) so that all the cells that have the value more than 80 get highlighted. Now, what if I want to apply the same conditional formatting rule to the second column (for Physics score) os that all the cells above 80 are highlighted in green. This can easily be done! Below are the steps to copy conditional formatting from one cell to another: The above steps would copy the conditional formatting from column B and apply it to the selected cells in column C. One thing to remember when using Paste Special to copy conditional formatting is that it will copy all the formatting. So if there is any border in the cells or if the text has been made bold, etc., that would also be copied. Note: The same steps shown above would also work when copying and pasting conditional formatting to cells in another sheet or even another workbook.
Copy Conditional Formatting Using Format Painter
Format painter is a tool that allows you to copy the format from a cell (or range of cells) and paste it. And since conditional formatting is also a part of the formatting, you can also use format painter to copy and then paste it. Suppose you have a dataset as shown below where I have conditional formatting applied to the Math score column so that all the cells that have the value more than 80 get highlighted. Below are the steps to use format painter to copy conditional formatting from the Math score column and apply it to the Physics score column: Pro tip: In case you want to copy the conditional formatting and paste it on multiple cells or ranges (that you can not select at one go), click on the Format painter icon twice. That will keep the format painter active and you can paste the formatting multiple times (unless you hit the Escape key). Once you have the format painter activated, you can use it on the same sheet, on some other sheet in the same workbook, and even on the other workbook. Again, just like with paste special, Format painter also copies all the formatting (including the conditional formatting).
Issue when Copying Conditional Formatting
In most cases, you will have no problems copying and pasting conditional formatting from one cell to another. But you may face issues if you have used a custom formula to determine which cells to format. This option allows you to create your own formula and formatting is applied in the formula returns TRUE for a cell and not applied when the formula returns FALSE. If you have used a formula in conditional formatting that uses absolute or mixed references, then it may not work as expected when copied. For example, in the below example, I have used the formula =$B2>=80 to highlight all cells in column B that have a value higher than 80. But when I copy this conditional formatting to column C, it still references the B column and I get the wrong result (as shown below). So, if you copy conditional formatting from one cell to another and don’t get the expected result, it’s best to check the formula used and adjust the references. For example, in this case, I can change the formula to =B2>=80 and it should work fine. If you’re wondering where the formula goes, click on the Home tab and then on Conditional Formatting. In the options that appear, click on ‘New Rule’. In the New Formatting Rule dialog box, click on the option – Use a formula to determine which cells to format. This will show you the field where you can enter the formula for the selected range. If this formula returns TRUE for the cell, it will get formatted and if it returns FALSE, it will not. So these are two simple ways you can use to copy conditional formatting from one cell to another in Excel – using Paste Special and Format Painter. And in case you see issues with it, check the custom formula used in it. Hope you found this tutorial useful! Other Excel tutorials you may find useful:
Highlight Rows Based on a Cell Value in Excel (Conditional Formatting)Highlight EVERY Other ROW in Excel (using Conditional Formatting)How to Remove Table Formatting in ExcelSearch and Highlight Data Using Conditional FormattingHow to Apply Conditional Formatting in a Pivot Table in ExcelHighlight the Active Row and Column in a Data Range in ExcelApply Conditional Formatting Based on Another Column in ExcelCopy and Paste Multiple Cells in Excel (Adjacent & Non-Adjacent)
