Tricks to Insert, Delete, Hide Rows and Columns

Select a row

To select a row easily:

  1. Place the mouse cursor on one of the row numbers.
  2. Click when the cursor changes to a horizontal arrow pointing to the right.
Arrow cursor to select the row

You can also select a row using the keyboard shortcut Shift + Spacebar. The selected row is the one containing the active cell (B3 in our example).

Shortcut Shift Space

Select a column

To select a column:

  1. Position the mouse cursor on one of the letters in the column headers.
  2. Click when the cursor changes to an arrow pointing downwards.
Arrow cursor to select the column

You can select a column with the keyboard shortcut Ctrl + Spacebar. The selected column will be the one containing the active cell.

Shortcut Ctrl Space

Here's a tip to remember that Ctrl + Space selects the column: Ctrl and Column both start with a C (easy 😉).

Inserting a column or a row

The best-known technique for inserting a row is to:

  1. Right-click in the column header.
  2. Select the Insert option.
Insert column with the right click

There's also a second, more practical method: click and drag.

  1. Select a column.
  2. Place the cursor on the square.
  3. Press the Shift key (the cursor changes).
  4. Click and drag.
Insert column with the mouse

This also works for rows.

Insert Rows with the mouse

Consequences for formula references

Note that inserting a row or column will not disrupt any formulas you already have.

  • Here, the initial formula is =B2*C2.
  • After adding column C, the formula becomes =B2*D2.

Excel automatically adjusted the column C reference to ensure the formula consistently points to the appropriate cells.

Impact of an insert in the reference of a formula

Deleting columns

Deleting a column is as simple as adding one:

  1. Select the column to delete.
  2. Right-click on the header.
  3. Select the Delete option.
Delete column with a right click

Or you can use the shortcut Ctrl + -.

  1. Select the column you want to delete.
  2. Press the combination Ctrl + - (minus or dash).

Deleting a column used in a formula

WARNING: if you delete a column that is used in a formula, the impact on your calculations can be devastating 😱😱😱.

In this situation:

  1. Deleting column C has no impact on the formula, since it doesn't use this column in its calculation (=B2*D2).
  2. In the second case, column C is used in the formula (=B2*C2):
    • Deleting column C results in a #REF! error, because the cells that referenced this column no longer have a valid reference.
    • The only way to restore your previous formulas is to press Ctrl + Z to undo the last action.
Delete column used in a formula

You now know how to select, insert and delete rows and columns safely in Excel, and how your formulas react so you avoid the classic #REF! error.

Continue learning

Keep building your Excel skills with the rest of this tutorial series:

Most Viewed Posts