How to Deal with Blank Cells That Are Not Really Blank in Excel (4 Ways)

Sometimes you may think a cell is blank because there’s nothing in it visible. But turns out it may not be actually blank. There might be a formula that returns a null value, or maybe a hard space, newline, or just a simple space. However, this tutorial will teach you how to detect and delete blank cells that are not really blank in Excel.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


4 Ways to Deal with Blank Cells That Are Not Really Blank in Excel

1. Use Paste Special to Delete the Blank Cells That Are Not Really Blank in Excel

In the following screenshot, we have a dataset whose Profit column displays the profit amount only if the Income is greater than the expenditure. So the blank cells represent that there’s no profit but losses.

If you click any of the blank cells, you will see the corresponding formula in the formula bar.

Blank Cells That Are Not Really Blank in Excel

Now to delete all the blank cells that are not actually blank,

❶ Select the entire column.

❷ Then press the CTRL + C keys simultaneously to copy the selected cells.

❸ Now right-click on the top cell of the Profit column.

❹ From the pop-up menu, choose Values (V).

Use Paste Special to Delete the Blank Cells That Are Not Really Blank in Excel

This will replace all the formulas with their corresponding values. So the formulas that breed null values will be replaced by null values. This means those blank cells are not actually blank.

To verify this, click any one of those blank cells. In the formula bar, you will see no contents in them. There are literally blank now.

Read More: How to Delete Empty Cells in Excel (6 Methods)


2. Apply Filter to Delete the Blank Cells That Are Not Really Blank in Excel

In this method, we will filter out all the blank cells that are not really blank and later delete them all.

To do that,

❶ Click anywhere on the dataset.

❷ Then press CTRL + SHIFT + L to apply Filter.

Apply Filter to Delete the Blank Cells That Are Not Really Blank in Excel

❸ Click on the drop-down icon of the Profit column.

❹ Select only the Blanks as shown and hit OK.

Select Blanks using Filter to Delete the Blank Cells That Are Not Really Blank in Excel

This will filter out all the blank cells that are not really blank.

❺ Now select them all.

❻ Then press CTRL + – keys together to delete them instantly.

A little dialog box will appear to get the confirmation regarding the deletion.

❼ Just hit the OK button.

❽ After that click on the Filter icon at the right-bottom corner of the Profit column.

❾ Select all the values and hit OK.

This will bring back all the cells with no blank cells among them.

Read More: How to Remove Blank Cells in Excel (10 Easy Ways)


Similar Readings:


3. Use Find and Replace to Remove the Blank Cells That Are Not Really Blank in Excel

Using this method, you can find all the blank cells that are not really blank. Later you can delete them altogether and make them actually blank.

To do that,

❶ Select the cells where you want to find the existence of the blank cells that are not really blank.

❷ Then press CTRL + H keys together.

The Find and Replace dialog box will appear.

❸ In the Find what box click once and then,

  • Press the Space button once to look for the normal spaces.
  • Press the ALT key and then type 0160 to find the hard spaces.

❹ After that, click on the Find All button.

This will create a list of all the records having spaces in any of the cells of them.

❺ Press CTRL + A to select all the search results.

Use Find and Replace to Remove the Blank Cells That Are Not Really Blank in Excel

❻ Leave the Replace with box as it is.

❼ After that hit the Replace All button.

Remove the Blank Cells That Are Not Really Blank in Excel

This command will delete all the blank cells that are not really blank.

Related Content: Find, Count and Apply Formula If a Cell is Not Blank (With Examples)


4. Use VBA Script to Remove the Blank Cells That Are Not Really Blank but Newlines in Excel

It’s hard to determine if any of the blank cells contain spaces due to newlines. To remove them all, you need a VBA script.

❶ At first, press ALT + F11 to open the VBA editor.

❷ After that, create a new module from Insert > Module.

❸ Copy the following code.

Sub RemoveNewLines()
    With Me.Cells
        Set mm = .Find(Chr(10), LookIn:=xlValues, LookAt:=xlWhole)
        If Not mm Is Nothing Then
            ff = mm.Address
            Do
                mm.Value = ""
                Set mm = .FindNext(mm)
                If mm Is Nothing Then Exit Do
            Loop While mm.Address <> ff
        End If
    End With
End Sub

❹ Paste and save the code in VBA editor.

❺ Finally, press F5 to run the code.

This code will delete all the blank cells that are not actually blank but contains newlines.

Related Content: How to Remove Blank Lines in Excel (8 Easy Ways)


Things to Remember

  • Press CTRL + – keys to delete any items.
  • To insert a hard space, press the ALT key and then type 0160.

Conclusion

To sum up, we have discussed 4 ways to delete blank cells that are not really blank in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo