Nuraida Kashmin
Blank cells inside the dataset sometimes become disturbing. These also create difficulties in the calculation. There are numerous methods to remove blank cells in Excel. In this article, we are going to know about them with explanations and examples. In the picture below, you can see the overview of the result that we will get after removing blank cells from a data set.
Practice Workbook
Download the following workbook and exercise.
10 Quick Ways to Remove Blank Cells in Excel
In this section, I will describe 10 simple and quick methods to remove blank cells in Excel. Let’s explore the methods one by one.
1. Removing Blank Cells Manually in Excel
We can manually remove blank cells. Assuming we have a dataset of the Customer’s payment history with a lot of blank cells.
STEPS:
- First, select all the blank cells by pressing the Ctrl key from the keyboard.
- Next right-click on the mouse and select Delete.
- Or we can simply go to Home > Cells > Delete.
- Now we can see a small window. Select the required option and click OK.
- Finally, we can get the result.
Read More: How to Remove Blank Cells Using Formula in Excel (7 Methods)
2. Using ‘Go To Special’ Feature to Delete Excel Blank Cells
Removing blank cells from a huge dataset is quite difficult if we try manually. ‘Go to Special’ can play an important role here. Let’s say we have a Customer’s payment history dataset.
STEPS:
- Firstly, select the whole range containing blank cells at first.
- Secondly, go to Home > Editing.
- Thirdly, from the Find & Select drop-down click ‘Go To Special’
- Consequently, we can see a small window pops up.
- Then, select the Blanks option and click OK.
- Here we can see all the selected adjacent & non-adjacent blank cells.
- Now, go to Home > Delete > Delete Sheet Rows.
- After clicking that, we can see the final result.
Read More: How to Remove Blanks from List Using Formula in Excel (4 Methods)
3. Use of Keyboard Shortcut to Erase Blank Cells in Excel
The keyboard shortcut is another easy way to remove blank cells. To know more, follow the steps below.
STEPS:
- Select all the blank cells from the range.
- Now press the ‘Ctrl + –’ keys for the result.
4. Remove Empty Cells with Find Command
The Find command is an Excel built-in option. Here we are going to use it in a dataset of the Customer’s payment history with blank cells.
STEPS:
- First, select the whole data range from the worksheet.
- Now, in the Home tab, select the Editing.
- Then, go to Find & Select > Find. We can also press the Ctrl+F keys to open the Find menu window.
- In this window, click Options to see the advanced search criteria.
- Next, keep the Find what box blank.
- After that, select Sheet from the Within the drop-down box.
- We have to make sure that the ‘Match entire cell contents’ box is ticked.
- Then select Values from the Look in the drop-down box.
- Click on Find All.
- Here, we can see all the blank cells. According to our dataset, there are 8 blank cells.
- Now, press Ctrl + A to select them all and select Close to omit the window.
- Now, go to Home > Delete > Delete Sheet Rows.
- At last, we can see the output.
Read More: Excel VBA: Find the Next Empty Cell in Range (4 Examples)
Similar Readings
- How to Fill Blank Cells in Excel with Go To Special (With 3 Examples)
- VBA to Count Blank Cells in Range in Excel (3 Methods)
- How to Fill Blank Cells with Value from Left in Excel (4 Suitable Ways)
- Fill Blank Cells with N/A in Excel (3 Easy Methods)
- How to Find Blank Cells in Excel (8 Easy Ways)
5. Use of Filter Option for Removing Blank Cells
An in-built option Filter can help us to find the blank cells from the below dataset and remove them.
STEPS:
- First, select the whole dataset.
- Next, go to the Home tab.
- Click Sort & Filter > Filter.
- We can see the filter toggle in each column.
- Select one of them.
- From the drop-down, uncheck Select All & check Blanks.
- Press OK.
- Now we can see the filtered blank cells.
- Select the cells without the Header and delete them manually.
- Again click on the filter toggle.
- Click on Select All and select OK.
- In the end, we can filter data without blank cells.
Read More: Excel VBA: Check If Multiple Cells Are Empty (9 Examples)
6. Use of Advanced Filters to Remove Blank Cells in Excel
Sometimes we can use the Advanced filter with a condition for removing blank cells in Excel. From the bellow dataset, we are going to remove all the blank Date cells. For this, we need to take some initial steps. At first, select the criterion cell C14. Here we type “<>”. Also, we need to insert the total header where we want to see the result.
Now, follow the steps below.
STEPS:
- Firstly, select the entire dataset.
- Then, go to Data > Advanced.
- Consequently, a small advanced filter window pops up.
- Now, insert the list and criteria ranges, where to copy. Also, select the option to copy another cell.
- Press OK.
- Finally, we can see the result in cell range B16:E16.
Read More: How to Delete Empty Cells in Excel (6 Methods)
7. Use of the Sort Option to Delete Excel Blank Cells
We can remove Excel blank cells by sorting them. Assuming we have a dataset like the previous methods.
Now follow the steps below to remove blank cells using the Sort Option
STEPS:
- Firstly, select the data range.
- Secondly, go to the Data tab.
- Now, from Sort & Filter section, select the ascending or descending Sort command.
- Now, we see that all the blank cells are at the end of the dataset.
- Select the blank cells and delete them manually to see how the dataset looks.
Read More: How to Delete Blank Cells and Shift Data Up in Excel
Similar Readings
- How to Find Blank Cells Using VBA in Excel (6 Methods)
- Ignore Blank Cells in Range in Excel (8 Ways)
- How to Set Cell to Blank in Formula in Excel (6 Ways)
- Return Value if Cell is Blank (12 Ways)
- How to Find & Count If a Cell Is Not Blank (with Examples)
8. Insertion of FILTER Function to Remove Blank Excel Cells
In an Excel Table, we can use the FILTER function. It’s a dynamic array function. Let’s say we have a data table of the Customer’s payment history in the B4:E11 range. We are going to remove the blank cells and show the result in Cell B14 by filtering the data according to the Amount row.
STEPS:
- Firstly, select Cell B14.
- Secondly, type the formula:
=FILTER(Table1,Table1[Amount]<>"","")
- Now, hit Enter to see the result.
Read More: How to Remove Blank Lines in Excel (8 Easy Ways)
9. Erase Blank Cells After the Last Used Cell with Data
For removing the formatting of blank cells of the given data set after the last used cell with data, we can follow these steps.
STEPS:
- Firstly, select the first blank cell of the header.
- Secondly, press Ctrl + Shift + End to select the range of the cells between the last used cells with data and the current data.
- Thirdly, go to Home > Delete > Delete Sheet Columns.
- As a result, you will see that the blank columns have been deleted.
Read More: Find If Cell is Blank in Excel (7 Methods)
10. Use of Power Query to Remove Empty Cells in Excel
Power Query is an Excel Business Intelligence tool. We are going to use this powerful tool for removing blank row cells. Here is our data table.
STEPS:
- Firstly, select any cell in the table.
- Then for adding data to the Power Query window, go to Data > From Table/Range.
- Now, select the Home tab.
- From the Remove Rows drop-down, click Remove Blank Rows.
- Then, for creating a new table without blank rows, click the Close & Load option.
- Lastly, we can see the new table. We also can replace this data with the original one but it’s optional.
Read More: How to Make Empty Cells Blank in Excel (3 Methods)
Conclusion
By using these methods, we can easily remove blank cells in Excel. Moreover, there is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.
Related Articles
- Return Non Blank Cells from a Range in Excel (3 Suitable Ways)
- Excel If Two Cells Are Blank Then Return Value
- Fill Blank Cells with Text in Excel (3 Effective Ways)
- How to Fill Blank Cells with Formula in Excel (2 Easy Methods)
- Fill Blank Cells with Dash in Excel (3 Easy Ways)
- How to Fill Blank Cells with Value Above in Excel VBA (3 Easy Methods)
- How to Fill Blank Cells with Color in Excel (5 Methods)
