Shahin Mahmud
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
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)
- Now select the row where you want to move the copied row to and Right Click > Insert Copied Cells.
- A dialogue box will appear to ask you how it will place the copied data. Select Shift Cells Down.
- Now delete the copied row.
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).
- Now select the row where you want to move the copied row to and Right Click > Insert Copied Cells.
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).
- 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.
- Now drop the row where you want it. Done!
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.
- 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.
- Now drop the column where you want it. Done!
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.
- Now select Blanks.
- Excel will automatically identify blanks in the range. Now right-click on the selection and select Delete.
- 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.
See. We did it !!
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.
” title=”