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.

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

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

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

Similar Readings

• If Cell is Blank Then Show 0 in Excel (4 Ways)
• How to Set Cell to Blank in Formula in Excel (6 Ways)
• Ignore Blank Cells in Range in Excel (8 Ways)
• How to Remove Blanks from List Using Formula in Excel (4 Methods)
• Apply Conditional Formatting in Excel If Another Cell Is Blank

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 box as it is.

❼ After that hit the Replace All button.

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

• Fill Blank Cells with Value Above in Excel (4 Methods)
• Null vs Blank in Excel
• Highlight Blank Cells in Excel (4 Fruitful Ways)
• How to Calculate in Excel If Cells are Not Blank: 7 Exemplary Formulas
• Find If Cell is Blank in Excel (7 Methods)
• How to Find Average with Blank Cells in Excel (4 Easy Ways)