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
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.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Today's PCs are more powerful than ever, but you can still have slowdowns when it comes to calculating large workbooks. ...
Discover MoreExcel stores dates and times internally using what is called a serial number. This tip explains how that serial number is ...
Discover MoreWhen working with multiple workbooks, you'll typically want to resize the workbook windows so you can see the data from ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments