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.

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

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.

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

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. They are literally blank now.

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.

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

❹ Select only the Blanks as shown and hit OK.

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.

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.

❻ Leave the Replace with the box as it is.

❼ After that hit the Replace All button.

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

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.

Things to Remember

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

Download Practice Workbook

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

Blank Cells Not Really Blank.xlsm

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.

<< Go Back to Blank Cells | Excel Cells | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Joris Van Bossche Aug 9, 2022 at 6:33 PM

Hi,
I get a compile error: invalid use of the Me keyword.

Any ideas?
- Reply
  Mrinmoy Aug 14, 2022 at 12:24 PM
  
  Hi Joris,
  The Me keyword can’t appear in a standard module because a standard module doesn’t represent an object. If you copied the code in question from a class module, you have to replace the Me keyword with the specific object or form name to preserve the original reference.
  Thanks!