Inserting and deleting rows and columns

Hello students, welcome to Kwickprep. You finished a class list, and then a new student joins in the middle of the roll numbers. Do you retype the whole list? No. Today we will learn to insert and delete rows and columns, and how formulas react when you do.

First, three words that sound similar but work differently. Insert adds new empty rows or columns, and pushes the existing data aside. Delete removes the cells completely, and the data below or to the right moves in to close the gap. Clear only empties the cells, and the empty cells stay in place. Be careful, because the Delete key on the keyboard only clears contents, it does not delete the row.

Where does a new row or column appear? A new row is inserted above the selected row, and the rows below move down. A new column is inserted to the left of the selected column, and the columns move right. When you insert only some cells, Excel asks whether to shift the existing cells down or to the right.

Let us insert a row for the new student. First, click row number 6, where the new student should go. Right-click the row header. Choose Insert from the menu. A blank row appears as row 6, and the old row 6 moves down to row 7. Now type the new student's details in the empty row.

There are several ways to insert. The right-click menu on a row or column header is the quickest. On the ribbon, go to Home, then the Cells group, then Insert, and choose Insert Sheet Rows or Insert Sheet Columns. The shortcut is Ctrl Shift and the plus key, or Ctrl plus on the number pad, after selecting a whole row. To insert many at once, select three rows first, and Insert adds three new rows together.

Deleting works the same way. Right-click the row or column header and choose Delete. On the ribbon, go to Home, then Cells, then Delete, and choose Delete Sheet Rows or Delete Sheet Columns. The shortcut is Ctrl with the minus key, after selecting the whole row or column. Select several rows or columns first to delete them all together.

If you select only some cells, not a whole row, Excel opens a small box. For example, select two cells and press Ctrl with plus. The box asks whether to shift cells right or shift cells down. It also offers Entire row or Entire column. The Delete box is similar, with shift cells left and shift cells up.

What happens to formulas? Suppose row 11 has equals SUM of B2 colon B10, the total of the marks. If you insert a row inside that range, Excel changes the formula to B2 colon B11 by itself. If you delete a row inside the range, the range shrinks. But if you delete a cell that a formula points to directly, it shows the hash REF error. That means the reference is broken.

Now pause and predict. Cell C1 contains equals A1 plus B1. You delete the whole of column B. What does the formula become? Column C moves left to become column B. The formula now reads equals A1 plus hash REF, because the cell it needed was deleted.

Here are four safety tips. If you insert or delete the wrong thing, press Ctrl+Z at once to undo it. Select the whole row or column by its header, so you do not shift only a few cells by mistake. If you might need the data later, hide the row instead of deleting it. And save the workbook before any big change, so you always have a safe copy.

Let us revise what we learned today. Insert adds space, Delete removes cells, and Clear only empties them. A new row goes above the selection, and a new column goes to the left. Ctrl with plus inserts, and Ctrl with minus deletes. Formulas adjust themselves, but a deleted reference gives the hash REF error. And Ctrl+Z undoes a mistake, so practise on a copy of your class list today.