How to Insert Multiple Rows in Excel at Once

Need ten new rows in the middle of your list? You don't have to right-click and choose Insert ten times. Excel inserts as many rows as you select, all in one go.

Select the same number of rows as you want to insert. Select 5 rows, get 5 new rows. The new rows appear above your selection.

1. Click the row number where you want the new rows to start. For new rows above row 10, click 10.
2. Hold the mouse button and drag down to select as many rows as you want to add. Drag to row 14 to add 5 rows. The tooltip shows how many you've selected, e.g. 5R.
3. Right-click anywhere in the selection.
4. Choose Insert.

The five new blank rows appear above the original row 10, and everything below moves down.

1. Click a cell in the row where you want the new rows.
2. Press Shift + Space to select the whole row.
3. Hold Shift and press the Down arrow to extend the selection to as many rows as you need.
4. Press Ctrl + Shift + + (the plus key). On a laptop without a number pad, that's Ctrl + Shift + =.

On a Mac, press Control + I, or Command + Shift + +.

Everything above works for columns too. Select several column letters, right-click and choose Insert. New columns appear to the left of your selection.

Keyboard: Ctrl + Space selects a column, Shift + Right arrow extends it, then Ctrl + Shift + +.

After inserting rows once, click somewhere else and press F4 (or Fn + F4 on a laptop). Excel repeats the insert with the same number of rows. It's great for adding the same gap in several places.

For spacing out a list, there's a neat sorting trick:

1. In an empty column next to your data, number the rows 1, 2, 3… all the way down (type 1 and 2, select both and double-click the fill handle).
2. Copy those numbers and paste them directly underneath the last one, so the list runs 1 to the end twice.
3. Select all your data including the helper column, and sort by the helper column smallest to largest.
4. Delete the helper column.

Each original row is now followed by a blank one.

Select the rows the same way, right-click and choose Delete, or press Ctrl + − (minus).

If your data is formatted as a Table, right-click a cell and choose Insert > Table Rows Above. To add rows at the end, just start typing in the row directly below the table, and it'll grow automatically.

Insert is greyed out, or you see "Microsoft Excel can't insert new cells". Excel would have to push data off the bottom of the sheet. There's usually something in the very last row, such as stray formatting or a formula copied all the way down. Press Ctrl + End to see where Excel thinks your data ends, delete the unwanted rows below your real data, then save and try again.

The sheet is protected. Go to Review > Unprotect Sheet, or ask the owner to allow row insertion when protecting.

Formulas didn't include the new rows. If you insert rows at the very bottom of a range, SUM formulas may not expand. Insert inside the range, or use a Table.

Select as many rows as you need, right-click and choose Insert, or press Ctrl + Shift + +. New rows go above, new columns go to the left, and F4 repeats it anywhere else.