In this article we will learn about what is conditional formatting and how we to copy conditional formatting in google sheets using 2 methods with proper example.
What is Conditional Formatting in Google Sheets?
Conditional formatting allows a user to provide conditions to the cells, upon satisfaction of the condition a certain formatting (called conditional formatting) is applied to them. It saves the user from the trouble of carefully looking at the cells and pointing out which cells need formatting and which should not have that certain formatting. Common examples are pointing out the failed students, low attendance employees, empty stock products and so on.
Why we use Copy Conditional Formatting in Google Sheets?
Copying a conditional formatting and applying it to multiple cells simultaneously saves one person from the trouble of applying conditional formatting to all required cells one by one. It can save up a lot of time because the number of cells to be formatted can be in large quantity. In this article, we will show you how to copy conditional formatting and apply to multiple cells with a few clicks in google sheets.
Download/Copy Practice Workbook
How to Copy Conditional Formatting in Google Sheets
Conditional formatting allows user to let sheets check the value in the cell and apply formatting if it satisfies the condition provided for formatting.
If you are dealing with a large google sheet and have to deal with thousands of cells, and now you are required to make a change and apply it to hundreds of cells in the sheet, doing it manually can take up to hours or even days.
You can copy conditional formatting within same sheet or different sheets.
Using copy conditional formatting can save your hours. Here we will discuss two easy methods for copying conditional formatting and applying on multiple cells within same sheet or different sheets.
These are as:
- Using Paste Special
- Using Paint Format
Let’s say you are working on the following sheet showing sales for multiple products within the last month and now you have to highlight the products with too fewer sales (say less than 100) so that you can limit their stock purchase.
You must first have a conditional formatting to copy it to other cells. We use the following conditional formatting here as per our requirements (highlight too fewer sales).
We see that we only applied the required conditional formatting on first cell of Sales. In order to copy the conditional formatting to other cells in the column, we follow the following steps:
Method 1: Copy Conditional Formatting using Paste Special
Step 1: Copy the cell(s) having the conditional formatting as:
- Right click on the cell(s) which have the conditional formatting to be copied. And select “Copy”.
- Right Click -> select Copy
Or Simply press “Ctrl+C” over the cell(s)
Step 2: Select the cells you want to copy the conditional formatting onto.
- You can select by dragging the mouse over desired cells to be formatted.
- Or Press Shift and select the cells to be formatted.
Selected cell(s) will be highlighted as:
Step 3: Right click on the selected cells. A sub-menu will appear, choose Paste Special and then Conditional Formatting Only.
Right Click -> Paste Special -> Conditional Formatting Only
Step 4: Now the selected cell(s) will have conditional formatting as well and the sheet will become:
Method 2: Copy Conditional Formatting using Paint Format
Paint format is referred as Format Painter in MS Excel. Here in sheets, it is called Paint Format. Paint Format allows user to easily paint the format from cell(s) to other cell(s). It can be used to copy the conditional formatting and applying it onto required cells.
Its icon can be found on top left corner of Google Sheets as looking like a paint roller.
Step 1: Select the cell(s) from which conditional formatting is to be copied from.
Step 2: Click on the Paint Format Icon. You will see that a dashed line appears on the selected cell as below:
Step 3: Now drag the mouse over the set of cells you want to copy the format onto.
It will automatically copy the conditional formatting to those cells as you see below:
Copying the conditional formatting by the use of Format Painter not only copies the conditional formatting to the destination cells but also copies the original formatting of cell. For instance, if the cell(s) containing the conditional formatting uses some cell formatting as specific text size, color or cell border, etc. then it will also be copied to destination cells. If you do not wish to copy the cell formatting and conditional formatting only then the best practice is to use the method 1 (Using Paste Special).
Applying conditional formatting to multiple cells is made very easy through the use of Paste Special and Paint Format tool and takes very less amount of time than manually applying conditional formatting. The steps are briefly elaborated above. If you have any issue with copying conditional formatting or have any question, feel free to ask in comments section below.
Thanks for reading!