A few words of thanks would be greatly appreciated.

Excel - - Highlight cell if no data

Ask a question


Please could somebody help me with this problem. I have a spreadsheet and i would like make some cells in a row go yellow if they have no data in them. The data will either be text or a date. This is to highlight if ALL information on a spreadsheet row has NOT been entered or has been left out (the row info will be entered over a period of time)

If this cannot be done do you know how to just make one cell turn yellow if there is no date entered?


Assume the cell you want conditional formatting on is cell D9.
  • Select cell D9.
  • Select Format > Conditional Formatting... menu option
  • In the Conditional Formatting dialog, change the 1st drop down from "Cell Value Is" to "Formula Is"
  • In the formula enter "=IF(LEN(TRIM(D9)) = 0, TRUE, FALSE)" without the quotes.
  • Then select the Format button and select the Patterns Tab. Select the color you want for the background of the cell.

If you want to highlight the whole row based on the value of this cell, change D9 to $D9. Then copy cell D9, select the row, right click and "Paste Special...". Select Formats checkbox and click Ok.

To apply this to all of the other Rows, select row 9 and copy it. Select all of the rows and "Paste Special...". Select Formats checkbox and click Ok.


Thanks to TR for this tip on the forum.
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team

Published by aakai1056. Latest update on December 11, 2009 at 04:52 AM by aakai1056.

This document, titled "Excel - - Highlight cell if no data," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).
Add comment


Respond to Anonymous User