CODEWITHSUNDEEP
excel
ftkg
ftkg

Reputation: 1762

How to copy the conditional formatting without copying the rules from a Conditional Formatted cell?

I have a row of data with conditional formatting in it.

I need to copy this row to another row. This new row must be identical (values, formats), but static, i.e. without the rules. If I copy and paste the row and clear the rules, the formatting from the CF rule vanishes.

Is it possible?

Upvotes: 34

Views: 95093

Answers (8)

Andrei Pop
Andrei Pop

Reputation: 136

You can do it using the "Office Clipboard" Pane ...

Copy the formatted region, then go to the "paste" page and open the Office Clipboard Pane, it is the small button in the right lower corner here: Office Clipboard pane

Then click on the "copied" content and it will paste it with formats and without conditional formatting. Very hidden, and not intuitive at all

Source

Upvotes: 6

Chris Brown
Chris Brown

Reputation: 21

I found a dead easy solution to this problem.

  1. Copy the cells with the results of conditional formatting that you want to duplicate in another place.

  2. Paste into an OpenOffice Calc spreadsheet (I used build 4.1.5) as Formatted - RTF. The formatting is still there, but OpenOffice apparently discards the conditions that created the formatting in the first place.

  3. Now simply copy and paste into Excel.

  4. Crack open the champagne.

This works with Excel files thousands of lines long.

Upvotes: 2

Laurita
Laurita

Reputation: 1

I found an easy solution for this! Once you have added your conditional formatting, simply add Filtering to your table, then click the filter (down arrow) on any of the Header columns > click Filter by Color > choose color > select all > choose same background color as background > Remove Filtering. Then you will go back to Conditional Formatting, remove the rule and you will still have the colors on the cells that the conditional formatting did, just without all of the extra load of actually having the Conditional Formatting rule set. I hope that helps and isn't too confusing. I forgot to mention that this specific example was related to background colors, but you can also filter on text colors as well.

Upvotes: 0

Hallel
Hallel

Reputation: 11

Copy and paste to Google Sheets, then copy and paste back into Excel.

(I had to resort to this because the copy/paste to Word didn't work for me)

Upvotes: 1

Doug Douglas
Doug Douglas

Reputation: 9

The Excel copy / Word paste/ copy/ Excel paste again is the only quick way of doing this.

Upvotes: 0

victor zubiaga
victor zubiaga

Reputation: 97

First copy then look for "paste all" option. It worked for me.

Upvotes: 0

Henry Mui
Henry Mui

Reputation: 606

Just discovered an easy way: Copy the table to MS Word and copy it back to excel :D

It worked perfectly for me.

Upvotes: 59

buradi
buradi

Reputation: 19

  1. Press CTRL+C on the cell(s) you want to copy.
  2. Press ALT > E > S > U on the cell(s) you want the value(s) to be pasted.

The value(s) and the formatting will be pasted, but not the conditional formatting rule(s).

Upvotes: -3

Related Questions