How to Delete Blank Cells and Shift Data Up in Excel

Get FREE Advanced Excel Exercises with Solutions!

Certainly, empty cells in an Excel spreadsheet are not unusual. In fact, when they are in the right place in the dataset, empty cells provide eye-soothing views; but in random places mess up the viewing, resulting in miscalculations & many more. Keeping this in mind, this article discusses how to delete blank cells in Excel and shift data up and explores removing blank cells with VBA code and using formulas.


How to Delete Blank Cells in Excel and Shift Data Up: 8 Ways

First of all, let’s consider the List of Best Selling Books dataset shown in the B4:E16 cells containing the Name, Author, and ISBN columns. Here, we want to remove the blank rows and the blank columns using various commands, features, and functions in Excel. Henceforth, without further delay, let’s observe each method in detail and with the proper illustration.

Dataset for how to delete blank cells in excel and shift data up

Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.


1. Deleting Blank Cells Manually

In truth, if we need to delete only a handful of rows and columns, then it is better to remove them manually. So, let’s see it in action.

📌 Steps:

  • First and foremost, hover the cursor until a downward pointing arrow appears >> left-click to select the column, here it is Column C.

Deleting Blank Cells Manually

  • Next, click the Delete drop-down >> press Delete Sheet Columns.

Using Delete columns option

  • Later, hold down the CTRL Key >> left-click on the row numbers to select multiple rows, in this case, rows 7, 9, 12, and 14 >> choose the Delete Sheet Rows option.

Using Delete Sheet Rows

Voila! That is how simple it is to remove blank cells in Excel and shift data up.

how to delete blank cells in excel and shift data up manually

Read More: How to Remove Unused Cells in Excel


2. Using Go To Special Command

Now, if we have lots of blank cells and rows we can apply Excel’s Go To Special command to select and remove all the blank cells.

📌 Steps:

  • Initially, select the B4:E16 cells >> click Find & Select drop-down >> select Go To Special.

Using Go To Special Command

  • Then, in the Go To Special window, click the Blanks button>> hit OK.

Selecting Blanks

  • Afterward, right-click to open the Context Menu >> choose Delete.

Pressing delete

  • In turn, check the Shift cells up option to remove all the blank rows.

how to delete blank cells in excel and shift cells up

  • Now, select the blank column >> select the Shift cells left option >> press OK.

Shift cells left

Boom! This deletes the blank cells as shown in the image below.

how to delete blank cells in excel and shift data up with Go To Special option


3. Utilizing Filter Option

Besides, we can also use the Filter option to easily remove multiple blank rows and columns. So, let’s see it in action.

📌 Steps:

  • To begin with, highlight the B4:E16 cells >> move to the Data tab >> click the Filter option.

Utilizing Filter Option

  • At this point, click any down-arrow button >> check the Blanks option >> enter the OK button.

Check the Blanks option

  • Again, click Delete Sheets Rows to remove all the blank rows.

Removing sheet rows

  • Next, click the down-arrow button >> check the Select All option >> click on OK.

Check Select All option

  • In a similar style, select the blank column >> press Delete Sheet Columns.

Removing Sheet Columns

Finally, the results should look like the picture shown below.

how to delete blank cells in excel and shift data up with Filter option


4. Applying Sort Feature

Alternatively, we can also use the Sort feature to delete blank cells in Excel. It’s simple and easy, just follow along.

📌 Steps:

  • At the very beginning, select the B5:E16 cells >> navigate to the Data tab >>  click the Sort feature.

Applying Sort Feature

  • At this point, select the blank rows >> use the CTRL + – (Minus) keys to delete them.

Using shortcut key to delete

  • Likewise, select the blank column >> press CTRL + – (Minus) keys to delete the column.

Eventually, your results should resemble the screenshot below.

how to delete blank cells in excel and shift data up with Sort feature

Read More: How to Remove Blank Cells from a Range in Excel


5. Employing Find Option

Conversely, Excel’s Find option can also delete blank cells and shift data up. Now, allow us to demonstrate the process in the steps below.

📌 Steps:

  • First, select the B5:E16 cells >> jump to the  Find option in the Find & Select drop-down.

Employing Find Option

  • Second, in the Find and Replace, check the Match entire cell contents option >> in the Look in field, select Values option >> press Find All.

Using Find & Replace

  • Third, the top result and press the CTRL + A keys to select all >> hit Close.

Using shortcut to select all

  • Fourth, apply the Delete Sheet Rows option.

Consequently, the results should appear in the screenshot below.

how to delete blank cells in excel and shift data up with Find option


6. Incorporating Advanced Filter Menu

For one thing, we can utilize the Advanced Filter menu to filter out blank cells in the dataset.

📌 Steps:

  • First of all, follow the steps shown in the animated GIF shown below.

Incorporating Advanced Filter Menu

Ultimately, the final output should look like the figure below.

how to delete blank cells in excel and shift data up with advanced filter


7. Using FILTER Function

Undoubtedly, functions are the life and soul of an Excel worksheet. Fortunately, we can use the FILTER function to delete blank cells present in the dataset.

📌 Steps:

  • Initially, choose the B4:E16 cells >> press CTRL + T to insert Excel Table as shown below.

Using FILTER Function

  • From this point, proceed to the Table Design tab >> rename the Table as “Best_Sellers”.

Renaming table

  • Lastly, move to the F5 cell >> insert the formula into the Formula Bar.

=FILTER(Best_Sellers,Best_Sellers[[Name ]]<>"" )

Formula Breakdown
  • FILTER(Best_Sellers,Best_Sellers[[Name ]]<>”” ) → filter a range or array. Here, Best_Sellers is the array argument, while Best_Sellers[[Name ]]<>”” is the include argument that removes the blank rows in the given array.

how to delete blank cells in excel and shift data up with FILTER function


8. Implementing PowerQuery Option

Moreover, we can implement another handy feature of Excel, that is to say, the PowerQuery option to remove blank rows present within the spreadsheet.

📌 Steps:

  • To start with, insert an Excel Table as shown previously >> go to the Data tab, and click the From Table/Range option.

Implementing PowerQuery Option

  • Not long after, follow the steps in real time as shown in the GIF.

Removing Rows in PowerQuery

  • Then, move to the Close & Load drop-down >> select the Close & Load To option.

Choosing Close & Load to option

  • Afterward, choose the Existing worksheet option and select the F4 cell.

Inserting table into existing sheets

Subsequently, the results should look like the screenshot below.

how to delete blank cells in excel and shift data up using PowerQuery


How to Delete Empty Cells and Shift Data Up Using Excel VBA

Additionally, if you often need to delete empty cells and shift data up, then you may consider the VBA code below. It’s simple & easy, just follow along.

📌 Steps:

  • First, navigate to the Developer tab >> click the Visual Basic button.

How to Delete Empty Cells and Shift Data Up Using Excel VBA

Now, this opens the Visual Basic Editor in a new window.

  • Second, go to the Insert tab >> select Module.

Inserting Module

For ease of reference, copy the code from here and paste it into the window as shown below.

Sub Delete_blank_cells()

    With Range("B4:D16")
        If WorksheetFunction.CountA(.Cells) > 0 Then .SpecialCells(xlCellTypeBlanks).Delete Shift:=xlShiftUp
    End With
    
End Sub

VBA code for how to delete blank cells in excel and shift data up

Code Breakdown

Now, in the following section, we’ll explain the VBA code used to delete blank cells and shift data up.

  • In the first portion, the sub-routine is given a name, here it is Delete_blank_cells().
  • Next, use the With statement and Range property to set the range of the dataset.
  • Then, use the If statement and CountA function to check whether the cell is blank, in which case, use the Delete method to remove the blank rows.

Code explanation how to delete blank cells in excel and shift data up

  • Now, click the Run button or press the F5 key to execute the macro.

Running macro

Eventually, the results should look like the image given below.

how to delete blank cells in excel and shift data up using VBA Code

Read More: How to Delete Blank Cells and Shift Data Left in Excel


How to Delete Blank Rows in Excel and Shift Data Up Using Formula

Last but not least, we can combine various Excel functions to delete blank rows and shift data up. In this situation, we’ll use the IFERROR, INDEX, SMALL, IF, and ROWS functions to copy and paste the data in a different location while removing the blank cells.

📌 Steps:

  • At the start, copy and paste the expression into the F5 cell.

=IFERROR(INDEX(B:B,SMALL(IF(B$5:B$16<>"",ROW(B$5:B$16)), ROWS(B$5:B5))), "")

Here, the B5:B16 cells refer to the “Name” column.

Formula Breakdown
  • ROW(B$5:B$16) returns the serial number of the row.
    • Output → {5;6;7;8;9;10;11;12;13;14;15;16}
  • ROWS(B$5:B5)returns the total row numbers in the given range.
    • Output → 1
  • IF(B$5:B$16<>””,ROW(B$5:B$16)) → checks whether a condition is met and returns one value if TRUE and another value if FALSE. Here, B$5:B$16<>”” is the logical_test argument that checks the B5:B16 range for blanks. The function returns the ROW(B$5:B$16) if the test holds TRUE (value_if_true argument) otherwise it returns FALSE (value_if_false argument).
    • Output → {5;6;FALSE;8;FALSE;10;11;FALSE;13;FALSE;15;16}
  • SMALL(IF(B$5:B$16<>””,ROW(B$5:B$16)), ROWS(B$5:B5))becomes
    •  SMALL({5;6;FALSE;8;FALSE;10;11;FALSE;13;FALSE;15;16}) returns the kth smallest value in data set.
    • Output → 5
  • INDEX(B:B,SMALL(IF(B$5:B$16<>””,ROW(B$5:B$16)), ROWS(B$5:B5)))becomes
    • INDEX(B:B,5)returns a value at the intersection of a row and column in a given range. In this expression, the B:B is the array argument which is the “Name” column. Next, 5 is the row_num argument that indicates the row location.
    • Output“The Grass is Always Greener”
  • IFERROR(INDEX(B:B,SMALL(IF(B$5:B$16<>””,ROW(B$5:B$16)), ROWS(B$5:B5))), “”) → becomes
    • IFERROR(“The Grass is Always Greener”, “”)  → returns value_if_error if the expression has an error and the value of the expression itself otherwise. Here, “The Grass is Always Greener” is the value argument, and “” is the value_if_error argument.
    • Output → “The Grass is Always Greener”

How to Delete Blank Rows in Excel and Shift Data Up Using Formula

Finally, the results should look like the picture shown below.

how to delete blank cells in excel and shift data up with Formula


Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Practice Section


Download Practice Workbook


Conclusion

To sum up, we hope this tutorial has provided you with helpful knowledge on how to delete blank cells in Excel and shift data up. Now, we recommend you apply all this know-how in the practice dataset by downloading the practice workbook. In addition, feel free to comment and provide your valuable feedback.


Related Articles


<< Go Back to Remove Blank CellsBlank Cells | Excel Cells | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo