Skip to main content

Kutools for Office — One Suite. Five Tools. Get More Done.

Check if cell or range is blank or not in Excel – easy guide

Author Siluvia Last modified

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.

A screenshot of Excel showing blank cells in a range of data and displayed as 'Delay


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

A screenshot of Excel showing a table with blank cells indicating delayed shipments

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

A screenshot of Excel showing the result of an IF formula checking for blank cells and returning 'Delay' or '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 the "Remove Spaces" feature of Kutools for Excel. 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!
A screenshot of the Kutools for Excel Remove Spaces feature to remove leading and trailing spaces from cells

If a cell is blank then stop calculating

In certain situations, when a formula encounters a blank cell, it may return an error or unexpected 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.

A screenshot of Excel showing a formula error (#DIV/0!) caused by a blank cell

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)

A screenshot of Excel with a formula to prevent errors when blank cells are encountered, showing correct percentage change calculation

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

A screenshot of Excel showing a formula to check if a specific range (G1:K8) is blank or not

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 "TRUE" 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 to more easily 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".

A screenshot of the Excel Conditional Formatting menu showing the option to highlight blank cells

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.
    A screenshot of the New Formatting Rule dialog box in Excel to highlight blank cells
Result

All blank cells in the selected range are highlighted with specified fill color.

A screenshot showing highlighted blank cells in Excel after applying conditional formatting


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
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

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!

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

Excel Word Outlook Tabs PowerPoint
  • All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
  • One installer, one license — set up in minutes (MSI-ready)
  • Works better together — streamlined productivity across Office apps
  • 30-day full-featured trial — no registration, no credit card
  • Best value — save vs buying individual add-in