How to Copy and Paste Conditional Formatting
Another type of formatting we have not covered so far is ‘Conditional Formatting’.
This is a type of formatting that Excel offers us that can be found in the ‘Styles’ section of the ‘Home’ tab (found between the ‘Number’ and ‘Cells’ sections).
Without making this an article about ‘Conditional Formatting’ (instead, read more about conditional formatting here) we will simply use a simple type that highlights cells within a range that are duplicates.
So we selected our first column and applied the ‘Conditional Formatting’ rule that highlights every value that is duplicated in the range with red font and pink background.
We can now copy this formatting by using the same copy cell format method as before.
We simply highlight the first formatted cell in the range which in this case is ‘B3’.
Then select the range that we want to apply the new format to (‘C3:C14’) and follow the procedure to paste format as we did before.
However, this time, we need to select ‘Merge Conditional Formatting’ icon from the ‘Paste Special’ drop-down list that appears when we right click into our destination cell.
‘Merge Conditional Formatting’ is the last icon in the ‘Paste’ section.
Now both value columns have the same conditional formatting rules applied.
This is definitely a great method to copy cell format and paste to the desired selection.
However, Microsoft Excel actually offers a better tool to perform the same function called the ‘Format Painter’.
This tool can also be found in the ‘Clipboard’ area of the ‘Home’ tab and is symbolized by the paint brush. It is just below the ‘Copy’ tool.
The process is very similar but instead of copying the cell or range of cells, we simply make our selection and click on the ‘Format Painter’ paintbrush.
This essentially performs the same function as copying does.
Then we select the cell or range of cells to apply the formatting to and that’s it.
Clearly, this is just a bit more of an efficient way to copy cell format and apply to another cell or range of cells since it involves fewer steps.