Click the first row number in the grey margin and drag down to the last one you want.
Once the rows you need run past the bottom of the screen, the drag has to scroll the sheet as well.
Docs · guide
How to add multiple rows in Excel, and where the rule lives
By Alberto Gulotta · Updated · 14 min read
How to add multiple rows in Excel: select as many rows as you want to insert, right-click the selection and choose Insert. Five selected rows give five new ones. That is the whole rule, and Microsoft prints it for the Mac and for the web, not in the Windows steps.
| The page | What only it tells you | Versions it lists |
|---|---|---|
| Insert or delete rows and columns, Windows tab | The ribbon route, the Insert Options button, and the size of the sheet | 365, 2024, 2021, 2019, 2016 |
| The same page, Web tab | “To insert multiple rows: Select the same number of rows” | Excel for the web |
| Insert one or more rows … in Excel for Mac | The same rule, with an example, and CONTROL-click instead of right-click | 365 for Mac, 2024, 2024 for Mac, 2021 for Mac |
| Select specific cells or ranges | The Name box, which is how you select a thousand rows without scrolling | 365, 2024, 2021, 2019, 2016 |
The rule is one sentence, and it is not where you would look for it
“To insert multiple rows: Select the same number of rows above which you want to add new ones. Right-click the selection, and then select Insert Rows.”
That is Microsoft, on Insert or delete rows and columns — in the Web tab. The Windows tab of the same page never says it. What it says instead is “Select any cell within the row, then go to Home > Insert > Insert Sheet Rows”, which inserts exactly one.
The Mac page has it too, with the example: “Select the same number of rows as you want to insert. For example, to insert five blank rows, select five rows.” And the Mac page was the only Microsoft Support page in the results Google showed for this question on 24 September 2026, which is how a Windows user can end up reading “Hold down CONTROL, click the selected rows”.
It is the same shape as the question next door: on grouping rows, Microsoft prints the Mac shortcut without the Mac procedure. Here it prints the rule for the Mac and for the browser, and leaves it out of the Windows steps.
The keyboard is where this question gets confusing, and the confusion starts on Microsoft’s own page. One page, Keyboard shortcuts in Excel, carries three different keys for this job in three different sections: the Windows section gives Ctrl+Shift+Plus to “open the Insert dialog box to insert blank cells”, the macOS section gives Ctrl+Shift+Equal to “insert cells”, and the Excel for the web section gives Ctrl+Plus to “insert cells, rows, or columns”. The accepted answer on the forum thread Google put first on 24 September 2026 gives the third one — “Press Ctrl+Plus sign (+), then press R, and click OK” — to somebody who never said which Excel they were in. The R and the OK belong to a dialog that the shortcuts page does not describe.
- Decide how many rows you want, and select that many. This is the whole rule. Microsoft’s Mac page puts it as an example: “to insert five blank rows, select five rows”. It adds “It’s okay if the rows contain data”, because the new rows go in above them.
- Select them by their numbers, in the grey margin. Microsoft’s page on selecting cells puts it in one line: “Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you select the last row or column.”
- For a big number, type the rows instead. In the Name box to the left of the formula bar, type 2:1001 and press Enter: rows 2 to 1001, a thousand of them, in the style Microsoft uses for “All cells in rows 5 through 10”, 5:10. Microsoft: “To select an unnamed cell reference or range, type the cell reference of the cell or range of cells that you want to select, and then press ENTER.”
- Right-click anywhere inside the selection and choose Insert. On a Mac, Microsoft says to “hold down CONTROL” and click instead.
- Or use the keyboard, with the key for the Excel you are in. On Windows, Microsoft lists Ctrl+Shift+Plus sign (+) to “open the Insert dialog box to insert blank cells”; on a Mac the key is Ctrl+Shift+Equal sign (=).
- Look at the small brush that appears beside the new rows. That is Insert Options, and it decides whether the new rows keep the formatting next to them or come out clean.
Microsoft’s wording for a large range: click the first cell, “then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.”
Reliable, and it still means finding row 500 with your eyes.
The small box to the left of the formula bar takes whole rows. Type 2:1001, press Enter, and a thousand rows are selected without a single scroll.
It is documented, on a Microsoft page that neither of the two insert pages links to.
Three of the four questions Google showed under this one on 24 September 2026 are about numbers — twenty rows, a hundred rows, a thousand rows — and for a thousand, dragging across the row numbers means scrolling the whole way. The Name box does it in one move, and it is documented on Select specific cells or ranges in Excel, a page that neither of the two insert pages links to. Type the rows, press Enter, then insert. The same box is how Microsoft reaches a hidden row 1, which has no row above it to click: that is on unhiding rows.
What comes with the new rows, and where the sheet stops
All four lines are Microsoft’s, read in full on 24 September 2026.
- They arrive dressed
- “When you select a row or column that has formatting applied, that formatting will be transferred to a new row or column that you insert.” Insert a row under a total and it can come out bold with a border above it.
- The button that undoes that
- Insert Options, the small brush beside the new rows. Microsoft’s picture of it shows three choices: the formatting on one side, the formatting on the other, or Clear Formatting.
- If the button is not there
- “If the Insert Options button isn’t visible, then go to File > Options > Advanced > in the Cut, copy and paste group, check the Show Insert Options buttons option.”
- How much room there is
- “Microsoft Excel has the following column and row limits: 16,384 columns wide by 1,048,576 rows tall.” Inserting pushes the rows below down, and when something sits at the bottom Excel stops with “Microsoft Excel cannot shift nonblank cells off the worksheet”. An older Microsoft page for that message names two causes: a formula added to the entire worksheet, or formatting such as a border added to a whole row or column.
Where to start
Four ways in.
- “Just tell me how.”
- One rule, and Microsoft’s own example — the rule and the page that leaves it out
- “The shortcut I was given did nothing.”
- There are three of them — one page three keys
- “I need a thousand rows.”
- Not by dragging — when you need a thousand rows
- “The new rows came out bold.”
- They inherited it, and there is a button — what the new rows bring with them
The rule and the page that leaves it out
Select as many rows as you want to insert. Microsoft writes it for the Mac and for the browser, and not in the Windows steps.
Grouping rows in ExcelMicrosoft documents the Mac shortcut without the Mac procedure, and the Windows procedure without a Windows shortcut.Open this guide →
Hidden rows in ExcelThree clicks — and the four reasons they fail, including the one where the rows were never hidden.Open this guide →
Sorting alphabeticallyOne word in Microsoft’s first step keeps your rows together, and two documented failures explain every list that sorts wrong.Open this guide →
Removing duplicates in ExcelThree tools and only one deletes, and what Excel actually counts as a duplicate.Open this guide →
One page, three keys
The same Microsoft page prints one key for Windows, another for a Mac and a third for the browser, and the accepted answer on Microsoft’s own forum gives the third.
Freezing rows in ExcelOne rule from Microsoft explains every case, including the two that send people to forums.Open this guide →
Excel showing the formulaThree causes, and how many cells are affected is the first clue: a whole sheet is one switch, a single cell is its format or the way the entry was typed.Open this guide →
Merging cells in ExcelMicrosoft says merging deletes the other cells — and there is a different command for what was meant.Open this guide →
When you need a thousand rows
Dragging across a thousand row numbers means scrolling the whole way. The Name box takes the range in one move, and it is documented on a page neither insert page links to.
Pivot tablesThe four clicks are the easy part — the data shape decides, and the Recommendations button is a data transfer.Open this guide →
XLOOKUP in ExcelThe six arguments, and the four cases where it beats the older lookup.Open this guide →
Comparing two workbooksMicrosoft’s tool is restricted to enterprise Windows — and one helper column does the job anywhere.Open this guide →
What the new rows bring with them
New rows can arrive with formatting already on them, and Microsoft names the button that takes it off.
Conditional formatting with a formulaMicrosoft’s note on absolute and relative references, and the three reasons a correct-looking rule does nothing.Open this guide →
Locking cells in ExcelEvery cell is already locked, so the real job is unlocking the ones people should edit.Open this guide →
Percentages in ExcelThe formulas are the easy half. The format changes the number, and does something different depending on when you apply it.Open this guide →
Questions people also ask
How do I insert 20 rows at once?
Select twenty rows by their numbers — click the first, then Shift-click the twentieth — right-click the selection and choose Insert. Microsoft’s rule is that you get as many new rows as you had selected.
How do I add 100 rows in Excel at once?
Type the rows in the Name box to the left of the formula bar — 2:101 is rows 2 to 101, a hundred of them — press Enter, then right-click the selection and choose Insert. The new rows go in above row 2.
How do I insert 1000 rows in Excel?
The same way, with 2:1001. The sheet has room — Microsoft gives it as “16,384 columns wide by 1,048,576 rows tall” — unless something sits in the last rows, and then Excel says it “cannot shift nonblank cells off the worksheet”.
Is there a keyboard shortcut for inserting rows?
Three, one for each section of Microsoft’s shortcuts page: Ctrl+Shift+Plus on Windows, which opens the Insert dialog, Ctrl+Shift+Equal on a Mac, and Ctrl+Plus in Excel for the web.
Why do the new rows come out bold, or with a border?
Because they inherit. Microsoft: “that formatting will be transferred to a new row or column that you insert”. Use the Insert Options brush that appears beside them and choose Clear Formatting.
How do you add up multiple rows in Excel?
That is a different question with a different answer: it is SUM, not Insert. Select the cell under the column and use AutoSum, or type =SUM() around the range.
Not covered here. It does not cover inserting columns, which is the same four pages with the word changed and one difference worth knowing: the new columns arrive to the left of what you selected.
It does not cover Google Sheets, which has its own menus and its own help pages for this job.
And it does not cover deleting rows, or the blank rows left behind by a filter, which are two different jobs with two different commands. What holds instead is simple: every rule, key and limit here is quoted from the Microsoft page that publishes it, with the section of that page it came from.
Sources
- Microsoft Support — Insert or delete rows and columns: the Windows ribbon route, the Insert Options button and how to bring it back, the formatting a new row inherits, and the size of the sheet. Its Web tab is the only place on the page that says to select the same number of rows. Applies to Excel for Microsoft 365, 2024, 2021, 2019 and 2016 — support.microsoft.com, read September 2026.
- Microsoft Support — Insert one or more rows, columns, or cells in Excel for Mac: the same rule with a worked example, the note that it is fine if the rows contain data, and CONTROL-click in place of right-click. Applies to Excel for Microsoft 365 for Mac, 2024, 2024 for Mac and 2021 for Mac — support.microsoft.com, read September 2026.
- Microsoft Support — Keyboard shortcuts in Excel: the three keys quoted above, one from the Windows section, one from the macOS section and one from the Excel for the web section of the same page — support.microsoft.com, read September 2026.
- Microsoft Support — Select specific cells or ranges in Excel: the Name box, and the instruction to type a cell reference and press Enter. Neither of the two pages about inserting rows links to it — support.microsoft.com, read September 2026.
- Microsoft Support — Select cell contents in Excel: “Drag across the row or column headings”, and the SHIFT-click route for a large range of cells. Applies to Excel for Microsoft 365, 365 for Mac, 2024, 2024 for Mac, 2021, 2021 for Mac, 2019 and 2016 — support.microsoft.com, read September 2026.
- Microsoft Support — Overview of formulas in Excel: the reference 5:10 for “All cells in rows 5 through 10”, the form the Name box takes for whole rows — support.microsoft.com, read September 2026.
- Microsoft Support — Error message: To prevent possible loss of data, Microsoft Excel cannot shift nonblank cells off the worksheet: the message and its two causes, a formula added to the entire worksheet or formatting added to a whole row or column. Written for Excel 2000 and listed under Excel 2010 — support.microsoft.com, read September 2026.
- Microsoft Tech Community — Inserting multiple rows, in the Excel forum: the question asked on 22 May 2019, cited here for the question itself and for the accepted answer, which gives the key from the browser section of the shortcuts page — techcommunity.microsoft.com, read September 2026.
- DataCamp — How to Insert Multiple Rows in Excel: 4 Different Ways, 7 March 2025: the Name box as its fourth way; Domestika’s guide is the only other result that reaches it. DataCamp sells courses, and says so on the page — www.datacamp.com, read September 2026.
- Domestika — Practical Guide: How to Insert Multiple Rows in Excel, 21 May 2024: the Name box with a row reference, “5:155”, as its third method — www.domestika.org, read September 2026.
Written by Alberto Gulotta
Founder and editor of AI Tools Primer, writing from Palermo, Italy. Thirty-five years of taking computers apart, starting with a Commodore 64 — the long version is on the about page.
Something wrong on this page? Write to aitoolsprimer@gmail.com and it gets fixed.
Written on 25 September 2026.
Independence and limits
No affiliate links and no paid placements anywhere on this site. Nobody pays to appear here, and no company has seen this page before you did.
This is general information, not professional advice. Where a page touches money, health, safety or the law, it names its source and the date it was read — and your situation may still differ. See the privacy page and the cookie policy.