Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Specifying the Behavior of the Enter Key.

Specifying the Behavior of the Enter Key

by Allen Wyatt
(last updated October 18, 2018)

18

When you type information into a cell, it is normal to press the Enter key at the end of your entry. Excel allows you to specify exactly what should happen after you press Enter. Basically, you can specify that nothing happen (the cell into which you entered information remains the currently selected cell) or that a different, adjacent cell is automatically selected.

To make your specification, follow these steps:

  1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and click Excel Options. In Excel 2010 or a later version display the File tab of the ribbon and click Options.)
  2. At the left side of the dialog box, click the Advanced option. (See Figure 1.)
  3. Figure 1. The advanced options of the Excel Options dialog box.

  4. The first option in the Editing Options section of the dialog box is entitled After Pressing Enter, Move Selection. Either select or clear the check box, depending on whether you want the selection to move or not when pressing Enter.
  5. If you select the After Pressing Enter Move Selection check box, use the Direction drop-down list to specify the direction of the cell that should be selected.
  6. Click on OK.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8848) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Specifying the Behavior of the Enter Key.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Changing Built-in Word Commands

Want to replace Word's internal commands with your own macros? It's easy to do if you know the key discussed in this tip.

Discover More

Highlighting Information Using Shading

Need to draw attention to some text in your document? You can do it by applying some fast and easy shading to your text.

Discover More

Frequent Workbook Recovery Prompts

When you open a workbook, Excel examines that workbook to make sure it can understand the data it contains. This can lead ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Tab Key Won't Move from Cell to Cell in Locked Worksheet

Normally the Tab key can be used to move from one cell to another in Excel. If this cell movement doesn't work for you, ...

Discover More

Changing Excel's Starting Date

When keeping track of dates, internally, Excel uses special date serial numbers that are based upon a specific day in the ...

Discover More

Fixing the Decimal Point

Don't want to always type the decimal point as you enter information in a worksheet? If you are entering information that ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 + 2?

2017-12-18 11:52:40

FFC

I have the same issue as Ken below (2017-02-14 01:58:39): I do not want the selection to move after Enter, and while the sheet is unprotected it doesnt. As soon as I protect the sheet, however, Enter moves the selection to the next unprotected cell. How do I prevent this?
Can anyone help us?


2017-11-30 11:48:20

Jane

Beedy and Marty.....this may help...

I noticed the "Office 365" app was listed under the "Recently Added" category when I opened the Start menu (using the Start icon/Windows logo key). I don't know how Office 365 was installed on my computer (perhaps during the last MS update?), but I know for certain I would never have purposely installed it.
I uninstalled the Office 365, then searched and uninstalled 3 other apps that I have never installed or used.....MS Solitaire Collection, Candy Crush Soda Saga, and Sway. Voila!! My Excel is working like the good ol' days and my blood pressure is back to normal.

Others have indicated uninstalling programs called TeamViewer and Ditto have helped resolve their issues. I don't have those programs on my computer, so I think the culprit was Office 365 installed without my knowledge or permission.


2017-11-28 14:30:32

Beedy

Same issue as Jane and Marty.

Anybody?


2017-11-22 13:02:17

Jane

I'm having the same issues as Marty Bryant (see his Aug.30,2017 comment)....."In nearly all prior versions of Excel, the ENTER key could be used to auto-paste anything copied or cut and place them in the new selection spot without using Ctrl-V. But for some reason, and this was just in the last couple of months, this option no longer works, and I do not see any option to tweak it back the way it was. It seems the ONLY option now is to use Ctrl-V, which is a pain in the but for someone who has used these short cut keys for a very very long time."

Only my Excel issue just started today, where using the Enter key to auto-paste caused Excel to open a second spreadsheet, then hung for a long time, and caused lots of grief for me! The first time it happened, I thought I did something to cause Excel to open the second s/s, and spent 30 minutes fighting with Excel trying to save my s/s with both security passwords (read and modify)....for some strange reason, it was allowing the read option only. Finally, I was able to save the s/s to its original security settings.

All was going ok for a while, until I hit the Enter key to auto-paste, and again!!!...the bloody thing opened a second s/s and hung!! So I quickly ended the Excel program through Task Manager. It's running ok now, but I can't use the Enter key...unless I risk going through the hassles of losing information and the modify security password protection.

Is anyone else having this issue? Any suggestions how to fix the Enter key, so it can auto-paste like in the past? I've been using the Enter key to auto-paste for as long as Excel has been around...this dog is too old to learn new tricks!!


2017-08-30 14:05:56

Marty Bryant

In nearly all prior versions of Excel, the ENTER key could be used to auto-paste anything copied or cut and place them in the new selection spot without using Ctrl-V. But for some reason, and this was just in the last couple of months, this option no longer works, and I do not see any option to tweak it back the way it was.

It seems the ONLY option now is to use Ctrl-V, which is a pain in the but for someone who has used these short cut keys for a very very long time.


2017-08-02 22:21:32

Chris

@Gus
I just found a tip at http://www.excelarticles.com/LE10ePub-44.html. If you set the enter direction to "right" instead of "down" and highlight a range, Excel will go to the first column of the next row after you complete entering a row of data. This is a workaround but Excel used to do this even when the enter direction was set to "down".


2017-08-02 22:15:51

Chris

@Gus
I'm having the same issue. Excel usually intuits a data entry pattern but I even tried highlighting the range of cells (which used to ALWAYS generate the behavior you describe) and still Excel will only go in one direction. Did you find a solution?


2017-05-04 10:14:27

Gus

When the option for "After pressing Enter, move selection" is set to "Down" the normal behavior is that the selection goes down and to the left most cell of where data was being entered. Kind of like typewriter fashion. For example, if I started entering data in B2 and tabbed over and entered data into C2 and then D2 and hit Enter, I would normally expect the selection to be B3. However, I'm experiencing an issue with the selection only moving down and not over to the left. This is driving me a bit crazy. Any suggestions about what setting this is under?
Thanks.


2017-02-14 10:26:46

Duncan

FYI, in Google Sheets, the behaviour of the Enter key cannot be configured (at present) as far as I know. It always moves the cell pointer down one row.

This is amazingly irritating. Unfortunately my employer is (sort of) shifting to Google apps for collaboration. There is quite a divide between people who think this behaviour is nothing to worry about and sensible people like me who think it's reidiculous.


2017-02-14 01:58:39

Ken

I do not want the selection to move after Enter, and while the sheet is unprotected it doesnt. As soon as I protect the sheet, however, Enter moves the selection to the next unprotected cell. How do I prevent this?


2016-07-28 04:48:08

Jas

Hi,

I prefer the cell to go down when I press enter but this keeps defaulting back to not moving anywhere. Any ideas how I can change this default?

Thanks :)


2015-10-07 06:37:04

YM

Thank you for this.

I use Excel a lot and always hate to have the cursor jump to the bottom cell.

Why does it default that way?If a user had wanted to jump to the box below, they should just press the arrow down key instead.


2015-08-23 09:07:40

Willy Vanhaelen

@John

Your question wonders me because the answer is clearly in this tip. There are no macros involved. Perhaps you were deverted by the comments but these are discussions about advanced features.

Simply follow the steps of the tip and in step 3. uncheck "After Pressing Enter, Move Selection" and you are done.

I too prefer this setting. I want the cellpointer to stay put when I press Enter. If I want the cellpointer to move I can press one of the direction keys istead of pressing enter.


2015-08-21 07:58:57

John

I don't know anything about macros so please bear with me - For a workbook, when I type a number on a cell e.g. A1, I want the cursor to stay at the same cell (A1) after I press the Enter key. How do I do this please?

I am using Microsoft Excel for Mac version 14.5.4 on my Macbook Pro 2008.

Hope you can help me... Thank you very much in advance..


2014-09-04 14:54:17

Don

@Bryan...Instead of attaching the code to the Workbook_Open event, why not use the Workbook_Activate and Workbook_Deactivate event modules. That way you won't be troubled in other workbooks?

Also, assuming there are occasions where there are workbooks where left-to-right is preferred and up-to-down, you can add code at the worksheet level.

For each there is a Public variable to capture the state of the condition coming in and re-applying it going out.

Here is what I've used
In WORKBOOK
Option Explicit
Public PreviousDirectionOnEnter

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
PreviousDirectionOnEnter = Application.MoveAfterReturnDirection
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.MoveAfterReturnDirection = PreviousDirectionOnEnter
End Sub
'************************

In WORKSHEET
Option Explicit
Public PreviousDirectionOnEnter

Private Sub Worksheet_Activate()
PreviousDirectionOnEnter = Application.MoveAfterReturnDirection
Application.MoveAfterReturnDirection = xlToLeft 'or xlToRight or xlToLeft or xlUp
End Sub

Private Sub Worksheet_Deactivate()
Application.MoveAfterReturnDirection = PreviousDirectionOnEnter
End Sub


2014-06-05 08:07:24

Bryan

@Essa: Ummmm it's an individual preference. How stupid would it be if a colleague could attach the preference to a workbook, thus forcing you to use whatever style they prefer instead of the one you prefer? Can you imagine what havoc would be caused if you sent a spreadsheet to someone who didn't know about this option and suddenly the functionality is considerably different that what they are used to?

If for some reason you have a workbook that needs a different setting than the default, use the Workbook_Open event to turn it on and the Workbook_Close event to revert it back. Of course it will affect ALL open workbooks, but you can program around that as well.


2014-06-04 13:15:03

Essa

How stupid that this has to be changed in the Excel software itself, and not for the individual spread sheet!


2013-07-15 06:36:53

Duncan

It has never ceased to amaze me that:
1) people don't explore the Options menu to set up their personal preferences and just stick with what Microsoft deem to be correct; and
2) people put up with the cell selection moving down every time when they press Enter, even if that is only where they want to go in about 5% of real-life cases.


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.