How to Shift Cells Up in Excel – 5 Quick Methods

An example dataset is shown below.

Use the Find & Replace Option

Method 1 – Use Drag to Shift Cells Up in Excel

Step 1:

  • Select the cells you want to shift.

Apply Drag to Shift Cells Up in Excel

Step 2:

  • Hold the mouse Leftclick and shift upwards.

Apply Drag to Shift Cells Up in Excel

The cells will be shifted up.

Apply Drag to Shift Cells Up in Excel

Read More: How to Shift Cells Down in Excel


Method 2 – Right-Click to Shift Cells Up in Excel

Step 1:

  • Select the blank cells.

Use the Right-click to Shift Cells Up in Excel

Step 2:

  • Right-click to see options.
  • Choose Delete.

Use the Right-click to Shift Cells Up in Excel

Step 3:

  • Select Shift Cell Up.
  • Press Enter.

Use the Right-click to Shift Cells Up in Excel

Cells will be moved up.

Use the Right-click to Shift Cells Up in Excel


Method 3 – Use the Sort & Filter Command to Shift Cells Up in Excel

Step 1:

  • Select all cells in the range.

Apply the Sort & Filter Command to Shift Cells Up in Excel

Step 2:

  • In the Data tab choose Filter.

Apply the Sort & Filter Command to Shift Cells Up in Excel

Step 3:

  • Click the Icon.
  • Unmark Blanks.
  • Press Enter.

Apply the Sort & Filter Command to Shift Cells Up in Excel

All blanks in the range will disappear and the cell will be moved up.

Apply the Sort & Filter Command to Shift Cells Up in Excel


Method 4 – Use the Find & Replace Option to Shift Cells Up in Excel

Step 1:

  • Select all cells.

Use the Find & Replace Option to Shift Cells Up in Excel

Step 2:

  • Go to the Home tab and choose Find & Replace.
  • Choose Go To Special.

Use the Find & Replace Option to Shift Cells Up in Excel

Step 3:

  • Select Blanks.
  • Press Enter.

Use the Find & Replace Option to Shift Cells Up in Excel

Step 4:

  • Right-click a blank cell.
  • Choose delete.

Use the Find & Replace Option to Shift Cells Up in Excel

Step 5:

  • Choose Shift cells up.
  • Press Enter to see the results.

Use the Find & Replace Option to Shift Cells Up in Excel

Cells will be shifted.

Use the Find & Replace Option


Method 5 – Run a VBA Code to Shift Cells Up

 

Use the Find & Replace Option

Step 1:

  • Press  Alt + 11 to open the Macro.
  • Click Insert.
  • Choose Module.

Use the Find & Replace Option

Step 2:

  • Enter the following VBA code.
Sub Shift_cells_up
Dim lRow As Long
Dim iCntr As Long
lRow = 20
For iCntr = lRow To 1 Step -1
If Cells(iCntr, 1) = 0 Then
Range("A" & iCntr).Delete Shift:=xlUp
End If
Next
End Sub

Code Breakdown

lRow = 20 refers to the total row in the range.

For iCntr = lRow To 1 Step -1 checks IRow.

If Cells(iCntr, 1) = 0 is the If condition for blank cells.

Range(“A” & iCntr). is your range column

Delete Shift:=xlUp deletes rows and shifts cells up

Use the Find & Replace Option

This is the result.

Use the Find & Replace Option

Read More: How to Move Down One Cell Using Excel VBA


Download Practice Workbook

Download this practice workbook to exercise.


Related Articles


<< Go Back to Excel Cells | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo