Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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 January 19, 2016)

11

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 and Excel 2013 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, and 2013. 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

Replacing Quoted Text with Italics

If you have text surrounded by quotes in a document, you may want to remove the quote marks and make the text that was within ...

Discover More

Moving Drawing Objects

Add a drawing object to a worksheet and chances are good you'll need to move it in some way. Here's how to use the mouse to ...

Discover More

Using the INT Worksheet Function

The INT function allows you to convert a value to an integer. The effect the function has depends on the characteristics of ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Turning Off Paste Options

Paste some information into a worksheet and Excel helpfully displays some options related t the paste operation. If you don't ...

Discover More

Changing Error Checking Rules

Excel can check the data and formulas in your worksheet to see if it detects any errors. The rules used for this checking can ...

Discover More

Controlling Display of Page Breaks

Do you want page breaks displayed on the screen? Excel allows you to specify whether it should show those page breaks or not.

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. 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 eight minus 8?

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.