Maruf Islam
Blank cells in Excel dataset are not unusual. When they are in the right place in the dataset, blank cells provide eye-soothing views; but blank cells in random places mess up the viewing, resulting in miscalculations & many more. In this article, I use Filter, Sorting, & even the Manual method to delete blank cells in Excel resulting in moving the data Up or Left.
Suppose, I have a dataset with blank cells in rows & columns similar to the picture below
Dataset for Download
4 Easy Ways to Delete Blank Cells in Excel and Shift Data UpÂ
Method 1: Using Go to Special command
Step 1: Select the range/column you want to delete the blank cells from.
Step 2: In the Home tab, Go to Find & Select option >> Click on Go to Special.
For keyboard shortcut press CTRL+G, Go to dialog box will appear. Then Click on Special.
Step 3: Select Blanks then Click on OK.
Step 4: Blank cells in the range/column get highlighted.Right-click on any of the highlighted blank cells.
Step 5: From the options, Choose Delete.
A popup window appears. As we have blank cells along rows & columns inside the range, we have to select Shift Cell Up or Entire Row & Shift Cell Left or Entire Column for rows & columns respectively.
Step 6: Select the Shift Cell Up or Entire Row for rows.
The outcome will be similar to the image below
Step 7:Repeat steps 1 to 6, then Select the Shift Cell Left or Entire Column for columns.
The outcome will be similar to the image below
Method 2: Applying Filter
Step 1:Select the range you want to delete the blank cells from.
Step 2:Go to the Data tab >> Sort & Filter section>> Click on the Filter option.
You can also apply Sort and Filter by pressing keyboard shortcut CTRL+SHIFT+L altogether.
It adds Sort and Filter menu to every column headings & we can use the checked/unchecked option to filter out blank rows.
Step 3:Click on the Sort & Filter menu in any column. A drop-down menu will appear.
Step 4:Unchecked all other options except Blanks & press OK.
The outcome image will contain only the blank cells.
Step 5:Select all the blank cells & Execute Home Tab>Delete> Delete the Sheet Rows.Â
Step 6:Click on the Sort & Filter menu in any column. From the Drop Down Options, Click on Select All.
Step 7: Click OK. Then the final image will be
Deleting a complete blank column with Filter is not doable. But We can achieve that via Home>> Delete>> Delete Entire Column sequence execution.
Step 1:Select the entire blank column you want to delete. Go to Home Tab>> Click on the Down Arrow below Delete command.
Step 2:Select Delete Entire Column.
Method 3:Applying Sorting
Step 1:Select the range you want to sort.
Step 2:Go to the Data tab>> Sort & Filter section.
Step 3:Select Sort by A to Z. And the data cells move up placing blank cells below them. Now, we can use Home>> Delete>> Delete Entire Sheet RowsÂ
Again for deleting blanks in column, repeat the sequence-Select Column >>Home tab >>Delete>> Delete Sheet Columns. And the final output becomes
Method 4: Delete Blank Cells Along the Rows or Column ManuallyÂ
Step 1:Select any cell or range of cells you want to delete. Right-click on it. A window will appear; Select Delete. A command window will pop up.
Step 2: Select
i)Shift Cell Left or Entire Column to move the rest cells left, deleting the blank cells.
We can use the keyboard shortcut CTRL+ -, to pop up delete commands.
After applying the command, the final output will be as shown below
ii)Shift Cell Up or Entire Row to move the rest of cells Up, deleting the blank cells.
After applying Shift Cells Up or Entire row in each blank row, the outcome will be as shown in the image below
Conclusion
Blank cells in a dataset drive formulas to provide error outcomes, thus it is a good practice one should avoid using them. In this article, we use Go to Special, Filter, Sorting, and even Manual methods to delete blank cells entirely along the rows or columns. Though the methods are quite easy, improper use of them can meddle with your dataset, resulting in serious manipulation of entries. I hope, you find these above methods user-friendly & super easy to use. Comment if you want to add or have further queries.
