3 Approaches to Make Empty Cells Blank in Excel

In this lesson, I’ll demonstrate 3 different approaches on how to make empty cells blank in Excel. Moreover, these techniques will enable you to solve the problem that led you here. However, you will master several crucial Excel features and functions during this article, which will come in extremely handy for any assignment using Excel.

Contents hide

📁 Download Excel File

Difference Between Empty and Blank Cells

Learn to Make Empty Cells Blank in Excel with These 3 Useful Methods

1. Manually Clearing Out Spaces

2. Using Filter Option

3. Employing VBA Code

How to Quickly Select Blank Cells in Excel

📄 Important Notes

📝 Takeaways from This Article

Conclusion

📁 Download Excel File

The sample worksheet that was used during the discussion is available to download for free right here.

Making Empty Cells Blank.xlsm

Difference Between Empty and Blank Cells

An empty cell in Excel has no data or formula entered into it. A blank cell may have data or a formula that returns an empty string (i.e. “”). The appearance of empty and blank cells is the same, but they behave differently in formulas and functions. Space can be registered as a value in Excel. We can’t see any visual sign of space which practically looks the same as a cell which has nothing in it. So here in this lesson, we are going to make sure cells have no value in them.

We are using the ISBLANK function to denote if the cells have value or not. This function returns TRUE if the cell is Blank and FALSE if not. As well as the LEN function to determine the string length of strings.

Learn to Make Empty Cells Blank in Excel with These 3 Useful Methods

Here in this article, we will learn to make empty cells blank in Excel using different approaches. To be exact, I’ve provided a total of 3 methods down below.

Method 1

1. Manually Clearing Out Spaces

This way, we are going to make empty cells blank in excel without any kind of function or formula, or tool. We will be doing the process manually. Excel registers space as value; thus, the empty cells are not always necessarily blank. There can be space registered as values.

⬇️⬇️ STEPS ⬇️⬇️

Select the empty cell that you want to make Blank.

Press BackSpace, and you’ll see the text cursor based on your alignment.

Press Enter to make it a new blank cell. See the Blank column that says TRUE.

Method 2

2. Using Filter Option

In this approach, we will use the Filter Tool to make empty cells blank in Excel. This tool is always handy for filtering out certain criteria. We have a column Blank here, which states if the column C cells are Blank. Follow the instructions below.

⬇️⬇️ STEPS ⬇️⬇️

Select the whole table with the Headers.

Now go to the Filter option from the Data tab of the ribbon.

Select the empty cells by pressing the drop-down button from the Sold Select the (Blanks) option and click OK.

So, we have filtered out the blank cells here. After that, select the blank cells by dragging.

Now go to the Home tab from the ribbon, and on the right side of the ribbon, you will find Clear option. Select Clear Contents from Clear options.

So, the blank cell says TRUE, which means the cells are blank now.

Method 3

3. Employing VBA Code

In this section, we will use Microsoft Visual Basic to make empty cells blank in Excel. Using the VBA code is an advanced form of solving any problem in Excel. It’s also a time-saving, efficient and productive way of solving problems in Excel.

⬇️⬇️ STEPS ⬇️⬇️

First, you need to turn your Developer tab on in Excel.
Now go to Visual Basic option from the Developer tab of the ribbon.

After that, a new window will pop up. Select Module from the Insert.

Copy the following code.

Sub Turn_emptycells_Blank()
Dim z As Range
Set z = Selection
For Each cell In z
cell.Value = Trim(cell)
Next
End Sub

Now save it as a Macro Enabled Workbook.
To use first, select the cells you want to apply the code into. The table contains a Blank Column which indicates whether the cells in Column C are Blank or not, and it is visible that no cell in Column C is Blank.

Now go to Visual Basic from Developer tab.

Click on Run.

You can also go to Macros from the Developer tab.

After that, click Run.

The results will be like this. The empty cells have now become blank cells.

How to Quickly Select Blank Cells in Excel

Here we will learn how to quickly select Blank cells in Excel when you have scattered blank cells in a dataset. By following this lesson, you will be able to select the scattered blank cells in your dataset quickly and effectively. Follow the instructions below.

⬇️⬇️ STEPS ⬇️⬇️

First, select the table or range from where you want to select the blank cells.

Now press F5 a small window will open. Select the Special option from the window.

Select the Blank option and press OK.

The picture below shows all the blank cells selected within the range.

📕 Read More: 4 Ways to Find and Replace Blank Cells in Excel

📄 Important Notes

🖊️ Use shortcuts to reduce the time, for instance, F4 for absolute value.

🖊️ While writing formulas, carefully see the suggestions and don’t forget the commas and parentheses.

🖊️ Be careful while defining criteria to be accurate as the dataset.

🖊️ While copying any formula, try pasting it in the formula bar instead of the cell itself.

🖊️ If the data is left aligned, it indicates it is in text format. On the other hand, right alignment indicates to be in number format.

📝 Takeaways from This Article

📌 The article demonstrated different approaches how to make empty cells blank in Excel.

📌 In the first section, we showed how to use Filter Tool to make empty cells blank in an Excel dataset.

📌 Then, we demonstrated how to use the VBA Code and Manually make empty cells blank in Excel.

📌 After that, we learned How to Quickly Select Blank Cells in Excel using keyboard shortcut.

📌 Finally, we properly learned how to make empty cells blank in excel with different approaches.

Conclusion

Firstly, I hope you were able to use the techniques I demonstrated in this how to make empty cells blank in excel in Excel lesson. As you can see, there are a lot of options on how to do this. However, decide deliberately on the approach that best addresses your circumstance. Most importantly, I advise repeating the steps if you become confused in any of the steps if you get stuck. However, practice on your own after taking a look at the Excel file in the practice workbook I’ve provided above because practice makes a man perfect. Above all, I earnestly hope you can use it. Please share your experience with us in the comment box about how your experience was throughout the article and what we need to improve or add to make things better for you folks. However, if you have any problem regarding Excel, spit it in the comment box. The Excelden crew is always available to answer your questions. In conclusion, keep educating yourself by reading. For more Excel-related articles, please visit our website Excelden.com.