By David Ringstrom, CPA
Spreadsheet users frequently insert rows and columns into spreadsheets, but for most users, it's a multiple-step process. Even savvy users who rely on keyboard shortcuts have to use two different shortcuts in sequence. In this article, I'll explain how you can 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 in Figure 1, on the Home tab, choose Insert and then Insert Sheet Rows or Insert Sheet Columns.
Figure 1: Many users rely on the Insert Sheet Rows command to insert new rows in a spreadsheet.
- 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 in Figure 2, select one or more rows or columns, right-click on the selection, then choose Insert.
Figure 2: You can right-click on the worksheet frame and choose Insert to add new rows or columns.
- 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.
You can use this same approach with columns that you select by way of 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.
Figure 3: Press Ctrl-+ on your number pad or Ctrl-Shift-+ on your keyboard within a worksheet cell to display the Insert dialog box.
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 italicize text.
Regardless, it's easy to streamline this task in Excel 2007 and later:
- As shown in Figure 4, choose Insert on the Home tab, right-click on Insert Sheet Rows, and then choose Add to Quick Access Toolbar.
Figure 4: Add Insert Sheet Rows to your Quick Access Toolbar to streamline this repetitive task.
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 that's along 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.
- If your fingers slip and you press Alt-F4, you'll likely encounter the prompt shown in Figure 5. Alt-F4 is a Windows keyboard shortcut for closing the active program.
Figure 5: If you press Alt-F4 instead of Alt-4, you'll be instructing Excel to close.
- You can reassign the numeric shortcut for Quick Access icons by changing the order of the commands. To do so, as shown in Figure 6, click the arrow at the right of the Quick Access Toolbar and then choose More Commands. Select 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.
Figure 6: Reassign keyboard shortcut numbers by reorganizing your Quick Access Toolbar.
- To remove an icon from your Quick Access Toolbar, right-click on it and choose Remove from Quick Access Toolbar.
A previous version of this article first appeared on www.accountingweb.com .
About the author:
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link
