Go Back

Learn How to Copy and Paste Conditional Formatting to Another Cell

Read time: 20 minutes

Conditional formatting allows you to copy the format of a cell to another cell or range of cells. You can easily copy the conditional formatting rule applied on one cell to other cell or range of cells by using one of the following approaches.

Copy and paste conditional formatting to another cell

First approach – simple copy/paste

One approach is to copy the value from the cell whose formatting you want to copy and paste it to the target cell or range of cells. To do this, highlight the cell you want to copy, and hit the Copy button on the Home tab or Control-C, and the go to the target cell and hit the Paste button the Home tab or Control-V.

But this approach will not only copy the conditional formatting rule but also copy the value, which is not required in many cases. So this approach may not be useful all the time and you may feel it more troublesome to edit the values again.

Second approach – format painter

Another approach that will only copy the conditional formatting rule from one cell to another cell or range of cells is to use the format painter approach. This will not affect your dataset at all, but you can easily copy the conditional formatting rule to your targeted dataset.

The Format Painter is available in the clipboard section of the Home Tab. It just copies the formatting applied on one cell (including conditional formatting) to another cell or range of cells.

Click on the cell whose formatting you want to copy, click on Format Painter icon and you can see pointer changes to the paintbrush, then drag the paintbrush to cell or range of cells where you want to copy the format.

To stop using a paintbrush, just press Esc.

Example of copying and pasting conditional formatting to another cell

Here, in the example given below, conditional formatting is applied on cell B2, if the value in cell B2 is between 5 and 10, it’ll just fill the cell with red color.

Now if we want to apply the same formatting to data table D3:F7, just select cell B2, click on the Format Painter, it will change the pointer to paintbrush, then drag it across the range D3:F7 and when you leave the mouse, you’ll see that conditional formatting rule that was applied on B2, has now been applied to range D3:F7. Have a look at the images below.

By using Format Painter approach, you can easily copy the conditional formatting rule to other cells.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Add Minimum Cost and Maximum Cost rows that are formatted similarly to row 50 (Average Cost row). Use functions to calculate the minimum and maximum costs for the systems purchased. Convert the Initial Cost data including the Average Cost, Minimum Cost, and Maximum Cost, and the Depreciated Value data from a generic number to accounting format with no decimal places. Copy the average, minimum, and maximum functions to the Depreciated Value columns. Merge and center the title “Desktop & Laptop Systems Inventory & Depreciation” above all of the columns with data. The CEO has asked you use conditional formatting to create an easy visual on the spreadsheet of any device that is near the end of its useful life. Utilize conditional formatting for all columns that contain a depreciated value of less than $500 they will appear red. Create a pie chart to show the initial cost totals by department. You may create a set of summary data to the right of the current data to use for the creation of your chart.
Solved by Z. A. in 24 mins
Automatticaly copy row from Sheet1 to another sheet if cell in certain column from Sheet1 has specific conditional formatting.
Solved by X. L. in 15 mins
Conditional formatting across a column Y6>=$C$2 Format J6 I've tried relative and absolute referencing to the cells and used format painter to copy down column J but it all formats when it shouldn't, same when using colum referencing rther than cell. So checking if a date cell is greater than or equal to a date in cell C2 and if it is to change the format of cell in column J. Should I be doing this a different way?
Solved by F. Q. in 16 mins
Greetings! I'm trying to copy the following conditional formatting formula to different cells, but I need the cell reference to change to reflect the cells where they're being pasted, ie. S16:T16, S17:T17, S18:T18, etc. I'm looking for a quicker way to input the formulas so that I don't have to put them in individually, as there are hundreds that need to be inputted. If you can assist, I would greatly appreciate it. Also, the point of this formula is to highlight the adjacent cells that have the same value. =COUNTIF($S$15:$T$15,INDIRECT(ADDRESS(ROW(), COLUMN(), 4))) > 1
Solved by G. H. in 27 mins
hi I have a spreadsheet that uses conditional formatting. ive created a new formatting rule -> use a formula to determine which cells to format. My formula is =if(bg7 = true, true, false) it works but I want to copy and paste it to rows below. ive used the format painter button but it doesn't automatically change the cell from bg7 to bg8 to bg9 etc. seems like I have to do manually
Solved by E. J. in 21 mins
Hi! I want to code my excel so that it will copy the data from multiple cells to another location using conditional formatting. For example, if the word in cell D4 is not equal to the word in cell E4, I want it to copy cells B4, C4, D4, and E4 to another location on the document. Furthermore, I would like to have this run through multiple rows without leaving spaces in the "copied" area. I could code it as an if statement (which would take forever because i have a lot of data) but I don't think it would allow me to have as many in-bedded if statements as I need. Thank you for your help!
Solved by K. L. in 20 mins
Hi, I've got 2 columns in Excel. The first has the quantity of an item in stock, the second has the quantity needed for a job. For the second collum, I have conditional formatting so that if the number required is greater than the stock, the cell turns red. And if it is ok (so between 1 & number in stock) then it turns green. Is there a way to copy the conditional formatting to all the other items? At the moment, (using both copy/paste, and the copy formatting brush) it copies the conditional formatting, but references it to the copied item's stock, rather than applying it to the new item's stock.
Solved by X. W. in 17 mins
I am looking to copy a cell that contains a conditional formatting formula into a column of other cells that would also contain the same formula but would need to perform the conditional formatting using results in its own row. My conditional formatting works fine but when I try to copy my cell to others in the same column, the conditional formatting stays on the origional cells. Is it possible to do what Im trying?
Solved by T. D. in 18 mins

Leave a Comment

avatar