Data entry can be a thankless job and if Excel doesn’t cooperate, it can be a hateful job. Fortunately, you can get Excel to work with you instead of against you, if you know these quick tips for controlling the cursor’s movements.
First, Excel moves the cursor down to the next cell when you press Enter. That’s not always the direction you’ll want to go. If you’re entering data from column to column, it can be downright infuriating. Now, you can press the right arrow key instead of Enter, but doing so will slow you down. Pressing Tab is a bit easier than stretching for the right arrow key, but perhaps the easiest solution is to change the cursor movement as follows:
- Click the File tab and then click Options (under Help). In Excel 2007, click the Office button, and then click Excel Options. In Excel 2003, choose Options from the Tools menu.
- In the left pane, select Advanced. In Excel 2003, click the Edit tab.
- In the Editing Options section, choose Right from the Direction dropdown under the After Pressing Enter, Move Selection option, as shown. You can choose Right, Left, Up, and Down. In Excel 2003, the option is Move Selection After Enter.
- Click OK.
This is a permanent change, but it’s easy to reset, so don’t let that stop you from using it to your advantage.
The second trick I have in mind is temporary. Simply select the data input range and start entering data. Excel will move the cursor from column to column until you reach the right-most column in the selection. At this point, when you press Enter, Excel will move the cursor to the left-most column in the next row.
For instance, in the sheet below, you’d select A29:B33 to enter the next five records. Excel will automatically select A29. After typing the first date, you press Tab to select B29, enter the data and press Tab again. This time, Excel selects A30, not C29. This quick selection tip makes quick work of on-the-fly data input tasks.
My third data input trick is to use Excel’s Table feature (Excel 2003 doesn’t support this feature). Simply convert the input range to a table and Excel will control data input as above – simply press Tab to move from cell to cell, within the table range. To create a table, select the existing data and do the following:
- Click the Insert tab.
- In the Tables group, click Table.
- Specify whether the range has header cells or not, and click OK.
That’s all there is to it – you don’t have to select a thing, just start entering data!
Of course, there are many ways to ease the data input burden and these are just a few. What methods to you employ to make data input a bit easier?
Subscribe to the Developer Insider Newsletter
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays