Software

Three ways to control Excel's cursor movement during data entry

Inputting data is seldom anybody's favorite chore but controlling the way the cursor moves certainly eases the task.

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:

  1. 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.
  2. In the left pane, select Advanced. In Excel 2003, click the Edit tab.
  3. 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.
  4. 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:

  1. Click the Insert tab.
  2. In the Tables group, click Table.
  3. 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?

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

9 comments
Winniewu
Winniewu

Hi, Dear, I am using excel 2003 and I need to change cursor movement direction frequently, I chose tools, option, edit, right or down, 4 clicks for every change, it's so inconvenience, it is a easy way to do this, or can I put "cursor movement dircetion" in the toolbars? Winnie

smadahugh
smadahugh

For the ???Three Ways to Control Excel Cursor Movement??? I can add a fourth way to move the Excel cursor that has proven to be useful in certain circumstances. If you are entering multiple lines of information across multiple columns ??? enter the information in the left-most column then use the key to advance to the next column on the right. ???This is important??? ??? As long as you continue to use the key while going to the right, when you???re done entering data for that particular line or row, you can press the Key for the last entry in the row and the cursor will step down to the next row and all the way to the left ??? back to the first column ??? allowing you to begin the next line of information using a minimum of keystrokes. This might take a little getting used to???. But once you do it, you???ll find it very useful and it offers an economic keystroke alternative for entering data across a spreadsheet.

zimmerwoman
zimmerwoman

If you change the cursor movement to "right" (1st method) and then select the range of rows and columns to update (2nd method), the cursor will move right upon each press of the "Enter" key, until it gets to the far right and then it will move down one row to the left-most cell on that row in the range. I type by touch and so avoid anything that requires taking the hands off the home keys unnecessarily. And that includes picking up the mouse unnecessarily, so I have most of the old hotkeys hardwired and starting to learn the new ones.

elastoman54
elastoman54

You can also highlihght the group of cells then hold down the control key and hight other cells in different areas as long as those are highlighted you can enter the data and press enter the cursor will proceed to the next hightlighted cell. for example if you highlight cells a1 thru e1 then hold down the control key and hightlight cells a2 thru e2 and proceed to hightlight say a 10 thru e10 then start entering data the cursor will start at a1 and continue to e1 then a2 to e2 then to a10 to e10 if you press the enter key after enteirng your data. This can be done with none continus cells both rows and columns. this a temporary to enter data into a group of cells without changin how the cursor moves as shown above.

Rudi-S
Rudi-S

Hi Susan. I often fill out records in a large table and a very useful procedure I use is to use the CTRL+D shortcut key to duplicate a record, and then just make minor changes for the new record. 1. To start I select the entire record which I plan to duplicate. 2. Then using CTRL, I select the empty cells at the end of the table where the new record must be duplicated. (This can be a single row, or multiple rows. The amount of rows selected is the amount of duplicates created) 3. With the original and the empty cells selected, just press CTRL+D to duplicate - and then make the minor changes for a new record(s). Cheers PS: TX for all your great articles...keep them coming.

ssharkins
ssharkins

Thanks for extending this technique -- I'm sure many readers will benefit from the additional information!

ssharkins
ssharkins

Thanks for sharing your technique and I'm glad you like the tips -- I hope you find them useful.

ssharkins
ssharkins

Thanks and I hope you can put them to good use!