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.
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. We can always find the blank cells in Excel. 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.
- This will open the Go To Special dialog box.
- Now, select Blanks and click OK.
- 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.
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.
How to Make Empty Cells Blank in Excel: 3 Different Methods
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.
- 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 and then selecting View Code.
- 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.
- Finally, this will make all the empty cells blank.
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 in 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.
- 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 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.
- This will open the Go To Special dialog box.
- Furthermore, select the Blanks and click on the OK button.
- And, you can see it shows the blank cells.
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.
- Now, just simply press the Backspace on your keyboard.
- And, this will remove all the space and make the cell blank.
Download Practice Workbook
You can download the workbook and practice with them.
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 that 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
- How to Deal with Blank Cells That Are Not Really Blank in Excel
- Return Non Blank Cells from a Range in Excel
- Null vs Blank in Excel
- How to Highlight Blank Cells in Excel
- How to Set Cell to Blank in Formula in Excel
- Formula to Return Blank Cell instead of Zero in Excel
<< Go Back to Blank Cells | Excel Cells | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
