Written by Allen Wyatt (last updated June 5, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
When you enter information in a cell, and then press the Enter key, Excel normally moves to the cell below the one in which you entered the information. You can configure Excel to move in a different direction after pressing Enter by following these steps:
Figure 1. The Advanced options in the Excel Options dialog box.
There is one interesting thing about how Excel selects a new cell: If you press Shift+Enter (instead of Enter) when entering data, then Excel selects the cell in the opposite direction of what you have specified in step 4. Thus, if the Direction drop-down list is set to Down, and you press Shift+Enter, then Excel actually moves the selection upwards.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6712) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Cell Movement After Enter.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Excel allows you to make a wide range of customizations to both the Quick Access Toolbar and the ribbon. If you want to ...
Discover MoreYou can use some of the tools in Excel to convert cell references in formulas into names. Converting back (from names ...
Discover MoreWhat are you to do if you suspect that some of your worksheet functions aren't recalculating automatically? Here's some ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-01-31 04:57:32
David Smith
FAO Angelo
On the Advanced tab, in the Editing options group, choose the value for the After pressing Enter, move selection list:
Text above taken from
https://www.officetooltips.com/excel/tips/changing_moving_direction_of_the_cell_pointer_after_entering_data.html
2024-01-30 23:43:13
Angelo Mascaro
How to achieve the same result in Excel in Microsoft 365, online version?
2023-04-24 12:24:07
That is a good tip. In addition to having a parameter that moves to the left or right or down, etc, wouldn't it be nice to take it one step further and have a parameter that not only allows the direction to go one cell down, but setting a number of spaces for the cell to jump to going down, ie, 10 cells or 12 cells or 14 cells etc. I created a simple water meter worksheet with a date column consisting of each respective month and year with each address entry containing these 12 columns Jan to Dec so the idea would be to just hit the enter key and it would drop to the next month below it which could be 10 or 12 or 14 cells below it. Tried to capture a picture and send, but not savvy enough to use the [{fig]} option as shown in the instructions how to do this. Too bad I cannot attach a screen video MP4 file..., compliments of Microsoft which is an excellent screen recorder app...
2022-03-05 11:34:19
J. Woolley
@lefthanded
See https://contexturesblog.com/archives/2022/02/24/excel-data-entry-tips-jump-to-next-cell-or-previous-cell/
Also, My Excel Toolbox includes the MoveAfterEnter macro to set adjacent cell movement after the Enter key is pressed.
See https://sites.google.com/view/MyExcelToolbox/
2022-03-04 20:57:03
lefthanded
In my job, I am used to dealing with financial software that's more intuitive. When I enter an amount in the credit or debit column and hit the TAB key, it automatically takes me to the beginning of the next line so that the data entry can continue quickly and seamlessly. Is there a way to do this in Excel?
2022-01-19 10:35:09
J. Woolley
@Maxine
Re. step 3: Make sure the After Pressing Enter Move Selection check box is selected AND HAS A CHECK MARK.
Otherwise: Press Alt+F11 to open Visual Basic Editor. Under your workbook's VBAProject, select Sheet..., ThisWorkbook, or Module... and pick View > Code (F7). Look for something like Application.MoveAfterReturn = False and make it True. See https://docs.microsoft.com/en-us/office/vba/api/excel.application.moveafterreturn
2022-01-18 08:54:47
Maxine
Hi there, I have completed the above but when I hit Enter it is still staying in the same cell (I am using a macro enabled workbook)?
Many thanks
Maxine
2021-06-02 17:53:49
Chrissie
If you want the cursor to return to the first cell of next row (or any row) after you hit enter place cursor in last cell you are using and hit f5. Enter the cell you want to return to. Now go to the end of that row and you should return down one row and to the beginning or whatever column you selected.
2020-06-25 05:29:50
David Smith
FAO Zever
how would i use make a spreadsheet where i want to enter data only in rows a,b and c then go back to A. for example i want to function like this .
enter data in A2 then go to B2 then to C2 after C2 i want it to go to A3
Hi Zever
The solution to moving sideways, then down a row is to use the TAB key after entering your data in the cells. When you reach the final cell in your row press ENTER to enter the data and your cursor should go back to cell A3
This works for me in Excel 97 but I had to go into OPTIONs/Edit and set "Move selection after Enter" to "Down".
Hope this helps?
Dave
Liverpool
England
2020-06-24 09:02:40
Zever
how would i use make a spreadsheet where i want to enter data only in rows a,b and c then go back to A. for example i want to function like this .
enter data in A2 then go to B2 then to C2 after C2 i want it to go to A3
2020-06-24 08:54:32
Zever
how would i use make a spreadsheet where i want to enter data only in rows a,b and c then go back to A. for example i want to function like this .
enter data in A2 then go to B2 then to C2 after C2 i want it to go to A3
2020-06-11 09:45:57
JMJ
Here is the way I use to fill a table, hitting as few keys as possible:
-1) Have the Enter key moving to the next cell down, as indicated in this tip.
-2) In the first cell of the first row of your table, enter the value you want, then hit TAB: this moves the cursor to the next cell to right
- 3) in this cell, enter the corresponding value, then hit TAB, and so on for each cell in the row
-4) when you've reached the last cell of the row, hit ENTER instead of TAB: this moves the cursor one line down and just below the first cell where you began to type!
So, you can type without ever leaving your keyboard nor using arrow keys.
I hope this helps...
2019-05-06 11:37:17
Martin
I have found in Excel 365, (whatever version that is), that if the spreadsheet is protected then the return to the starting column when pressing enter does not work. Pressing enter goes in whatever direction is specified as per Figure 1 of this tip.
As soon as the sheet is unprotected, pressing enter will return to the starting column and next row after having used Tab to move through the cells in the previous row.
This makes filling in a table of values much nicer than using the mouse or arrow keys to do the same thing.
Another option to make an entry in a cell and have the cursor remain in that cell is to click the check mark in the block beside the cell reference indicator block at the top left of the spreadsheet. Means moving from the keyboard to the mouse though.
2019-05-01 09:12:20
Dave Smith
Hi Trevor,
The key combination of Control/ENTER keeps the cursor on the cell you have just edited.
Hope this helps.
Dave
2019-05-01 05:49:03
Dave Smith
Hi Trevor,
The key combination of Control/ENTER keeps the cursor on the cell you have just edited.
Hope this helps.
Dave
2019-04-30 22:54:34
Trevor
Is it possible to hit enter once, have it stay in the same cell, then move down after hitting enter again?
2018-04-03 18:16:03
Pranav
Hi Allen, Could you help me, I have a tracker. I would like to know how to disable the row after I select the last column. I need that pointer to go directly to the row below.
Ex:- tracker from Column 1,2,3
Row A,B,
So when I enter a value in A3, press enter or tab, Pointer should go directly to B1. main point is A1to A3 should be disabled. No editing should be possible.
2018-01-04 20:02:13
CL
i'm using excel 2010. Is there a tip/way when i press enter the cell jump to within 'Allow User to Edit Ranges' cell?
2017-09-05 12:20:50
Daniel Fits
I'm using excel 2013 and i have about 4 sheets linking up to eventually and automatically prepare an invoice for farmers. Recently when i try making some inputs, and after entering, the cursor moves to a particular cell in a different workbook. Whatever entry I make, after entring, moves to that cell. This is making it difficult to work in the entire workbook. Please how do I solve this. Thank you
2017-03-10 15:33:44
Peter Atherton
Greg Stough
Ctrl + Enter holds the cursor.
2017-03-09 09:52:53
B. Diddy
Thank you for this tip! Saved me a ton of frustration!!!!!
2017-01-23 18:32:54
Jan Barcz
I figured it out. Somehow the scroll lock got pushed.
Thanks
2017-01-23 18:02:45
Jan Barcz
If I put my cursor in cell B1 and push the down arrow, the whole page shifts up. This didn't use to do this. Not sure what I did to cause this?
2017-01-04 07:12:03
Graham
In reply to Greg, the solution could be to create 2 macros and link them to command buttons. The code needed is :-
Application.MoveAfterReturn = True
or change to = False
Alternatively a single macro could be set up to toggle between the 2 options.
To control the direction of cursor movement add code such as :-
Application.MoveAfterReturnDirection = xlToRight
replace with xlToLeft, xlDown or xlUp if desired.
2017-01-03 10:55:53
Greg Stough
This has been an ongoing issue for me so perhaps you can help. When I uncheck the "After Pressing Enter, Move Selection" checkbox, as you mentioned, this affects all my workbooks. I'd like to have the cursor to remain in the same cell in some workbooks, but not in others. Is there a way to do this? Ideally I'd like it at the sheet level, but if I can get it to work on the workbook level that would be okay too.
2016-12-15 11:35:42
CJ
James - in protected worksheets, I have more luck using the tab key rather than enter. This moves the cursor to the next unlocked cell.
Hope this helps!
2016-10-25 09:06:07
dan
AC: press tab then enter to return
2016-10-22 11:31:16
David Siu
Awesome, it works great!
2016-09-27 09:14:45
AC
I am using Excel 2016. In previous versions of Excel you could enter data across multiple columns and when you pressed enter it would move the cursor down one and all the way to the first column you started with. How can I do this in Excel 2016?
2016-08-13 03:41:36
James Bowen
Jim (or anyone else who knows),
I used your suggestion and deselected the movement after enter. Initially, it had the desired effect (cursor remained in the selected cell after enter). However, I then went in and used the protect worksheet option (allowing the cursor to only snap to unlocked cells). After protecting the worksheet, entering data into a cell, and hitting enter I've found that the cursor jumps to a cell approx 400 rows below the cell I'm working in. At that point, reselecting or deselecting the movement after enter has no effect. Can you offer any suggestions on why this works on an unprotected worksheet and not a protected one?
Thanks So Much
2016-08-07 23:44:13
JIM MINER
As a teacher of Excel, I usually suggest students deselect the movement after enter. This allows the cursor to stay in the current cell where a host of other commands can be implemented.
Use the up arrow, down arrow, right arrow or left arrow to move the cursor. It works flawlessly. Try it, you'll like it!
2016-08-07 19:04:32
Alec Whatmough
I often need (want) to flick between moving down and moving across on Enter, so I have added two small macros to my personal workbook, and put buttons on the QAT.
Macros are:
Sub MoveRightOnEnter()
Application.MoveAfterReturnDirection = xlToRight
End Sub
Sub MoveDownOnEnter()
Application.MoveAfterReturnDirection = xlDown
End Sub
If you want the option to change by worksheet, you could add it to the sheet code as worksheet.activate and deactivate code (you could also do it as workbook.open/close, but that is only any good if you open the file, do your thing and close it again.)
2016-08-06 09:57:23
Graham
I originally entered this comment for a similar article for VBA on individual workbooks. Probably more appropriate here.
I turn off the 'After pressing Enter' option. This has the effect of no movement.
However the 4 'Arrow' keys can be used for BOTH data entry (instead of the Enter key) and movement to another cell.
Because I can never be certain in which direction I want the 'selection' to move, I prefer to use these 4 'Arrow' keys to move up, down, left or right as desired.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments