How to mark spaces or errors in Microsoft Excel

Seeing things in a table can be much faster using colors that pop off the screen. With conditional formatting in Microsoft Excel, you can make it easier to find empty cells or formula errors.

How to automatically mark blank cells in Excel

When you have a table full of data that you expect to fill every cell, it’s easy to overlook cells that are left blank. And a lack of data can be just as damaging as wrong data. Here’s how to see blank cells using conditional formatting.

Open the sheet and select the cells that you want to apply the formatting to. Go to the Home tab and in the Styles group of the ribbon, click Conditional Formatting. Select “New Rule”.

In the New Formatting Rule window that appears, under Choose Rule Type at the top, select Format Only Cells That Contain.

Click Format Cells That.  contain

At the bottom of the drop-down box, select Format cells only with “Spaces”. Then click Format to the right of the preview to choose how you want the blank cells to be formatted.

Select spaces and click Format


In the Format Cells window, use the font, border, and fill tabs above to choose your formatting. Click OK. “For ours example, let’s use Fill to color our empty cells light yellow.

Choose your formatting

You are back in the New Formatting Rule window, where you can see a preview of the formatting for empty cells. When you’re happy with this, click OK to apply the conditional formatting.

Confirm and click OK

You should then see all of the blank cells in the area you selected highlighted with the formatting you selected.

Conditional formatting for blank cells in Excel

How to automatically highlight errors in Excel

Even if Microsoft Excel does a decent job of calling out bugs, they may not get noticed if you have a big leaf to flip. To make sure you see the errors quickly, conditional formatting can save the day.

You actually follow the same procedure that you used to highlight spaces in the previous section, with one difference.

Go to the Home tab, click Conditional Formatting, then choose New Rule.


At the top of the New Formatting Rule window, select Format Only Cells That Contain. But this time, select “Error” in the Format cells only using drop-down box below.

Choose Failure

Then click “Format” to select the formatting. Therefore example, adjust the font options to make the cells with errors bold and red. After choosing formatting, click OK, then click OK again to apply the rule.

Confirm and click OK

Now these errors will disappear from the screen!

Conditional formatting for errors in Excel

You can do this type of formatting in Microsoft. use Excel many other things to do as well. Check out how to use conditional formatting to highlight a line based on its content or how to use a progress bar in. create Excel.

Related Posts