Ask Your Question

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

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

Mickledo gravatar image

updated 2020-08-11 17:59:39 +0200

Alex Kemp gravatar image

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 reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-08-11 18:00:11.551210


How do you open your CSV file?

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

Did you use Paste special->Numbers?

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

3 Answers

Sort by » oldest newest most voted

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

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 +0200 )edit

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

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 +0200

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

Question Tools

1 follower


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

Seen: 172 times

Last updated: Aug 14 '18