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.
Leave a Comment