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.

how to make empty cells blank in excel


📁 Download Excel File

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


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.

Difference between blank cell and empty cell


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.

selecting a cell to make it blank

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

Using the Backspace button to clear the content of a cell

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

making an empty cells blank in excel

📕 Read More: 4 Ways to Leave Blank If Cell Contains Zero with Excel Formula

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.

Selecting the whole table to make the cells blank

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

Selecting the Filter Tool

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

Filtering the Blank cells

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

Selecting the filtered empty cells to make them blank

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

Clearing content using the clear tool

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

Making the empty cells blank

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 ⬇️⬇️

Selecting Visual Basic option from Developer Tab

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

Creating new module to insert new code

  • 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

Inserting the code to make empty cells blank

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

Selecting the whole dataset

  • Now go to Visual Basic from Developer tab.

Selecting Visual Basic option from Developer Tab

  • Click on Run.

Running the code from the Visual Basic window

  • You can also go to Macros from the Developer tab.

Selecting Macros option from Developer Tab

  • After that, click Run.

Macro option to run the created Code

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

Making the Empty cells blank


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.

Selecting the whole table to select the blank cells

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

Pressing F5 opening the go to option

  • Select the Blank option and press OK.

Go to Special window to select the blank cells

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

Selecting the blank cells

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


Related Articles

(Visited 51 times, 1 visits today)
Nashid

Nashid

Hello there. I am Nashid, an Engineer. I Love music, traveling, sports and gaming. I have recently grown interest in DATA SCIENCE and wish to build a career over it. Excel is one of the greatest modern digital creation of human. Life simply gets easier. My Articles are meant to lead you to that easier life with efficiency. If you need any kind of help regarding my articles even anything else try contacting me on social platforms of which I've provided. Keep learning. Wish me luck and Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo