Alt-Down Arrow is one of my favorite keyboard shortcuts in Excel. First off, it gives me an easy means to speed through repetitive data entry within a column. Second, it allows me to access Excel's Filter arrows as well as Data Validation lists without using my mouse. I'll run through all three techniques in this article.
First, let's start off with the data entry aspect of Alt-Down Arrow. As shown in Figure 1, I've entered Red, White, and Blue in cells A1, A2, and A3, respectively. If I place my cursor in cell A4 and then press Alt-Down Arrow, Excel reveals an alphabetized list of the entries that I just made. I can use the down arrow key to select an item from the list and then press Enter to place that value in cell A4.
Figure 1: Use the Alt-Down keyboard shortcut to easily view and make selections from a list of your entries.
This feature works anywhere in Excel where you've created a list of items, but there are a couple of caveats:
- Your cursor must be either within or adjacent to a block of cells that contain words. As shown in Figure 2, the list will either not appear or will be blank if you try to use it with a range of numbers.
- The list will also not appear or will be blank if your cursor is not within a cell that is immediately adjacent to or within the block of cells that contain the text you wish to choose from.
- The drop-down list only works within a given column, so you won't be able to use it for data entry that goes across a row.
Figure 2: The list will appear blank if your cursor is not properly positioned adjacent to a columnar block of text.
You can also use the Alt-Down Arrow keyboard shortcut in conjunction with Excel's Filter feature. For benefit of the uninitiated, filtering in Excel allows you to hide or display rows based on criteria that you provide. Filtering is typically used with a list, such as shown in Figure 3. To enable the Filter feature, click any cell within the list and then:
- Excel 2007 and later: On the Home tab choose Sort & Filter, and then choose Filter.
- Excel 2003 and earlier: Choose Data, Filter, and then AutoFilter.
In any version of Excel you can also press Ctrl-L to add the filtering arrows to a list. At this point most users use their mouse to click on the filtering arrow within a given column. If we wished to display only sales of apples and oranges:
- Excel 2007 and later: Click the arrow in cell C3, clear the checkbox for Select All, and then select Apples and then Oranges, and click OK.
- Excel 2003 and earlier: Click the arrow in cell C3, choose Custom, and then set both lists to Equals and then choose Apples and Oranges, respectively.
Note that the newest versions of Excel allow you to pick and choose as many items as you wish, while Excel 2003 and earlier limit you to one or two choices at a time.
Figure 3: The Filter feature allows you to hide or display rows based on criteria that you specify.
With that background in mind, you can use the Alt-Down keyboard shortcut to display the filtering menu for a given column, instead of having to click it with your mouse. Within the list itself, use the arrow keys to navigate up and down the menu, and use the space bar to toggle checkboxes on or off. Press the Enter key to click OK.
You can also use Alt-Down Arrow to select an option from a Data Validation list. I've written about Data Validation in the past, so here I'll just say that instead of using your mouse to click on a Data Validation arrow, you can simply press Alt-Down instead. As with filtering, use your arrow keys to make a selection and then press Enter.
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 [email protected] or follow him on Twitter. David speaks at conferences about Microsoft Excel, teaches webcasts for CPE Link, and writes freelance articles on Excel for AccountingWEB, Going Concern, et.al.