Highlight empty cells
Table of Contents
1. Highlight empty cells
The image above shows a data set containing a few random blanks. A conditional formatting formula highlights the empty cells yellow.
Conditional formatting formula:
1.1 Explaining conditional formatting formula
Step 1 - Specify relative cell reference
The image above shows what the CF formula looks like in each cell, the actual cells do not contain this formula.
The cell range we want to highlight empty cells in is B3:F13 so the cell we must highlight first is the cell in the upper left corner in order to highlight the correct cells.
Cell B3 is a relative cell reference and changes in the cell range we want to format. If we started with B4 cell B3 will be highlighted if cell B4 is empty and we don't want that. We want to highlight the empty cell.
Step 2 - Create a logical expression
The equal sign is a logical operator and the result is a boolean value TRUE or FALSE.
B3=""
becomes
106=""
and returns FALSE. Cell B3 is not highlighted.
1.2 How to apply conditional formatting
The conditional formatting formula applied to cell range B3:F15 highlights all blank cells yellow. Here is how I did it:
- Select cell range B3:F13.
- Go to tab "Home" on the ribbon.
- Press with mouse on the "Conditional Formatting" button.
- Press with mouse on "New Rule..".
- Select "Use a formula to determine which cells to format".
- Type =B3="" in field "Format values where this formula is true:".
- Then press with left mouse button on "Format..." button.
- Go to tab "Fill".
- Pick a color.
- Press with left mouse button on OK button.
- Press with left mouse button on OK button to return to Excel.
1.3 Formulas returning nothing.
Note, a cell is conditionally formatted if it contains a formula that returns nothing "". The cell is not highlighted if the formula returns a value.
The image above demonstrates a formula in cell C13 that returns nothing "", the cell is highlighted. Cell C13 is selected and the formula is displayed in the formula bar.
1.4 Cells with hidden values
The image above shows an empty cell E3 but it is not highlighted, cell E3 is actually not empty, it is only formatted to hide values.
Go to the "Format Cells" dialog box to inspect cells that should have been highlighted. The dialog box above shows cell E3 with a custom cell formatting. ;;; hides all values in a cell, select category: General to show the value again.
2. Highlight error cells
The image above demonstrates a conditional formatting formula that highlights cells containing an error.
Conditional formatting formula:
2.1 Explaining formula
Step 1 - Specify cell reference
The conditional formatting formula is applied to cell range B3:F13, the cell reference must point to the upper left cell which is cell B3 in this example.
B3 returns 106.
Cell reference B3 is a relative cell reference, it changes accordingly when the parser goes to the next cell. It is possible to use absolute cell references, however, this will not work in this example.
Step 2 - Identify error value
The ISERROR function returns TRUE if the value is an error value.
ISERROR(value)
ISERROR(B3)
becomes
ISERROR(106)
and returns FALSE. Cell B3 is not highlighted.
Get Excel *.xlsx file
Related articles
Slicers let you control data displayed in a chart, simply press with left mouse button on a button to quickly […]
In this post I am going to try to explain formula basics in conditional formatting. It is really good if […]
This article demonstrates how to apply Conditional Formatting formula to a cell range, it finds cells that are in close […]
The conditional formatting formula applied to cell range F3:F13 highlights all cells containing text. Here is how I did it: […]
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]
This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]
The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]
This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Conditional […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]
Question: I have a list that I keep adding rows to. How do i create a border that expands as […]
The image above shows you how to highlight rows with multiple criteria using OR logic. The criteria are found in […]
In this article, I will demonstrate how to search a table using conditional formatting. The criteria highlight matching column and […]
Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]
The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, […]
A conditional formatting formula highlights values in column B that also exist in column D. =COUNTIF($D$3:$D$7,B3) The same thing happens […]
In this post I am going to try to explain formula basics in conditional formatting. It is really good if […]
The image above shows conditional formatting highlighting unique distinct values, duplicates are not highlighted. Conditional Formatting Formula: =COUNTIF($B$3:B3, B3)=1 The […]
This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
