Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
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
up vote 2 down vote accepted

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

share|improve this answer

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.

share|improve this answer
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

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

share|improve this answer

You must log in to answer this question.

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