Insert Rows
Excel never asks how many rows you want — the size of the selection is the instruction, which is why one row keeps appearing where five were wanted. The pages below divide along that line: a block of rows in one place, one row above each of several scattered places, rows inside a Table that have to carry its formulas down with them, or a sheet that refuses to insert anything at all. Whichever it is, check the totals afterwards. A range spanning the insertion point stretches to include what arrived; one that begins exactly there does not, and nothing warns you either way.
The formula
Shift+Space select the whole row the cursor sits in
Ctrl+Shift+= insert rows above the selection (Ctrl+Plus on a numeric keypad)
select 5 rows, then Insert inserts 5 rows at once, above the selection
Ctrl+click rows 4, 9, 15 then Insert — one new row above each of the three
F4 repeats the last insert, one row per pressA worked example
A 200-row order list, and a late batch of three orders has to go in above row 12.
Click row number 12, drag down to row 14 to select three rows, then right-click > Insert
Rows 12, 13 and 14 are new and empty, and the old row 12 has moved down to row 15. A =SUM(B2:B199) that spanned the insertion point widens itself to =SUM(B2:B202); one that started exactly at the insertion point would not, which is worth checking before you trust the total.
Which one do I need?
| If you want to… | Use |
|---|---|
| Adding several rows in one go | Select that many existing rows first — Excel always inserts as many rows as the selection covers |
| Adding a row above each of several scattered rows | Ctrl-click each row number, then Insert once; every selected row gets a new one above it |
| Adding rows inside an Excel table | Click the last cell and press Tab, or right-click > Insert > Table Rows Above — the table formulas fill down by themselves |
| Adding columns rather than rows | The same moves sideways: select column headings, right-click > Insert |
| Taking rows out instead of putting them in | Select the row numbers, right-click > Delete; Ctrl+Minus is the keyboard version |
| Clearing out the empty rows already scattered through the data | Ctrl+G > Special > Blanks to select them, then Home > Delete > Delete Sheet Rows |
Frequently asked questions
Why does Excel say it cannot insert a row?
The message "To prevent possible loss of data, Excel cannot shift nonblank cells off the worksheet" means something occupies the very last row, 1048576 — usually stray formatting rather than anything you can see. Press Ctrl+End to find where the used range really ends, select every row from below your data down to that point, delete them, then save and reopen the file. A protected sheet refuses inserts for a different reason, and says so.
Do inserted rows copy the formatting of the row above?
Yes, by default — which is why a row inserted into a shaded block arrives already shaded. A small paintbrush button appears beside the new row straight after the insert; click it to pick Format Same As Below or Clear Formatting instead. It disappears as soon as you do anything else, so use it immediately.
How do I insert a row without touching the mouse?
Shift+Space selects the row the cursor is in, then Ctrl+Shift+= inserts one above it (Ctrl+Plus if you have a numeric keypad). Hold Shift and press the down arrow before inserting to extend the selection over several rows and add that many at once. F4 repeats the whole insert without reselecting.
Do my formulas update when I insert a row?
Ranges that span the insertion point stretch to cover it, so =SUM(B2:B50) becomes =SUM(B2:B51). A row added directly above the first cell of a range is excluded: with a new row inserted at row 2, that same formula shifts to =SUM(B3:B51) and quietly ignores the entry you just made. Inserting inside the range rather than at its edge avoids the trap.
New guides and tools, once a month
DE + EN · double opt-in · no spam