Keyboard Shortcuts for Inserting Rows

Written by Allen Wyatt (last updated December 9, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


6

When Tim is working on a worksheet, he often needs to insert rows above whatever cell he has selected. He wonders if there is a keyboard shortcut to insert a new row above and, perhaps, a related shortcut to insert a row below.

Using the keyboard to insert rows above the current row is easy, and there are a couple of ways to do it. Longtime users of Excel will know that you can press Alt+I and then press the R key. This inserts a row directly above whatever cell you have currently selected. I mention longtime users of Excel because this key sequence was used way back, before the ribbon interface was introduced in Excel 2007. The sequence is maintained by Microsoft even in the latest versions of the program for compatibility reasons.

A newer approach is to first select the current row of cells (you can press Shift+Spacebar to do that) and then press Ctrl++ (that's the Ctrl key plus the + key). You can use the plus key on the numeric keypad, or you can include Shift in the sequence to get to the plus key on the regular keyboard. Either way, a full row is inserted above the row you selected to begin with.

Note that these shortcuts work a bit differently if you are working within a defined table. In that case, you don't need to select the current row first—pressing Ctrl++ will add a row above the current row in the table. If you want to affect a both the table and the cells outside the table, then you'll need to press Shift+Spacebar twice (first to select the row in the table and second to select the worksheet row outside the table) before using the insert row shortcut.

Inserting a row below the current one is a different story—Excel doesn't provide a shortcut for that. This means you have two possible workarounds. First, you can simply move down a row and then use one of the techniques already described. Second, you could create a macro to insert a row below:

Sub InsertRowBelow()
    Cells(ActiveCell.Row + 1, 1).EntireRow.Insert
End Sub

Note that the macro uses the .Insert method to add the entire row, but it is applied to the row below where the active cell is located. Thus, this single-line macro effectively does exactly what the first workaround does—moves down a row and inserts above.

If you create such a macro, you can assign it to a shortcut key so you then have your own shortcut for inserting a row below the current one.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8072) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Character Limits for Cells

Excel places limits on how much information you can enter into a cell and how much of that information it will display. ...

Discover More

Splitting Sentences to Cells

If you have a lot of text in your workbook, at some point you might want to split out sentences into individual cells. ...

Discover More

Limiting What is Shown in the Open Dialog Box

When you display the Open dialog box, Word has to determine what files to display. This tip looks at a baffling ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Generating Random Testing Data

Need to test your formulas? Then you need some testing data that you can use to see if the formulas function as you ...

Discover More

Can't Access the Registry

Many Windows applications rely on information stored in the Registry. If that information cannot be accessed, the ...

Discover More

Grabbing a User's Name from Excel

One of the settings you can make in Excel is to specify a user's name. This name is accessible through macros, and can ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 + 4?

2023-12-12 03:05:28

Michael van der Riet

Now that Lotus and Borland are no longer existential threats to Microsoft you'd think that they'd re-enable the old / keyboard shortcuts.

While we're on that, is it possible in Excel to copy a formula à la Lotus that contains absolute addresses while deciding which addresses stayed absolute?


2023-12-11 13:28:09

Frank Moore

I just noticed that when I do that in a table with formulas, the formulas don't seem to adjust for the new inserted row. I have a table that I created to track the amount of snowfall at our house. The formula for the row above the inserted row is =C21+E20. C is the row where I enter the amount of snow fall for the day and E is the column for the total accumulated snow fall. The formula for E for the inserted row is correct =C22+E21 but the formula for the row below the inserted row is =C23+E21 instead of =C23+E22 and all the formulas in the cells for Total Snowfall (E) below the inserted rows are off by 1


2023-12-11 01:00:09

Peter

Sorry, that was not a keyboard shortcut, but interesting anyway. Peter


2023-12-11 00:58:20

Peter

I recently discovered that within a table, right click, insert, insert row below. ...Or something like that. (Using Excel 365)


2023-12-10 02:45:34

Frank Moore

I have wondered about this for several years. What a great tip! Thank you


2023-12-09 12:24:14

Jay Jacob Wind

Inserting a row below is trivial and requires no macro.
Just press <ENTER> to go down one row, and then you can use <ALT>+I+R

Jay Jacob Wind


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.