Inside The Web
    Facebook Twitter Instagram
    Inside The Web
    • Office
      • Excel
      • Ms Word
      • Gmail
    • Productivity
      • Time Management
      • Task Automation
    Inside The Web
    Home»Excel»How to Check if a Cell is Blank or Not in Excel

    How to Check if a Cell is Blank or Not in Excel

    AsmiBy AsmiMarch 5, 2023 Excel 5 Mins Read

    Sometimes, a cell that appears to be blank actually holds hidden characters in them. Excel registers such hidden characters as existing values in the cell. This can create a range of issues including,

    • Formula Errors: You will get inaccurate results when using count functions like COUNTA. The COUNTA function counts all non-empty cells. As the cells with hidden characters aren’t technically empty, the COUNTA function makes a count.
    • Errors in Sorting Cells: You cannot accurately sort empty and non-empty cells according to the content visible in the sheet.
    • Filtering Cells: Excel labels empty cells as (blanks) while using the Filter tool. However, when you have hidden characters embedded in the cells, they will not be grouped together with the empty cells.
    • Issues in Analyzing Data: Excel will not locate cells with these special characters when you search for blank cells. This will be an issue when you wish to look for empty cells in your sheet.
    • Tabling Error: Excel separates tables if there are blank cells in between. However, if the cells aren’t truly empty, your table will be registered as one.
    •  Increased File Size: As you enter characters into your sheet, your file size grows. Keeping cells with hidden characters will increase your file size which will take up more space in your device.
    • Issue with importing and exporting data: As the hidden characters in empty cells increase the file size, the process of importing and exporting data slows down. This may create difficulties in getting your spreadsheet ready.

    To workaround issues such as these, it is a good idea to double-check if the cells are really empty. In this article, we will dive into the multiple ways you can check if a cell is empty in Excel, including using formulas to making special selections, so keep reading!

    Use ISBLANK Function in Excel

    Excel has the ISBLANK function, dedicated to returning a boolean value if the cell passed as the argument is blank. The ISBLANK function requires only one argument which is the reference cell. Here is the format to write the ISBLANK function in a formula:

    =ISBLANK(value)

    Here, we’re using the ISBLANK function to determine if the cells in column A are empty or not. In a new cell, we entered the following formula:

    =ISBLANK(A2)

    ISBLANK Excel

    Although cell A2 looks empty, it actually has a space in it. Therefore, the ISBLANK returned FALSE.

    We used Flash Fill to copy the formula to the corresponding cells downwards. Cells A2 and A5 have text and linebreak respectively so, ISBLANK returned FALSE.

    ISBLANK Function Excel

    Cells A4 and A6 returned TRUE as they have no hidden characters embedded in them.

    Use Go to Special

    You can use the Go to Special to select all blank cells in your sheet. When the cells are selected, they look different than the non-empty cells giving you the idea of which cell is blank and which cell has data in them.

    1. Select the range where you want to look for blank cells.
    2. Go to the Home tab of your sheet and select Find & Select.
    3. Choose Go to special.
      Go to special Find and select Excel
    4. From the window, select the option next to Blanks.
      Go to special blanks Excel
    5. Click OK. All empty cells from the range will be highlighted in your spreadsheet.
      Blank Cells in Excel

    Sort and Filter tool

    You can use the Sort and Filter tool to check all cell content under a range. You can use this method to locate any hidden characters including line breaks in your cell range. If you want to get rid of linebreaks, you can check out our other article on how you can remove break lines!

    However, if you suspect having spaces in between the cells, this method will not work for you as it labels it exactly as it would label empty cells.

    1. Select your range and head to the Home tab.
    2. Click on Sort & Filter and choose Filter.
      Filter tool Excel
    3. Select the drop-down menu next to the column header.
    4. Deselect Select All.
      Go to special blanks Excel
    5. If you see any, click on the list of items that appear to have nothing; these cells have hidden cells in them.

    Use Find Tool to Locate Hidden Characters

    The Find tool is a great utility to check if your cell holds hidden content. You can look for spaces and linebreaks which are the more common characters that discretely fill blank cells up. If you wish, you could also replace the characters with nothing to make the cell completely blank.

    1. On your keyboard, use Ctrl + F.
    2. Click the Options button.
      Find Options Excel
    3. In the Find what section,
    • Enter space (“ ”) to find blank cells with spaces.
      Find Cells with Space in Excel
    • Hit Ctrl + J to look for line breaks. You might see a blinking dot appear next to the Find what section.
      Find Line Break Find Tool Excel
    1. Select the box next to Match entire cell contents.
    2. Click Find All.

    If your sheet has no such values, Excel will display an error message. If your sheet does in fact hold such cells, they will appear in the Find results.

    Excel Basics
    Asmi

      Asmi is a content writer specializing in MS Office. She loves writing Excel-related content to assist other users to learn their way around the program. She started using Excel when she first entered the industry as a marketing intern. Soon, she realized that learning the often-overlooked spreadsheet program was no piece of cake. In her articles, she attempts to brief out complex topics to help beginner to intermediate-level Excel users. Asmi uses Excel for everything, including creating a grocery list to managing her personal finance. She’s a bit dramatic when it comes to the Flash Fill feature; so much so that could write a eulogy out of it. When she’s not working, you’ll find her learning a new recipe, or cleaning up her mess (out of the failed recipe).

      Add A Comment
      Table of Contents
      • Use ISBLANK Function in Excel
      • Use Go to Special
      • Sort and Filter tool
      • Use Find Tool to Locate Hidden Characters
      Recent Posts
      • How to Lock a Cell in Excel Formula
      • How to Make Gridlines Darker in Excel
      • How to Apply Cell Style in Excel
      • How to Apply Top and Bottom Border in Excel
      • How to Retrieve Last Value in Column Excel
      • Home
      • Privacy Policy
      © 2023 Inside The Web

      Type above and press Enter to search. Press Esc to cancel.