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 visible in it. But it may not actually be blank. There might be a formula that returns a null value, a hard space, newline, or just a simple space. Let’s explore some ways to detect and delete blank cells that are not really blank in Excel.

Method 1 – Use Paste Special

Suppose 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 losses rather than profits.

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

STEPS:

❶ Select the entire column.

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

❸ Right-click on the top cell of the Profit column.

❹ From the context menu, choose Values (V) as a Paste Option.

This will replace all the formulas with their corresponding values. So the formulas that return null values will be replaced by actual null values.

Click any of the blank cells. In the formula bar, you will see no contents in them. They are literally blank now.

Method 2. Use Filter

We can filter out all the blank cells that are not really blank and then delete them all.

STEPS:

❶ Click anywhere on the dataset.

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

❺ Select them all.

❻ Press the CTRL + – keys together to delete them instantly.

A little dialog box will appear to confirm the deletion.

❼ Just hit the OK button.

❽ 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 non-blank cells.

Method 3 – Use Find and Replace

This is another method to find all the blank cells that are not really blank, then delete the contents to make them actually blank.

STEPS:

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

❷ Press the 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 type 0160 to find the hard spaces.

❹ Click on the Find All button.

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

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

❻ Leave the Replace with box as is.

❼ Hit the Replace All button.

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

Method 4 – Use VBA Script to Remove Newlines

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

STEPS:

❶ Press ALT + F11 to open the VBA editor.

❷ Create a new module by clicking 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.

❺ Press F5 to run the code.

This code will delete all the blank cells that actually contain newlines.

Read More: How to Make Empty Cells Blank in Excel

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

Related Articles

• How to Set Cell to Blank in Formula in Excel
• Formula to Return Blank Cell instead of Zero in Excel
• How to Find Blank Cells in Excel
• Null vs Blank in Excel
• How to Highlight Blank Cells in Excel
• Return Non Blank Cells from a Range in Excel

<< Go Back to Excel Cells | Learn Excel