How to Copy and Paste for Conditional Formatting in Excel

By Shawn McClain

In Excel 2010, conditional formatting will automatically alter the format of a cell -- such as the font, background color or text color -- based on a preset condition. You can then sort the data based on the formatting to bring all the cells that meet your condition to the top of the page. Setting up conditional formatting can be a long process, especially if you have multiple conditions, so if you need to expand the formatting to additional cells, the best method is to simply copy and paste the conditional formatting. If you are using formatting that is conditional upon the range of value in the cells, like data bars or top/bottom rules, you will need to take additional steps to get the new cells formatted as part of the same group as the old ones.

Copy Conditional Formatting

Step 1

Select the Excel cell that contains the conditional formatting rules you want to copy. Hold the "Ctrl" button and press the letter "C" to copy the cell to the clip board.

Step 2

Right click on the cell you want to copy the conditional formatting to. If you are copying it to a range of cells, left-click on the first cell and hold the mouse button down. Then move the mouse to the last cell and release the button. Finally, right-click on the selected cells.

Step 3

Move your mouse over "Paste Special" in the pop-up menu to reveal additional pasting options. Select the icon that has a percentage symbol and a paint brush. The icon is located under the "Other Paste Options" area of the menu and reads "Formatting (R)" when you move your mouse over it. Once you select this icon, the conditional formatting will now affect the new cells.

Combine Multiple Groups of Conditional Formatting

Step 1

Select the top-left cell in the first group and hold the mouse button down. Move the mouse to the bottom-right cell in the second and release the mouse button, selecting the entire area. If the cells you need to select are not right next to each other, you can hold down the "Ctrl" button to select additional cells and areas.

Step 2

Select the "Home" tab at the top of the screen and then press the "Conditional Formatting" button in the "Styles" area. Choose "Manage Rules" from the menu that appears. You will see a screen with several of the same conditional formatting rules listed.

Step 3

Select one of the duplicate rules and press the "Delete Rule" button. Continue until you only have one conditional formatting rule remaining. Select the small box next to the "Applies to" field, which will move your cursor back to the Excel worksheet.

Step 4

Select the exact same area of the worksheet as you did before you brought up the conditional formatting rules manager. Press the small box again once you have completed your selection. Click "Apply" at the bottom of the rules manager followed by "OK." All the selected cells are now a part of the same conditional formatting group.