Excel tip: Fast ways to insert rows and columns
In his latest tip, regular AccountingWEB.com contributor David Ringstrom shows how to speed up a common spreadsheet move.
How often do you find yourself inserting rows and columns into your spreadsheets? It’s a common activity for most users and usually involves several steps.
Even people who know their way around Excel’s keyboard shortcuts have to activate two different shortcuts in sequence. This article explains how to create your own custom shortcut to insert rows or columns with a single keyboard shortcut or mouse click.
There are several ways to add rows or columns to a spreadsheet:
- Excel 2007 and later: As shown here, select Insert on the Home tab and then Insert Sheet Rows or Insert Sheet Columns:
- Excel 2003 and earlier: Choose Insert and then Sheet Rows or Sheet Columns
- In any version of Excel, you can also insert rows and columns by way of the worksheet frame. As shown below select one or more rows or columns, right-click on the selection, then choose Insert.
- Some users rely on a keystroke approach to insert rows:
1. Press Shift-space bar to select a single row. You can optionally then hold down the Shift key and use the arrow keys to select additional rows.
2. Press Ctrl -+ to insert rows. In this context, make sure you're using the + sign on your number pad; otherwise, you'll want to press Ctrl-Shift-+ if you're accessing the plus sign that is somewhat adjacent to the letter P on your keyboard.
This approach also works with columns selected with the Ctrl-space bar keyboard shortcut. Ctrl+ also works for rows or columns that you select with your mouse.
- You can also press Ctrl-+ or Ctrl-Shift-+ without preselecting any rows or columns. When you do so, Excel will display the prompt shown in Figure 3. Press R to select Rows or C to select Columns, and then press Enter.
Another commonly used technique involves using Excel's macro recorder to record a macro that you place in your personal macro workbook and then assign a keyboard shortcut or icon. While this can be effective in Excel 2003 and earlier, actions carried out by way of a macro can't be undone; plus, users sometimes assign keyboard shortcuts to macros that supersede built-in shortcuts. For instance, one might think that Ctrl-I would be a great keyboard shortcut for inserting rows, but that means other users of that spreadsheet could no longer press Ctrl-I to italicise text.
Regardless, it's easy to streamline this task in Excel 2007 and later. As shown here, select Insert on the Home tab, right-click on Insert Sheet Rows, and then choose Add to Quick Access Toolbar:
If you haven't previously modified your Quick Access Toolbar, Insert Sheet Rows will become the fourth icon on the toolbar, which means its keyboard shortcut will be Alt-4. Going forward, you can now press Alt-4 at any time to insert a new row into your spreadsheet. If you need to insert multiple rows, you have several options:
- Press Alt-4 as many times as needed.
- Press Alt-4 once to insert the initial row, and then press either F4 or Ctrl-Y to repeat this action.
- Hold down the Shift key and then use the Down arrow key to select multiple cells. Press Alt-4 to insert multiple rows at once.
- Select one or more rows by way of the worksheet frame with your mouse, and then press Alt-4, or click the Insert Sheet Rows icon on the Quick Access Toolbar with your mouse.
As you've probably surmised, you can add the Insert Sheet Columns command to your Quick Access Toolbar as well to simplify adding columns.
As always in Excel, there are some nuances that you should be aware of. Make sure to use the 4 key at the top of your keyboard. If you press Alt and the 4 on your number pad, you'll insert a diamond symbol into your worksheet. Or if your fingers slip and you press Alt-F4, this is a Windows shortcut for closing the active program.
You can reassign the numeric shortcut for Quick Access icons by changing the order of the commands. To do this, click the arrow at the right of the Quick Access Toolbar and then choose More Commands. Pick a command from the list on the right, and then use the arrow keys to reposition the command on your Quick Access Toolbar. For instance, if you made Insert Rows be the first icon on your toolbar, its keyboard shortcut would become Alt-1.
To remove an icon from your Quick Access Toolbar, right-click on it and choose Remove from Quick Access Toolbar, as shown here:
"Either you work Excel, or it works you!" says David Ringstrom CPA, the head of Atlanta-based software and database consultancy Accounting Advisors. He presents Excel training webcasts for CPE Link and contributes articles on Excel to AccountingWEB and Microsoft Professional Accountant's Network newsletter. He can be reached by email at david[AT]acctadv.com. More Excel tips from David H Ringstrom available here.