Imagine a quarterly sales meeting. All the salesmen are here from different regions, and the manager is going to review all the sales figures.
To review the sales figures, he needs to know about the salesmen who sold more than a certain benchmark.
Excel has a feature that can help the manager highlight those sales figure which is called conditional formatting.
What is Conditional Formatting?
Conditional formatting is a feature in excel that lets users to highlight or format cells based on the values or text in a cell.
Let’s see how a conditional formatting works. Below I have attached sales figures of 14 salesmen. Using conditional formatting I have highlighted quarter one’s sales which are higher than $5000.
The manager needs to highlight the other quarters in a similar way. He can manually create conditional formatting for the other 3 quarters as well but there are easier ways to copy conditional formatting in excel.
How to Copy Conditional Formatting in Excel?
Let’s look at 2 easy ways to copy conditional formatting in excel.
Pointers from the author: These methods can be used to copy conditional formatting across multiple sheets, rows, or columns.
Method 1# Copy Conditional Formatting in Excel Using Paste Special
The conditional formatting essentially highlighted the high sales figures or color coded the sales figures that are above $5000.
Now, our job is to copy conditional formatting colors to other rows, sheets, or columns.
To do that, simply, select any of the green colored cells, right click and copy the cell’s contents.
You can also copy the contents of the cells by using the hotkey-
CTRL+C
After you have copied the contents of the cell, select the next column you want to apply the conditional formatting.
Then right click and go to “paste special”
After you select the paste special option, a dialogue box will appear. From the box, select “Formats” and click ok.
You will see that paste special gives you wide range of options to paste different cell contents. However, for this time, you want to copy conditional formatting. As conditional formatting is a type of format or in this case a color, you want to select “Formats.”
And voila, you have successfully copied conditional formatting.
Pointers from the author: Paste special option is indeed special. Hence you use it to copy multiple things like formulas, column widths, validations etc. So, if you have any other conditional formatting done in the cell (borders, effect etc.) it will also copy that.
Now let’s look at the second method of copying conditional formatting in excel.
Method 2# Copy Conditional Formatting in Excel Using the Format painter
The name tells it all, format painter paints format. Or it copies formats from a cell and pastes it on other cells, columns, rows, or sheets.
Before using the format painter, you need to understand how it works.
If you notice, we still have two other columns to conditionally format. But if you use the format painter once, you can’t use it again unless you select the format painter again.
To avoid that, click on format painter twice to use it multiple times before pressing the “Esc” key to get out of format painter option.
Pointers from the author: To use format painter multiple times or for multiple rows and columns, double click on format painter.
So, lets get started.
First, select any of the green colored cell and click “Format Painter”
To know if format painter is activated or not, you can see for the dotted border.
We have to copy the format across two columns. So, we need to click on “Format Painter” again.
Now, select the columns left to conditionally format.
You have to separately select the two columns to paste the conditional formatting.
Now press “Esc” key to get out of the format painter option.
And voila, you have successfully copied conditional formatting.
Using these two simple methods, you don’t have to copy and paste conditional formatting individually.
These methods can help you copy conditional formatting in two clicks.
Related Article:
Hi there, I am Naimuz Saadat. I am an undergrad studying finance and banking. My academic and professional aspects have led me to revere Microsoft Excel. So, I am here to create a community that respects and loves Microsoft Excel. The community will be fun, helpful, and respectful and will nurture individuals into great excel enthusiasts.