Whenever new cells are inserted into a worksheet, existing cells have to shift in order to make room to the newly inserted cells. When you insert entire rows or columns, the direction in which data will be moved is predetermined. If you insert an entire column, existing data is shifted to the right. If you insert an entire row, any existing data is shifted down. However, when you insert cells rather than entire rows or columns, Excel will always ask you in which direction we want the existing data to be shifted: to the right or down.

To insert a row of cells, highlight the cells above which you want the new cells inserted then choose Home Tab – Cells – Insert – Insert Cells. Excel will then display a dialogue box asking you to specify what you would like to do with the existing data and, naturally, you would choose “Down”. When you click OK, the existing data moves down and a range of cells is inserted of the same size as the range that was highlighted.

These exact same rules apply when we delete cells. To delete an unwanted range of cells, select the appropriate cells and choose Home – Cells – Delete – Delete Cells. Alternatively, you can right-click on the selected range of cells and choose Delete from the context menu. As before, Excel displays a dialogue box asking you to specify the direction in which you want the existing data to move. This time, however, data is being moved in order to fill the gap created by the Delete command, the directions offered to us are “Up” and “Left”. To delete a row of cells, we would choose “Up” and to delete a column “Left”.

When you are inserting cells, the format of the newly inserted cells will always match that of the cells which are selected when the Insert command is used.

Inserted cells are normally blank. However, it is possible to cut or copy a range of cells and then, instead of pasting them, to insert them above or to the left of a given range. To do this simply copy or cut the desired range and then highlight the range of cells above or to the left of which you want the cells inserted. If you right-click, you will notice that the context menu displays the command “Insert Copied Cells” (or “Insert Cut Cells” if the cut command was used) in place of the normal “Insert” command. Similarly in the Insert drop-down menu in the Cells group of the Home Tab of the Excel Ribbon, “Insert Cells” has also been replaced by “Insert Copied (or Cut) Cells”. If the copy command is used, having inserted cells, to exit copy mode and get rid of that flashing border around the copied cells, simply press the Escape key on your keyboard.