You’ve spent some time getting your conditional formatting rules just perfect. But now you need to apply them to another column or range of cells in your sheet without affecting the data in those cells. You just want the formatting duplicated. How do you do it?
You have 2 options. You can use the format painter, or you can copy and paste just conditional formatting using Paste Special.
Here’s a picture of the sample data we’ll be using for this ‘How To’. A simple job list with a status column that is green when a cell is not empty, red when it contains “TODO”, and yellow when the status cell contains “In Progress”.
Copying conditional formatting using ‘Paste Special’
You can copy conditional format rules using ‘Paste special‘ from the context menu. A normal paste (Ctrl+v or ⌘+v on Mac) will add the rules and replace the data in the destination cells. If we don’t want to do that, then using ‘Paste special’ will allow us to paste only the conditional formatting rules. Here’s how to use it:
- Select a cell containing the rules you want to copy
- Copy the contents of the cells using Edit > Copy from the menu or Ctrl+c or ⌘+c (Mac)
- Select the cells that you want to add the rules to
- Open Edit toolbar menu and select Paste special > Conditional formatting only
- Google Sheets will add the formatting rules from your source cells to the selected destination cells
It’s worth noting here that pasting conditional formatting adds new rules to the destination cells. They will not replace any existing rules on those cells. So if the destination cells already have conditional formatting applied, be sure to remove those rules, otherwise they may interfere with the pasted ones.
Using ‘Paint Format’ to copy conditional formatting
Paint format is a handy toolbar button in Google Sheets that allows you to copy all formatting (including conditional format rules) from a source cell to a set of destination cells.
Here’s a step by step on how to use it:
- Select a cell containing the conditional format rules you want to copy
- Click the paint format button on the toolbar
- Drag your mouse to select the cells that you want to add rules to
NOTE: ‘Paint format‘ will copy ALL formatting from the selected cell to the destination cells. That’s conditional format rules, data validation rules, font, font size, borders, highlighting etc. Everything except the actual cell value.