Ask Your Question

Apply conditional formatting to empty cells

asked 2017-07-30 02:50:27 +0200

sleeperstakes gravatar image

I have a set of values each with different coloured backgrounds in one coloumn that i want to copy and paste over to another column, but when i delete the values after copying and pasting them, the coloured background stays. I want to know if there is way to apply conditional formatting to empty cells so that they can be changed to white, non transparent, to replace the coloured background (or something with the same effect). I tried doing it with "contains" then "", and then a custom style, but when i opened the condition menu up again it just went back to the default style and kept the background colour. I tried "Contains" then "", which didn't work, then "Not Contains" then put in all the possible values with coloured backgrounds in perenthesies which didnt work. anyone got any ideas?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-07-30 05:04:45 +0200

librebel gravatar image

Hello @sleeperstakes,

For example to test if cell F1 is empty,

  1. Open the Conditional Format dialog,
  2. Set the condition to : "Formula is"
  3. Set the formula to : "ISBLANK(F1)"
edit flag offensive delete link more


And how do you make this work for a range of cells?

einpoklum gravatar imageeinpoklum ( 2019-08-26 16:52:20 +0200 )edit

3a) Set the formula to "ISBLANK($Sheet1.$A1), or whatever is the top-left-most cell in the range of cells. $ signs indicate the portions of the cell identifier that do not change as the formatting algorithm applies the conditional formatting throughout the workbook. In this case, the worksheet name and the column letter stay the same (hence the $ signs) while the row number is variable, because the hypothetical range that I'm using in my example consists of many rows in a single column on a single worksheet.

fyrstormer gravatar imagefyrstormer ( 2019-12-03 05:40:38 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-07-30 02:50:27 +0200

Seen: 3,918 times

Last updated: Jul 30 '17