Editor choice

How to Delete Blank Cells in Excel and Shift Data Up

Fill Blank Cells with Value Above in Excel (4 Methods)

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

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.

go to special

For keyboard shortcut press CTRL+G, Go to dialog box will appear. Then Click on Special.

special shortcut

Step 3: Select Blanks then Click on OK.

click on blanks

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.

click on 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.

shift cell up

The outcome will be similar to the image below

shift cell up

Step 7:Repeat steps 1 to 6, then Select the Shift Cell Left or Entire Column for columns.

shift cell left

The outcome will be similar to the image below

final shift cell left

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.

filter

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.

filter

Step 4:Unchecked all other options except Blanks & press OK.

filter drop down menu

The outcome image will contain only the blank cells.

blank cell

Step 5:Select all the blank cells & Execute Home Tab>Delete> Delete the Sheet Rows. 

select blank cellsStep 6:Click on the Sort & Filter menu in any column. From the Drop Down Options, Click on Select All.

select all

Step 7: Click OK. Then the final image will be

filter final

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.

column delete

Step 2:Select Delete Entire Column.

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.

sorting

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 

sorting result

Again for deleting blanks in column, repeat the sequence-Select Column >>Home tab >>Delete>> Delete Sheet Columns. And the final output becomes

final sort

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.

shift cell left

We can use the keyboard shortcut CTRL+ -, to pop up delete commands.

After applying the command, the final output will be as shown below

result shift cell left

ii)Shift Cell Up or Entire Row to move the rest of cells Up, deleting the blank cells.

shift cell up

After applying Shift Cells Up or Entire row in each blank row, the outcome will be as shown in the image below

final result

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.


Further Readings:

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo