Google spreadsheet: Remove conditional formatting, but keep the format

Question

Is there a way to remove the conditional formatting from cells, while keeping the applied format intact?

Publias

@AbdulHameed your link only addresses removing the conditional formatting, which also removes the applied format... – Publias

Answer 1

The only way I've found is:

• Copy the cells.
• Clear formatting on the cells (ctrl + \)
• Paste special -> Paste format only (paste over the said cells).

Nippers

Answer 2

• Copy a cell that has no conditional formatting
• Select the cells with de conditional formatting you want to delete
• Click Edit > Paste special > Paste conditional formatting only.

Adila

Answer 3

Copy the required range to another sheet and paste special, values only. Then immediately paste special again, formatting only.

If you want to keep the work in the same place on the same sheet, just cut instead of copy, and then do the above paste operations to the same place.

The advantage of the first method is that by creating a copy you can't accidentally mess up your original and can always replace this when you are satisfied with the result.

Anam

Answer 4

Yes, to remove individual conditional formats while leaving other formatting unchanged, in Google Sheets:

• If the conditional format pane on the far right is present, close it by clicking the X in the top right of the pane.
• Highlight the range of cells (or select a single cell if you want to remove conditional format for only 1 cell) that have conditional formatting applied. For this to work, at least one of the cells you've highlighted must have conditional formatting defined.
• Right click the highlighted cells and select "Conditional Formatting" from the context menu.
• A vertical panel on the right will appear with a list of conditional format rules that are present in the highlighted range.
• Hover the cursor (do not click -- just hover) over the rule you want to delete. As you hover a trash can icon will appear.
• Click the trash can icon to remove that particular conditional format.

Weatherboarding

3/16/2017: Added a new top bullet, the absence of which may be why this answer was down-voted twice. The steps work, but if in the Google Sheet the Conditional formatting pain on far right is already present, the trash can will not appear. So per the new first bullet in the answer above, if present, close that Conditional Formatting pain on right before doing the rest of the steps. – Weatherboarding

I found that when I tried clicking the trash can, it deleted the rules for the entire sheet, so I had to manually edit the cell range for each conditional formatting rule to remove the offending columns.... I didn't down-vote; this is just feedback. :) Hope it helps! – Sprayberry

Note that the Trash icon can almost be invisible depending on your screen and brightness settings. I just ran into this issue and can confirm that this works. – Kent

No list of rules appears for me. Nothing to hover over. No trash can to click on. (Is this because I only have one rule set up so far?) I'll experiment and report back.... I'm back. That was quick. Yeah, once I added another rule, it gave me a list of them (with trash can to click on). – Alliteration

This is not what is being asked. This removes the applied format too. – Asclepius

Answer 5

I know this is an old question, but I tried the approaches suggested. They don't work. The conditional formatting 'çonditions' are also copied across.

My Solution is to copy and paste into excel and then copy the values back into google sheets. When pasting into excel the 'conditions' don't get copied over.

Shrewmouse

Answer 6

Some Redditor saved the day!

Here's the link

function clearFormatting () {
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var ss = s.getActiveSheet();
  ss.clearConditionalFormatRules();
}

Yoho

This does not do what the OP asked, which is to leave the formatting the conditions had applied while removing the logic of the conditional formatting. – Balk

Answer 7

To add to the most-voted answer, you can actually do this on an entire sheet, in case that's helpful. (Mac keys shown here)

Command-a to Select All
Command-c to Copy
Command-\ to Clear Formatting
Command-Option-v to Paste Format Only

Diabolo

Answer 8

This is an old question, but none of the solutions above really worked for me. Copying, clearing the formatting, and pasting simply added the conditional formatting back in.

A work around I found works similarly, but by deleting the conditional formatting actions individually did it somehow. Additionally, for my situation I also created a copy of the file and renamed it to keep the original file and all the conditional formatting for future builds.

How I was able to get it to work:

FILE - MAKE A COPY
RENAME THE FILE
EDIT - COPY (ENTIRE SPREADSHEET)
FORMAT - CONDITIONAL FORMATTING
DELETE ALL RULES INDIVIDUALLY
EDIT - PASTE SPECIAL - FORMAT ONLY

In the end I was left with all the previous data and all the conditional formatting minus the conditional rules. Hope this helps someone!

Yoon

Answer 9

Without a script is possible with a hack: copy into say Word and back again.

Gremial