3

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.

  • Copy/paste using destination formatting? It's of course an extra step for every paste.. – erikxiv May 1 '12 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 '12 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 '12 at 19:18
  • I know about it, but it will be tedious to do it every time – bigMir May 1 '12 at 19:30
4

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

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

]

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.

  • 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 '12 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 '12 at 19:50
0

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.