Ask Your Question

Conditionally formatted cells do not work when I paste data into them.

asked 2016-11-25 14:17:46 +0100

Mickledo gravatar image

updated 2016-11-25 14:41:16 +0100

I have conditional formatting set up to colour the cell backgrounds of credit payments in my bank statements. When I download my latest bank statements (CSV) and paste the data into my spreadsheet (paste special) the new positive cells do not get conditionally formatted and I end up having to manually colour the cells using the clone formatting paintbrush to achieve the desired effect or reapplying conditional formatting to the whole column. Does anyone know where I'm going wrong please?

edit retag flag offensive close merge delete


How do you open your CSV file?

kompilainenn gravatar imagekompilainenn ( 2018-08-14 16:30:55 +0100 )edit

Did you use Paste special->Numbers?

kompilainenn gravatar imagekompilainenn ( 2018-08-14 16:49:45 +0100 )edit

3 Answers

Sort by » oldest newest most voted

answered 2018-08-14 13:45:08 +0100

Hi guys, I've been looking around and I think this is a small bug.

I've noticed that when I have a multi-row and multi-column range in conditional formatting, whenever I copy-paste cells within this range, the conditional formatting range is broken up. It does not break anything, but is very messy when you want to edit the conditional formatting.

I think your problem is related to this. When you paste a formula from a cell without conditional formatting, or with a different type of conditional formatting, the formatting comes with the cell, so the original range is broken up.

I can reproduce this behavior reliably in version

edit flag offensive delete link more


There is Menu/Edit/Paste special [Ctrl+Shift+V] to paste without the format.

m.a.riosv gravatar imagem.a.riosv ( 2018-08-14 20:08:12 +0100 )edit

answered 2016-11-29 10:25:41 +0100

m.a.riosv gravatar image

Be sure,you are pasting numbers not numbers as text.

edit flag offensive delete link more

answered 2016-11-29 05:49:27 +0100

calcUser gravatar image

Any chance you are in Design mode? not sure if that can cause this but it might. also, I would review the "paste special" dialog closely for clues, maybe just try regular paste...

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-11-25 14:17:46 +0100

Seen: 97 times

Last updated: Aug 14 '18