Copy Cell Values Without Conditional Formatting in Google Sheets

Published on

Duplicate or unnecessary conditional formatting can make your Google Sheets sluggish. 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 is copied. This includes values, conditional formatting, regular formatting, data validation, and formulas. When you paste it into another cell, all these elements get transferred.

For example, if cell A1 contains a conditional formatting rule, copying and pasting its content into cell B1 will transfer the conditional formatting along with the value, formula, or data validation from A1.

Furthermore, if B1 already has a conditional formatting rule, pasting from A1 will not override it. Instead, it will keep the existing rule(s) plus the copied rule.

Do you know how to stop Google Sheets from copying conditional formatting? Check out the tips and examples below.

How to Copy Cell Values Without Conditional Formatting in Google Sheets

You can’t copy a cell’s content in different ways since there is only one copy option. However, you can paste the content in different ways. So, the key to copying a cell value without conditional formatting lies in how you paste the copied content.

Let’s see an example.

Assume that I have applied a conditional formatting rule in cell C1: if the value is “Yes,” the cell is highlighted in light red. To set this up, navigate to cell C1, click Format > Conditional formatting, select Text is exactly, and enter “Yes” in the field below.

If you copy cell C1 and paste it into another cell, the same conditional formatting rule will be applied to the new cell.

Here’s how to copy a cell value and paste it without carrying over the conditional formatting rule:

  1. In cell C1, press Ctrl+C (Windows) or ⌘ + C (Mac) to copy the cell’s content.
  2. In cell D1 (or any target cell), right-click and select Paste special > Formula only.
Paste formulas to copy-paste without conditional formatting

This will paste just the value or formula from the copied cell, excluding the conditional formatting.

If you don’t want the formula and only need the value, choose Paste special > Values only instead of formulas.

Alternative Workaround

If you frequently need to copy cell values without conditional formatting, you can clear conditional formatting separately:

  1. Paste the copied content normally.
  2. With the pasted cell still selected, go to Format > Conditional formatting and click Remove rules (You will see a trash icon or “Remove rule” when you hover your mouse over the rule).

This method removes any inherited formatting from the copied cell. However, be careful not to delete any rules specifically set for the paste range, if any.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

2 COMMENTS

  1. 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.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.