How to Make Empty Cells Blank in Excel (3 Methods)

In Microsoft Excel, while putting data in a spreadsheet, it’s a good practice to check all the cells. If there is some kind of data that contains nothing but those are empty cells, not blank cells. We have to make those empty cells blank because while working with those data, there may occur some issues like if we want to put formulas it will not work properly. In this article, we will demonstrate how to make empty cells blank in excel.


Download Practice Workbook

You can download the workbook and practice with them.


Dataset Introduction

To make empty cells blank we are going to use the dataset below. The dataset contains some products in column B and the total sales of each product in column C. We can see that cell C6 and cell C9 have no data. Those cells seem blank but they are not. Those cells are empty with some spaces. Now we need to make those empty cells blank. Let’s go through the methods at this time.


Show Empty Cells That Seem Blank in Excel

As we can see in the dataset, some cells contain nothing. Now the thing is how we can find that the cells are empty or blank. To show empty cells that seem blank, we need to follow the procedure described in the lower section of this article.

STEPS:

  • Firstly, select the data range B4:C9. Then, go to the Home tab from the ribbon.
  • Secondly, click on the Find & Select drop-down menu.
  • After that, click on Go To Special.

Show Empty Cells That Seems Blank in Excel

  • This will open the Go To Special dialog box.
  • Now, select Blanks and click OK.

Show Empty Cells That Seems Blank in Excel

  • And, finally, you can see a window will appear and give a message that No cells were found.

  • By doing this, you are able to know that the cells which contain nothing are not blank cells, they are empty cells. If you click on that particular cell, you can see there is some space there showing in the picture down.


Find Length of Cells Including Empty Cells from a Range in Excel

We can also find the length of the cells by using the LEN function in Excel. By finding the length of each cell including the cells that contain nothing, we can know that the cells are blank or empty. To use the LEN function to find the length of each cell content, we have to look at the procedure down.

STEPS:

  • In the first place, select cell C5 and write the formula there.
=LEN(C5)
  • In the second place, press Enter and the formula will appear in the formula bar.

  • Next, drag the Fill Handle down to copy the formula over the data range.

  • Now, you can see that cells C6 and C9 look like they are blank. But they have some length, which means there are some empty spaces in there and the cells are empty.


3 Different Methods to Make Empty Cells Blank in Excel

Making the empty cells blank helps to ensure that the data is absolute and error-free. We can make the empty cells blank by going along with the approaches shown in the section down. Let’s go through the methods to make empty cells blank in Excel.


1. Make Empty Cells Blank Using Excel VBA

Using Excel VBA to automate a work to do any task has several advantages. We can always complete the tasks in the same manner without doing this manually with Excel VBA. We are going to use the TRIM function to make empty cells blank in excel VBA. Now, let’s look at the steps on how we can use the Excel function in Excel VBA.

STEPS:

  • In the beginning, go to the Developer tab from the ribbon.
  • Then, click on Visual Basic and this will open the Visual Basic Editor. Or, press Alt + F11 to open the Visual Basic Editor.

Make Empty Cells Blank Using Excel VBA

  • Another way to open the Visual Basic Editor where you will be able to write the VBA code is just right-clicking on the sheet then, select View Code.

Make Empty Cells Blank Using Excel VBA

  • And, this will open the Visual Basic Editor.
  • Now, copy and paste the VBA code there.

VBA Code:

Sub EmptyCells_Blank()
Dim myRange As Range
Set myRange = Selection
For Each cell In myRange
cell.Value = Trim(cell)
Next
End Sub
  • Run the code by pressing the F5 key or clicking on the Run Sub button.

Make Empty Cells Blank Using Excel VBA

  • Finally, this will make all the empty cells blank.

Read More: Excel VBA: Find the Next Empty Cell in Range (4 Examples)


2. Excel Filter Option to Make Empty Cells Blank

We can use the Excel Filter option to improve the data analysis. We will find the filter option in the Sort & Filter command. Let’s take a look at the steps down to use the filter option to make empty cells blank in Excel.

STEPS:

  • First, select the whole data range. So we are selecting range B4:C9.
  • Then, go to the Home tab on the ribbon.
  • Further, click on the Sort & Filter drop-down menu.
  • After that, select Filter.

  • Now, you can see that your data has drop-down lists. That means your data is filtered now.
  • At this moment, click on the Sold drop-down list as the empty cells are located at this data table column.
  • Next, select the Blanks from the selection list and click OK.

  • And, you can see the empty cells now as the data is now sorted and filtered.
  • In this time, to make the empty cells blank, go to the Home tab.
  • Then, click on the Clear drop-down menu bar under the Editing group.
  • After that, select Clear Content.

How to Make Empty Cells Blank in Excel

  • And, finally, if you put your mouse cursor in those empty cells there is no space in there right now. That means the cells are now blank.

  • To clear the filter, right-click on the filtered data range and select Clear Filter From “Sold” from the Filter drop-down menu.

  • Right now, to make sure that the cells are now empty or blank, select the data range likewise the previous steps.
  • Further, by the same token, go to the Home tab and select the Go To Special under the Find & Select drop-down menu.

Excel Filter Option to Make Empty Cells Blank

  • This will open the Go To Special dialog box.
  • Furthermore, select the Blanks and click on the OK button.

Show Empty Cells That Seems Blank in Excel

  • And, you can see it shows the blank cells.

Read More: How to Find and Replace Blank Cells in Excel (4 Methods)


Similar Readings:


3. Manually Make Empty Cells Blank in Excel

We can manually make the empty cells blank. To do this, let’s follow the steps below.

STEPS:

  • Firstly, click on the cell that looks blank but there is some space in there. So we choose cell C6. And, you can see there is some space there showing in the following picture.

Make Empty Cells Blank Cells Manually in Excel

  • Now, just simply press the Backspace on your keyboard.
  • And, this will remove all the space and make the cell blank.

Make Empty Cells Blank Cells Manually in Excel

Read More: How to Remove Blank Cells Using Formula in Excel (7 Methods)


Conclusion

This article will not only teach you three quick and easy ways to make empty cells blank but also to find if the cells which contain nothing are blank and to find the length of cells in Excel. The above methods will assist you to make empty cells blank in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo