How to copy conditional formatting in Excel

This guide will cover how to copy conditional formatting in Excel from one cell to another easily.

This post will cover:

• Quick summary of conditional formatting
• Copy conditional formatting using copy and paste
• Copy conditional formatting using the format painter tool
• Scenarios where copying won’t always work

Why use conditional formatting?

Conditional formatting in Excel is super handy. When we are working with large sets of data and tables it can be useful to have some kind of visual guide so we know what to look for. Sometimes a data visualization is the best option, but when we have a lot of numbers to look at conditional formatting is a life saver.

Applying conditional formatting let’s us set rules which will automatically apply a feature such as colour shading, mini bar charts in the cell, or indicators such as up or down arrows.

Let’s say we want to measure whether a new data set has figures that are above or below the average that we would be expecting – we could apply a simple traffic light colour technique such as

• Green – more than 10% above the average figures
• Yellow – within 10% above or below the average figures
• Red – more than 10% below the average figures

By applying these rules we don’t have to sift through lots of data manually to find the stand out figures.

The challenge we often come across however is when we need to start applying these rules to a lot of different cells, especially if our files start growing rapidly. Going into each cell one by one and applying the conditional formatting, even a basic approach like the traffic light system above, can be horribly manual and tedious – really eliminating the whole point of why we want to have these visual cues in the first place!

Luckily, there are a couple of very easy techniques we can apply to copy conditional formatting in Excel from one cell to another. It’s not without it’s limitations if we have more complex rules, but the below will cover off two techniques, and a summary of where this might not always work.

Method 1 – Copy conditional formatting with a copy and paste

The first approach is nice and easy and simply involves a variation of copying and pasting.

Let’s use the below example data set where we are looking to track monthly sales for each employee, and track them against the average benchmark that has been set. Each employee is listed in the left column, with their total sales revenue in the second column. For the sake of this example the figure they were aiming to reach was $8,000.

In cell C3, the sales value for Employee 1, we have set up a simple set of 3 conditional formatting rules:

• Green – above $8,000
• Yellow – equals $8,000
• Red – below $8,000

Even something as simple as setting 3 basic rules can be a bit tedious when we have a big list of employees, so let’s use the paste special feature to copy this formatting over to cells C4 – C7.

To do so, right click on Cell C2 and press copy. Then highlight the cells you want to copy the formatting over to, and under the paste special sub menu select formatting under other paste options. See below:

This will only copy over the formatting of the cell we are copying, without changing any of the actual values that are sitting within it.

The result will be the below:

As you can see, all of the values have remained as is, but the formatting now covers all of the employees so we can very easily see who is under and over performing!

When working with single columns like this the paste special option should cover everything we need to copy conditional formatting.

Let’s move on to the second method.

Method 2 – Format painter

The second approach involves using a tool in Excel called the format painter.

This tool can come in really handy for more than just conditional formatting too. Let’s say you have changed a bunch of font sizes, colours, borders etc.. and you want to apply these to other parts of a spreadsheet. The format painter lets you very quickly choose the base cell and then ‘paint’ over other cells to copy the formatting.

Using the same example above with just the top employee formatted, highlight cell C2, and in the top left corner of the home tab, click on the format painter tool:

From here simply click on a single cell you want to copy the formatting over to, or click and drag the mouse down over all 4 employees. The result will then look exactly like our final result in the paste special approach.

Let’s say however that you have a lot of cells you want to apply the formatting to, but they aren’t all sitting together so a drag motion won’t work – you can double click on the format painter which keeps it turned on until you click the button again, or press escape. This lets you click into as many cells as you want, without needing to turn it back on after each individual click (which is a massive pain and something I wish I knew you could avoid earlier!).

Limitations of copying conditional formatting

Depending on the complexity of the conditional formatting, these methods might not carry over the entire set of rules perfectly. This tends to be the case when you are using a formula.

Dragging formatting works in a similar way to dragging the contents of cells when working with formulas. If rather than manually type in ‘8000’ as the comparison figure we have that in another cell and we set the rules linking to that, we need to ensure that we use the dollar sign to lock the formula in place.

In this example we have the $8,000 goal as a figure in it’s own cell, and the 3 rules for the top employee have been all selected based on cell E3 as opposed to simply typing in the number. This can be handy if the goal changes over time. Much easier to update the cell than the formatting.

If we were to take this, and copy down the formatting, we would get something like the below with every cell turning green, which clearly isn’t what we want!

Looking at the formula, we can see that it’s now looking at E4 rather than E3, and Employee 3 will be looking at E5 and so on. Because of this they are all green as anything higher than zero is above the ‘goal’ here.

To fix this simply add the dollar sign in the formula to lock this in – for example >$E$3

See below:

If you follow this technique every cell the conditional formatting is copied over to will stick with cell E3 as the reference point.

On the flip side – the opposite applies if you want the formula to move down in line with the cells. Maybe you are always comparing to the cell above to see if todays result was better than yesterday for example. In this case remove the dollar signs.

This sums up our guide on how to copy conditional formatting in Excel. For more excel tips be sure to check out the Excel Tips page.