How to Shift Data Up in Excel (3 Easiest Ways)

Microsoft Excel not only helps you arrange your data but also helps you to analyze it. It can help you to improve your productivity to a new level. Sometimes when processing a large amount of data there is a need to arrange your data that is more convenient to you or it so happens that there are some undesirable blank cells amidst your data. Today we will learn some ways to arrange your data so that you can be more productive.

Practice Workbook

About the Practice Workbook

Data Sheet

Here is the list of a Sports Accessories shop’s seller list. We can see the names of items sold by salesmen with their selling amount. There is a blank row in between the list too. We will learn how to arrange the rows and columns. We will also learn how to remove the blank row and move the last row one row up so that there is no blank.

3 Ways to Shift Data Up in Excel

1. Traditional Method

This method is very easy to understand but it is a bit lengthy process.

i. To Move a Row Up or Down

  • Select the row you want to move by using a mouse or keyboard (I’d prefer a mouse if the row is small but for a large row use a keyboard.) Now Copy it using CTRL+C or use the Ribbon while in the Home tab (as shown in the picture)

 Row Selection and Copy

  • Now select the row where you want to move the copied row to and Right Click > Insert Copied Cells.

Row Selection to Copy there using insert cell

  • A dialogue box will appear to ask you how it will place the copied data. Select Shift Cells Down.

Insert Cell Down

  • Now delete the copied row.

Copy Done

Wondering if you can use the Cut command instead of Copy? Of course, yes. In fact, Cut is far easier than Copy because this way you are removing the copied row. So you don’t need to follow the last instruction.

ii. To Move a Column Left or Right

Similarly, you can move a column right or left. This time we will learn it using the Cut command. Obviously, we already know the Copy/Cut command is nearly identical except in Copy you have an extra step of deleting the copied column.

  • Select the column you want to move by using a mouse or keyboard (I’d prefer a mouse if the row is small but for a large row use the keyboard.) Now Cut it using CTRL+X or use the Ribbon while in the Home tab (as shown in the picture).

Column Selection and Cut

  • Now select the row where you want to move the copied row to and Right Click > Insert Copied Cells.

Column Selection to Cut there using insert cell

Done.

Column Move Done

Read More: How to Move Rows up in Excel (2 Methods)

2. The Drag-Drop Method ( The Easiest of them all)

This is the easiest method. No more Copy/Cut and Paste!! Let’s see how.

i. To Move a Row Up or Down

  • Select the row you want to move up or down and bring the cursor to the upper or lower border while pressing the Shift key. The cursor will change itself to move-cursor (you can see a 4 direction arrow underneath it).

Select and Move Icon

  • Holding the Shift key when you drag the row up and down you’ll see a line that will determine where the new row will be.

Guide line Row

  • Now drop the row where you want it. Done!

Row Movement

ii. To Move a Column Left or Right

Now you know how to move a row it is very easy to move columns in the same way!!

  • Select the column you want to move right or down and bring the cursor to the upper or lower border while pressing the Shift key. The cursor will change itself to move-cursor (you can see a 4 direction arrow underneath it).

But be careful not to select the whole column (it may give you an error). So only select cells that do not contain any merged cell. In our example, there is a merged cell “Shift Data Up in Excel”. So we are selecting underneath that cell.

Column Selection

  • Holding the Shift key when you drag the column right and left you’ll see a line that will determine where the new column will be.

Guide line Column

  • Now drop the column where you want it. Done!

Column Movement

Read More: How to Shift Cells Right in Excel (4 Quick Ways)

3. Using Go To Special Function

This method is useful when you have a blank row in your data. So what if you have a blank row? Having a blank row can cause some problems in Excel’s calculations (like Average). So it is imperative that you don’t have empty cells in your dataset let alone a full row. Now that you know the effect of blank rows let’s see how we can remove empty row (we have an empty row in our sample workbook)

  • Select the Range (here B6 to D14 or in Excel B6:D14) and then press F5. This will open a dialogue box named Go To and Select Special.

Go to

  • Now select Blanks.

Select Blank

  • Excel will automatically identify blanks in the range. Now right-click on the selection and select Delete.

Delete Cell

  • A dialogue box will appear which will ask you what to do. Select Shift Cells Up. This will replace an empty row (or any empty cell for that matter) with the immediate next one.

Shift Cell and Remove Empty Cell

See. We did it !!

Removed and Done!

Related Content: Fix: Excel Cannot Shift Nonblank Cells (4 Methods)

Conclusion

This way you can easily move data in a row or a column and remove empty rows and columns. It may be a simple operation but it is important for organizing your data. Thank you for taking the time and checking out this article. We are here to help. Like you, we are also improving and learning new things. So feel free to rate this article and in the comment section give us suggestions about how we can improve our articles. Which part of this article needs improvement? Which part do you think is most useful? You can bookmark us for anything excel.


Related Articles

Shahin Mahmud

Shahin Mahmud

Hello I am Shahin Mahmud. I love working with Excel. I am a Civil Engineer holding a Bachelor’s degree from Bangladesh University of Engineering and Technology. I am interested in anything related to computers and phones. I also like to help people facing computer related problems and write articles about them.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo