How to Insert Multiple Rows in Excel at Once

To insert multiple rows in Excel at once, select as many rows as you want to add, then insert. Select three rows and Excel adds three blank rows above them. The fastest way is the keyboard: Shift+Space, then Shift+↓, then Ctrl+Shift+= (Mac: Control+Shift+=). This guide covers that, plus how to add a hundred rows, add rows in several places, and fix the error when Excel won't insert anything.

The rule: Excel inserts as many rows as you select#

Excel looks at how many whole rows are selected and inserts that many blank rows above the selection. The selected rows and everything below them move down. Nothing is deleted.

So the only question is how to select the right number of rows. Rows are the horizontal lines of cells, numbered on the left. The cells, rows and columns lesson covers the basics if you're new to the grid.

Insert multiple rows with the mouse#

The mouse method is easy to remember:

1. Click the row number where the new rows should start, for example 5.
2. Hold Shift and click the row number of the last row, for example 7. Rows 5 to 7 are now selected.
3. Right-click any of the selected row numbers and choose Insert.

Three blank rows appear at rows 5 to 7, and the old rows 5 to 7 move down to 8 to 10. You can also click and drag down the row numbers instead of Shift-clicking, or use Home > Insert > Insert Sheet Rows.

Insert multiple rows with the keyboard#

The keyboard is faster once you know the keys:

For example, to add four rows above row 12, click a cell in row 12, press Shift+Space, press Shift+↓ three times, then press Ctrl+Shift+=. If you press the insert shortcut with only cells selected, not whole rows, Excel opens a small Insert box. Choose Entire row and press Enter.

These selection keys are covered in the lesson on selection shortcuts, and the guide to inserting and deleting rows and columns has the full set of shortcuts for single rows and columns.

How to insert 100 rows (or more) at once#

Pressing Shift+↓ a hundred times is slow. Use the Name Box instead. It's the small box to the left of the formula bar that shows the current cell, like A1.

1. Click in the Name Box.
2. Type the rows you want to select, for example 10:109, and press Enter. That selects 100 whole rows, from 10 to 109.
3. Press Ctrl+Shift+= (Mac: Control+Shift+=), or right-click a selected row number and choose Insert.

100 blank rows appear above the old row 10. Change the numbers for any amount: 10:509 gives 500 rows. The Name Box tips guide shows other ways to jump and select with it.

Insert rows in several places at once#

To add one row above several different rows, select them together:

1. Click the first row number, for example 4.
2. Hold Ctrl (Mac: ⌘) and click the other row numbers, for example 8 and 15.
3. Right-click one of the selected row numbers and choose Insert.

Excel inserts one blank row above each selected row. If you select two rows next to each other in one of the groups, that group gets two rows.

Another way is to repeat your last action. Insert a row in the first place, click the next place, and press F4 (Mac: ⌘Y). This is handy when the places are far apart.

Insert a blank row between every row#

Sometimes you need a blank row after every data row, for example to make a printed list easier to read. There is no button for this, but a helper column and a sort do it in a few seconds:

1. In an empty column next to the data, number the rows 1, 2, 3 and so on. Five data rows get 1 to 5.
2. Copy those numbers and paste them directly below the last one, so the column has 1 to 5 twice.
3. Select the whole data range, including the helper column, and choose Data > Sort. Sort by the helper column, smallest to largest, with My data has headers ticked.
4. Delete the helper column.

Each data row now has a blank row after it, because the blank rows with the same number sort right after their partners. To go back, use the steps in deleting blank rows.

What happens to formulas and tables#

Formulas update when you insert rows inside a range. If B7 holds =SUM(B2:B6) and you insert two rows above row 4, the total moves to B9 and the formula becomes =SUM(B2:B8), so the new rows are included. Rows inserted just below the last row of a range may not be included, so check your totals after adding rows at the bottom.

In an Excel table (created with Ctrl+T; Mac: ⌘T), new rows are always part of the table and its formulas fill down by themselves. Select table rows and right-click > Insert > Table Rows Above, or press Tab in the last cell of the table to add a row at the end.

If your new rows come from another sheet, see how to combine sheets instead of inserting and pasting by hand. And if you only need rows out of the way, not gone, hiding rows is safer than deleting.

Why can't I insert rows in Excel?#

When Excel refuses, it usually shows a message saying it can't shift nonblank cells off the worksheet. A sheet has a fixed number of rows (1,048,576), and inserting would push something off the bottom. Fix it like this:

1. Click the first empty row below your data.
2. Press Ctrl+Shift+↓ (Mac: ⌘+Shift+↓) to select down to the last row of the sheet (press it again if it stops early), then Shift+Space to make it whole rows.
3. Press Ctrl+- (Mac: ⌘-) to delete those rows, then save the file.

Other causes: the sheet is protected (Review > Unprotect Sheet), or the rows cut through an old array formula. For that one, see the fix for you can't change part of an array.

Practise it#

Inserting rows quickly, without breaking totals, is one of the first things an Excel test checks. Try the exercise on this page, then practise working with cells, rows and columns in a live spreadsheet: you do each step and it checks it instantly. Follow it with the selection shortcuts lesson so selecting ten rows becomes second nature.