Check if cell or range is blank or not in Excel – easy guide
Dealing with blank cells or ranges in Excel can be a crucial part of data management and analysis. Whether you need to identify, fill, or skip blank cells, understanding how to check for them efficiently is essential. This guide provides simple yet effective methods to determine if a cell or range is blank in Excel, with practical tips to enhance your data handling skills.
Check if a cell is blank
This section is divided into two parts for straightforward identification of blank cells in the specified range. The first part demonstrates how to return specific text when a blank cell is encountered, while the second part shows how to halt formula calculations when encountering a blank cell.
If a cell is blank then return specific text
As shown in the shipment table below, if the item is delivered on time, it will be marked as Delivered in the Shipping Status column. If delayed, the Shipping Status remains blank. To identify the blank cells in this column to check for the delay delivery, you can do as follows.
Select a blank cell to output the result (such as I2 in this case), enter the following formula and press the Enter key. Then select this result cell and drag its Fill Handle down to get the rest of the results.
=IF(ISBLANK(F2), "Delay", "Completed") 
- In this formula, F3 is the cell I will check if it is blank. "Delay" indicates that if F3 is blank, the formula will return Delay as the result. Conversely, "Completed" means that if F3 is not blank, the formula will return Completed. You can modify the cell reference and specified texts according to your needs.
- If you want to keep the result cell blank when a blank cell is encountered, clear the first specified text in the formula, leaving only the double quotes. Such as:
=IF(ISBLANK(A2), "", "not blank") - If the cells appear to be empty but contain non-visible characters such as spaces or other non-printable characters, then these cells will also be treated as non-blank cells. To treat these cells as blank cells, you can use the following formula:
=IF(LEN(TRIM(A2))=0, "blank", "not blank")
Struggling to identify blank cells with spaces?
If a cell is blank then stop calculating
In certain situations, when a formula encounters a blank cell, it may return an error or unexcepted result, depending on the specific function and settings applied in the sheet. In the example below, I'm using the formula =(C2-B2)/B2 to calculate the percentage change between the previous month and this month for different products. However, when the source cell is blank, the formula produces a #DIV/0! error. This section will guide you on preventing this error when dealing with blank cells.
Select a cell (such as D2 in this case), enter the formula below and press Enter. Select this result cell and drag its Fill Handle down to get the rest of the result.
=IF(ISBLANK(B2), "", (C2-B2)/B2)
As you can see from the above results, all the error values have disappeared although there are blank cells.
Check if a range is blank
If you want to check whether a certain range is blank, the formula in this section can do you a favor.
Here I will take the range G1:K8 as an example. To check whether this range is blank or not, do as follows.
Select a blank cell to output the result, enter the following formula and press the Enter key.
=IF(SUMPRODUCT(--(G1:K8<>""))=0,"It is blank","It is not blank")
- This formula checks if the range G1:K8 is blank. If the range is blank, it returns "It is blank" as the result. If the range is not blank, it returns "It is not blank". You can modify the cell reference and specified texts according to your needs.
- If you don't want to specify texts and just return TURE or FALSE, use this formula:
=SUMPRODUCT(--(G1:K8<>""))=0This formula returns TRUE if the range is empty, otherwise it returns FALSE. - If the cells appear to be empty but contain non-visible characters such as spaces or other non-printable characters, then these cells will also be treated as non-blank cells. To treat these cells as blank cells, you can use the following formula:
=IF(SUMPRODUCT(--(TRIM(G1:K8)<>""))=0,"It is blank","It is not blank")or=SUMPRODUCT(--(TRIM(G1:K8)<>""))=0 - To check if multiple ranges are empty, try this formula:
=IF(AND(SUMPRODUCT(--(A7:C9<>""))=0, SUMPRODUCT(--(M2:P2<>""))=0),"Empty","has value")
Tips: Highlight blank cells
Highlighting blank cells helps easier identify and address blank cells in large datasets. This section will explore how to visually highlight blank cells in your data set using Excel’s Conditional Formatting.
Step 1: Select the range where you want to highlight the blank cells.
Step 2: Open the New Formatting Rule dialog box
Under the Home tab, click Conditional Formatting > Highlight Cells Rules > More Rule.
Step 3: Create a conditional formatting rule
In the New Formatting Rule dialog box, you need to configure as follows.
- Select Blanks from the Format only cells with drop-down list.
- Click the Format button to specify a fill color for the blank cells.
- Click the OK button to save the rule.
Result
All blank cells in the selected range are highlighted with specified fill color.
In summary, this guide teaches efficient ways to check and manage blank cells or ranges in Excel. Whether you're a novice or an experienced Excel user, mastering these simple yet powerful methods will boost your productivity and accuracy when working with data. For those eager to delve deeper into Excel's capabilities, our website boasts a wealth of tutorials. Discover more Excel tips and tricks here.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!