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.
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.
- Next, click the Delete drop-down >> press Delete Sheet Columns.
- 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.
Voila! That is how simple it is to remove blank cells in Excel and shift data up.
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.
- Then, in the Go To Special window, click the Blanks button>> hit OK.
- Afterward, right-click to open the Context Menu >> choose Delete.
- In turn, check the Shift cells up option to remove all the blank rows.
- Now, select the blank column >> select the Shift cells left option >> press OK.
Boom! This deletes the blank cells as shown in the image below.
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.
- At this point, click any down-arrow button >> check the Blanks option >> enter the OK button.
- Again, click Delete Sheets Rows to remove all the blank rows.
- Next, click the down-arrow button >> check the Select All option >> click on OK.
- In a similar style, select the blank column >> press Delete Sheet Columns.
Finally, the results should look like the picture shown below.
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.
- At this point, select the blank rows >> use the CTRL + – (Minus) keys to delete them.
- Likewise, select the blank column >> press CTRL + – (Minus) keys to delete the column.
Eventually, your results should resemble the screenshot below.
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.
- Second, in the Find and Replace, check the Match entire cell contents option >> in the Look in field, select Values option >> press Find All.
- Third, the top result and press the CTRL + A keys to select all >> hit Close.
- Fourth, apply the Delete Sheet Rows option.
Consequently, the results should appear in the screenshot below.
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.
Ultimately, the final output should look like the figure below.
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:
- From this point, proceed to the Table Design tab >> rename the Table as “Best_Sellers”.
- Lastly, move to the F5 cell >> insert the formula into the Formula Bar.
=FILTER(Best_Sellers,Best_Sellers[[Name ]]<>"" )
- 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.
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.
- Not long after, follow the steps in real time as shown in the GIF.
- Then, move to the Close & Load drop-down >> select the Close & Load To option.
- Afterward, choose the Existing worksheet option and select the F4 cell.
Subsequently, the results should look like the screenshot below.
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.
Now, this opens the Visual Basic Editor in a new window.
- Second, go to the Insert tab >> select 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
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.
- Now, click the Run button or press the F5 key to execute the macro.
Eventually, the results should look like the image given below.
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.
- 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”
Finally, the results should look like the picture shown below.
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.
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.
