How to copy conditional formatting in Excel

You can watch a video tutorial here.

Conditional formatting is a very useful tool that Excel provides to format cells based on their value.  You can use it either to analyze data or to make the data look more appealing. Once conditional formatting has been applied to a column or range of cells, it is easy to copy it to other columns. 

Option 1 – Use the Format Painter tool

Step 1 – Copy and ‘paint’ the format

• Select the source cell 
• Go to File > Clipboard 
• Click on the Format Painter button
• Drag it over the destination range or cells (the cursor changes to a paintbrush)

Step 2 – Check the result

• Check that only the formatting has been copied

Option 2 – Use the Paste Special option

Step 1 – Copy the source

• Copy the source cells using Ctrl+C or right-click and select Copy from the context menu
• Select the destination
• Go to File > Clipboard > Paste
• Select Paste Special

Step 2 – Paste the formatting

• In the Paste Special window choose Formats
• Click OK

Step 3 – Check the result

• Check that only the formatting has been copied