Duplicate or unwanted conditional formatting can make your Google Sheets resource hungry. To prevent duplicate formatting rules, you should know how to copy cell values without conditional formatting in Google Sheets.
When you copy a cell in Google Sheets, everything in that cell copied. It includes values, conditional formatting, formatting, data validation, and formula. Paste it in another cell, as you can see, everything copied from the source gets pasted.
You May Like: Data Validation – How Not to Allow Duplicates in Google Sheets.
If cell A1 contains a conditional formatting rule, when you copy and paste that cell content in cell B1, the conditional formatting will also get passed along with the value/formula/data validation in cell A1.
Further, if cell B1 has already a conditional formatting, the new copied rule causes duplicate conditional formatting rule in cell B1.
Do you know how to prevent conditional formatting rule copied in Google Sheets? See that tips with examples below.
How to Copy Cell Values Without Conditional Formatting in Google Sheets
I have set a conditional formatting rule in cell C1. The cell C1 contains the values Yes/No. If the value is “Yes” I have that cell color set to Light Red else No formatting.
If I copy this cell to D1 what happens? As you may know, the conditional formatting also gets copied. Here see how to copy cell values without conditional formatting.
1. In cell C1, press Ctrl+C to copy (shortcut key for Windows).
2. In Cell D1, press Ctrl+Shift+V or right click on cell D1, select Paste special and apply paste values only.
This way you can copy cell values and paste it without Conditional Formatting in Google Sheets.
How to prevent conditional formatting rule copied when copying a formula?
Here again, the steps are almost the same.
1. Ctrl+C to copy.
2. Alt+E+S+F to paste formulas only.
Follow the above tips when you want to copy cell values without Conditional Formatting in Google Sheets. Enjoy!
Additional Resources:
1. Highlight Matches or Differences in Two Lists in Google Sheets.
2. Highlight Duplicates in Single, Multiple Columns, All Cells in Google Sheets.
3. Change the Tick Box Color While Toggling in Google Sheets.
Hello,
I have a cell that I want to drag and copy to hundreds of other cells within the same row.
I want to copy the formula only and prevent the conditional formatting colors.
Because there are so many cells, I don’t want to use “Alt+E+S+F” for every cell.
Is there an easy way to drag and copy multiple cells and apply the “formula only” on that cell?
Thank you in advance!
Hi, Angela,
First, copy the content/formula (Ctrl+C).
Then select the cells you want to paste, right-click, and select paste special > formula only.
When you want to select distant cells, do as follows. Press and hold the Ctrl key and click the cells one by one.