Skip to main content

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") 

Notes:
  • 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?
Try Kutools for Excel's Remove Spaces feature. It can eliminate leading, trailing spaces in a range, ensuring the cell remains truly empty, all in just two clicks.
Want to access this feature? Download Kutools for Excel now!

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.

Note: In this formula, B2 is the cell I will check if it is blank, (C2-B2)/B2 is the formula I will use to calculate the percentage change. Please change these variables as you need.

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")

Notes:
  • 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<>""))=0
    This 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.

  1. Select Blanks from the Format only cells with drop-down list.
  2. Click the Format button to specify a fill color for the blank cells.
  3. 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

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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...

Description


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!
Comments (8)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Hello, what if you have multiple ranges to include in the formula? i.e. A2:D2 and M2:P2, When I add in the 2nd range the formula does not work...
This comment was minimized by the moderator on the site
Hi Nicholas Haughn,

The following formula can help you. Please give it a try. Thank you.
=IF(AND(SUMPRODUCT(--(A2:D2<>""))=0, SUMPRODUCT(--(M2:P2<>""))=0),"Empty","has value")
This comment was minimized by the moderator on the site
Hola,
Me gustaría cambia los resultados de VERDADERO/FALSO por otras palabras, es posible?
muchas gracias
This comment was minimized by the moderator on the site
Hi Paula,
If you want to display a specific result other than TRUE or FALSE, please enclose the formula in an IF function. Such as:
=IF(SUMPRODUCT(--(G1:K8<>""))=0, "Yes", "No")
This comment was minimized by the moderator on the site
Muchísimas gracias!!
Rated 5 out of 5
This comment was minimized by the moderator on the site
Hi

Thanks for this. It is what I needed. I am curious what is the significance of the '--' in the formula?
This comment was minimized by the moderator on the site
Hi Joe Shaer,
The double dash is used for converting a list of boolean (TRUE, FALSE) values to ZEROs and ONEs, which is a useful technique in many advanced formulas that work with cell ranges.
This comment was minimized by the moderator on the site
Thanks for this formula. 😊

To make it even more complete I would recommend to use the trim function on the range to eliminate white spaces too:
=SUMPRODUCT(--(TRIM(G1:K8)<>""))=0

Cheers, Dirk
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations