Sometimes, when working on Microsoft Excel, there may be a need to shift cells in different places according to our needs. The process of moving cells in Excel is mandatory for Excel beginners. In this article, I am going to show you how to shift cells in Excel.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
5 Easy Ways to Shift Cells in Excel
In this article, you will see five easy ways to shift cells in Excel. In the first procedure, I will use the Copy and Paste command to copy a range of cells into another place. Then, I will utilize dragging and dropping to shift cells. Thirdly, I will use the Insert option from Excel to shift rows and columns. Fourthly, I will demonstrate how to shift a cell along a row or column in Excel. Lastly, I will apply a VBA code for shifting a particular cell range.
To demonstrate my further procedure, I will use the following data set.
1. Using Copy and Paste Commands
In the first procedure, I will copy the data from a whole column, then paste it into another place on the worksheet, thus shifting the cell values. To do that, I will use the Copy and Paste command in Excel. For a better understanding, see the following steps.
- First of all, select the cell range B5:B9, as I want to copy the names of the employees rather than write them again.
- Secondly, right-click on the mouse after selecting the cell range and then choose Copy from the context menu.
- Additionally, you can press the keyboard shortcut CTRL + C to copy the cell range.
- Thirdly, select a cell from the destination cell range and then again right-click on the mouse and select the Paste icon like the following image.
- Moreover, you can press CTRL +V on your keyboard to do the same.
- Finally, you will find the copied data in cell range B12:B16.
- If you want to move cells in Excel with a keyboard and use shortcuts, then select any cell with data, then press CTRL + X, thirdly with the help of the arrow keys of the keyboard go to the desired location and press CTRL + V.
2. Utilizing Dragging and Dropping
The second procedure will demonstrate how to shift cells from a data set to another location, without using any shortcuts or commands. Here, I will use the dragging feature to drag the cell range and drop them in the desired location.
- First of all, select the cell range (B5:B9) for shifting.
- Secondly, move your mouse to the border of any side of the selected cell range, and you will find your mouse with a double cross arrow like the following image.
- Then, drag the mouse icon to the desired cell location and drop it there.
- Finally, you will find the dragged cell range in the destination.
- Additionally, you can follow this method to shift any cells in an up, down or sideward positions.
3. Applying Insert Option to Shift Cells in Excel
I will apply the Insert option of Excel to shift cells in the third procedure. By applying this option, you can shift both columns and rows in Excel. For the detailed procedure, see the following steps.
- First of all, I will show you the process to shift an entire column.
- In order to do that, select the desired column header at the top of the data set in the worksheet.
- Secondly, right-click on the mouse and select Insert.
- Consequently, it will shift the existing column to the right and create a new column in that place.
- Then, fill up that column with the required data.
- Furthermore, to shift rows in Excel, select the desired row header on the right side of the data set.
- Then, again right-click on the mouse and select Insert.
- Consequently, you will see the newly created row at row number 7.
- Finally, fill up the newly created row with the necessary data.
4. Shifting Cells Along Column and Row
In this method, I will show you how to shift a single cell along a row or column in Excel. Here, I will not shift the entire row or column. See the following steps for a better understanding.
- Firstly, select any cell from the data set and right-click on the mouse.
- Then, from the context menu, choose the Insert command.
- Secondly, to shift cells along the row, choose Shift cells down from the Insert dialog box.
- Then, press OK.
- Thirdly, you will see the selected cells have been shifted along the row by one row.
- Moreover, to shift the cell along the column select any cell from the data set.
- Then, choose Insert from the context menu after right-clicking on the mouse.
- Fifthly, select the Shift cells right command from the Insert dialog box.
- Then, press OK.
- Finally, this will shift the cells along the column by one column.
5. Applying VBA to Shift Cells in Excel
For the last method of this procedure, I will apply a VBA code to shift cells from one location to another. By giving the correct sequence or command in the code, I will execute this action.
- First of all, I will shift the value of the cell range B5:B9 to the cell range B12:B6 through VBA.
- In order to do that, go to the Developer tab and select Visual Basic.
- Secondly, you will see the VBA window after the previous step.
- Then, from the Insert tab use Module.
- Thirdly, copy the following VBA code and paste it into the module.
Sub Shifting_Cells_in_Excel() 'Selecting the cell range for shifting Range("B5:B9").Select 'Cutting the cell range and selecting new cell destination Selection.Cut Destination:=Range("B12:B16") 'Shifting cells in new destination Range("B12:B16").Select End Sub
- Firstly, set the name of the sub-procedure.
- Secondly, select the cell range for shifting.
- Then, cut the selected cell range and paste them into the desired cell range location.
Selection.Cut Destination:=Range("B12:B16") Range("B12:B16").Select
- Fourthly, save the code in the module.
- Then, keeping the cursor in the module, press F5 or the Play button.
- Finally, you will find the cell range in the desired destination after playing the code.
Applying Formula to Move Data from One Cell to Another
In this section, I will show you how to move data from one cell to another by applying formulas in Excel. The process of doing this is very simple. Follow the below-given steps for a better understanding.
- First of all, take the following data set to perform the procedure.
- Here, I will apply a formula to move the employee name.
- In order to do that, write the following formula in cell C11 to copy the data of cell B8.
- Finally, after pressing Enter, it will show the same data as cell B8.
Easy Way to Move Partial Data from One Cell to Another in Excel
Now, in the last section of this article, I will show you an easy way to move partial data from one cell to another in Excel. Here, the cells will contain a large string of data and after performing some steps, the data will be separated into different cells. To do that, see the following steps.
- Firstly, look at the following data set where each cell contains more than one cell value.
- So, I will demonstrate the way to move partial data from these cells.
- In order to do that, select the cell range B4:B8 and then go to the Data tab of the ribbon.
- Then, from the Data Tools group select Text to Columns.
- Secondly, you will see the Convert Text to Columns Wizard dialog box with steps 1 to 3.
- In Step 1 of the dialog box, firstly, choose Delimited and then Next.
- Thirdly, in the Step 2 dialog box, select Space to adjust them in different cells.
- Then, press Next.
- Fourthly, in Step 3 of the dialog box press Finish.
- Finally, after performing all the steps, you will find the values of the cells separated in various cells.
That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to shift cells in Excel. Please share any further queries or recommendations with us in the comments section below.
The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions ever.