6

After switching from Excel 2007 to Excel 2010 I've started to receive duplicates of conditional formatting rules. For example, I have a rule which colors the cell when it detects that cell contains the word "hello". When I copy this cell to other cells (which also contain the same rule), sometimes I receive duplicates. Those duplicates really slow down Excel.

So my question is, is there any way to disable copy/paste of conditional formatting? I want to copy/paste all content except the conditional formatting, including all formulas, values, and so on.

I don't know how to reproduce that problem, it occurs only occasionally and I work with Excel a lot.

Improve this question
4
  • Copy/paste using destination formatting? It's of course an extra step for every paste..
    – erikxiv
    May 1, 2012 at 19:08
  • actually, usually I don't see what kind of paste I make, I work a lot with excel, so sometimes the easiest thing to do is just copy a lot of cells with conditional formatting, what do you mean by extra step?
    – bigMir
    May 1, 2012 at 19:15
  • You could paste special and choose formulas, or afterwards clear formatting (both will avoid conditional formatting to be copied)
    – erikxiv
    May 1, 2012 at 19:18
  • I know about it, but it will be tedious to do it every time
    – bigMir
    May 1, 2012 at 19:30

6 Answers 6

Reset to default
5

Excel has had problems with copying conditional formats correctly since Excel 2007. If you didn't notice any problems then you were lucky. :-)

The easiest way to not copy conditional formatting is to not copy any formatting at all. i.e. to use Paste Special and paste Formulas or Values. This is actually quite fast if you use the old keyboard menu shortcuts.

  • To Copy: <Ctrl> + c
  • To Paste everything <Ctrl> + v
  • To Paste Special Values: <Alt>, e, s, v, <Enter>
  • To Paste Special Formauls: <Alt>, e, s, f, <Enter>

I remember the Paste Special shortcuts by way of the old Excel menu: E dit -> Paste S pecial -> V alues and E dit -> Paste S pecial -> F ormulas

Improve this answer
2

There's now a "Merge Conditional Formatting" option in the "Paste Special" menu and this pastes everything you want (Data Validation, formatting, all the awesomeness) without creating duplicate conditional formatting rules!

enter image description here

Improve this answer
1

If you are able to define all necessary conditional formatting rules at Worksheet level then you may not need conditional formatting at cell level. This makes it possible to copy and paste cells and rows without any changes in conditional formatting rules. Look at these rules from one of my sheets:

ScreenShot of Excel Rules Dialog ]

As you see these are defined as rules for "This Worksheet", formulas are defined manually and areas to which they apply are also defined manually. In result if you look at rules for "Current selection" you will not see anything else. This means that all your rules are at worksheet level, there are no rules at cell level, so no rules are affected by copy/paste operations made on cells. Rules stay the same unless you manually edit them, and they are working as expected.

If you are curious - these conditional formatting rules are from an IP address plan. Here is the effect from those rules on the sheet:

IP Address Plan sample worksheet

]

Improve this answer
1
  • 2
    doesn't prevent the rules being messed up by Copy & Paste. I define all my rules at Worksheet level, then they all get messed up.
    – go2null
    Mar 12, 2021 at 8:54
0

For the destination cells that have the same conditional format rules defined, first select these cells/cell range and clear all rules by by going to Home > Conditional Formatting > Clear Rules from selected cells

enter image description here

Then, copy/paste your entire cell range with conditional formatting as desired.

EDIT: Alternatively, you can record a macro that copies only values from your source cells to your (conditionally formatted) destination cells. That's the quickest way for you to mass copy/paste multiple cells without duplicating formatting.

Improve this answer
2
  • 1
    thank you very much for the answer but it will be tedious every time to clear all rules and paste one more time (better than nothing anyway), I want to find a way disable conditional formatting copy or any other way, just to make one operation and get rid of that problem
    – bigMir
    May 1, 2012 at 19:29
  • @bigMir Are you willing to use a macro? It should be a one-time setup after which you can just use a shortcut key to copy-paste without any formatting (just values, keeping destination formatting)
    – prrao
    May 1, 2012 at 19:50
0

The best answer I can find does not work with CUT, it must be Copy, then Paste Special, and use the third icon on the top row, which will paste "Formulas & Number Formatting". Then delete or clear the original info.

enter image description here

Improve this answer
0

If you are able to make the file a macro enabled file, right click on your tab and select "VBA code" then copy the following code into each worksheet in the document you where you need to prevent your conditional formatting from "fragmenting".

'This code substitutes "paste while merging conditional formats" any time paste is selected (no matter which type of paste is selected) from a dropdown menu AND from ctrl+V

Private Sub Worksheet_change(ByVal Target As Range)
If Application.CutCopyMode = xlCopy Then
Application.EnableEvents = False
Application.Undo
Target.PasteSpecial Paste:=xlPasteAllMergingConditionalFormats
Application.EnableEvents = True
End If
End Sub

'script to turn off drag and drop in sheet
Private Sub Worksheet_Activate()
Application.CellDragAndDrop = False
End Sub

'script to turn back on drag and drop when leaving sheet
Private Sub Worksheet_Deactivate()
Application.CellDragAndDrop = True
End Sub
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .