Microsoft

Quick Excel data entry trick

Use this easy-to-implement trick to force Tab to jump to the first cell in the next row when entering new data.
Pressing the Tab key, by default, moves the cursor to the right. This is useful when entering a new row of values -- at least until you reach the last column in that row. Then, you must grab the mouse and select the first cell in the new row or press Down and Left Arrow a number of times. Regardless of your manual technique, it's awkward at best. Fortunately, there's a simple selection trick that forces Excel to move the cursor to the first row in the next record when you reach the last column in a selection. Before you start entering data, position the cursor in the first cell of the first blank row, as you normally would. Then, highlight the columns and rows necessary to accommodate the new data. For instance, let's suppose the current data is in A1:E10 and you need to insert three new records. In this case, you'd select A11:E13.

Once you've selected the necessary cells, enter the first value -- that would be cell A11. Then, press Tab. Enter the appropriate value for Cell B11 and press Tab. Continue in this fashion until you reach cell E11. Enter the value for E11 and press Tab. Instead of selecting F11, the cell to the right, Excel cycles around to the next cell in the selection, which is A12. That's right where you need to be to start the next new record!

This selection method cycles through only the selected cells, so you must select the appropriate number of records, or more records than you need. If you press Tab while in the last cell in the selection (cell E13 in the above example), Excel will cycle around to the first cell (A11). Consequently, if you're not careful, you could overwrite existing data. The trick, when you don't know exactly how many rows to select, is to select more rows than you need.

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.

12 comments
ivijayan
ivijayan

Good lil trick for time saving

aikimark
aikimark

select the starting cell click Data | Form allow the top row contents to be used as the field names do your data entry in the form, tabbing between fields and then pressing Enter for a new record (the default action) Note: this is very similar (keystroke wise) to the earlier comment. ============= Missing trick: freeze the first row, so that the column headers are always visible during the data entry activity.

dustyjan95
dustyjan95

It's a little harder, but another option is to set the scroll area - press Alt F-11 to open the Visual Basic window, highlight the worksheet in question, and press F4. Then in the Scroll Area field, enter the cells you want to be able to edit - A11:E13 in the above example. You can then use Enter, or Tab to move between cells. This is overkill for simple situations, but very useful if you have a lot of data.

efs710920mex
efs710920mex

Another way is without selecting anything, start in A11, enter data and move to the right with tab, keep entering data until you reach the end of the row (E11 in the example), hit enter and the active cell will move to the first cell in the next row (A12). All you have to remember is to move with tab always. If you use the right arrow key it won't work.

clifforde
clifforde

Susan, I have always used Data-Tab, Data-Tab, Data-Tab, Data-Tab then ENTER. This will bring the cursor to first cell in new row. This presumes that the cursor position is set to Down rather than Right in Settings.

ssharkins
ssharkins

Thank you both for mentioning this method -- I should have, but I don't use this method myself. The two are similar, but not quite the same. For me, remembering to switch from pressing Tab to pressing Enter, at the end of the record just doesn't work -- I just can't make the switch. If you're entering enough records, I think most people would catch on quickly enough though, so it's definitely worth knowing.

jdoverton+techrep
jdoverton+techrep

Also, if you actually have a data table with a header row, you can just place your cursor in the first empty row and use the Data|Form... option to simply enter more records using a simple form.

tufte
tufte

You can do the same thing with columns if you are set up so that tabs take you to the right and enter takes you down. Just highlight the multi-column array you want to fill, type the first entry, tap enter, and repeat. Excel will fill the array going down the first column, then down the second column, and so on.

MarciaAnn
MarciaAnn

I've been doing data entry for over 27 years. In each version of Excel that I use, I've always been able to tab tab tab enter and I am taken down to the row beneath from where I started tabbing.

Nebraska Jack
Nebraska Jack

thsi is available in 2007 also, but you have to place the Form icon on the Quick Access Toolbar from the All Commands selection list.

stapleb
stapleb

If you find you have made a mistake, use Shift + Enter to move back up to the error and either type over it, or use F2 to edit, then Enter to continue. This way you will not lose your highlight.

Editor's Picks