Excel - - Highlight cell if no data

October 2016


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.

Related :

This document entitled « Excel - - Highlight cell if no data » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.