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)

14

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

Simple Relative References in External Links

Linked data can be essential in pulling information from one workbook to another. One downside of links, however, is that ...

Discover More

Technique for Adding a Text Box to an Envelope

Text boxes can be a great design feature to use in laying out a document. You may want to add one to an envelope, however. ...

Discover More

Moving Drawing Objects

When you need to move a drawing object around your document, you use the mouse after you select the object. This tip shows ...

Discover More

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!

More ExcelTips (ribbon)

Slash Key No Longer Works as Expected

Press the slash key and Excel may display a series of keyboard commands near the ribbon. If this behavior drives you nuts, ...

Discover More

Setting the AutoRecover Directory

Excel, by default, periodically writes information to AutoRecover files that can help protect your data in case Excel is ...

Discover More

Turning Off ScreenTips

ScreenTips are one of those artifacts of Microsoft trying to make Excel be overly helpful. If the ScreenTips bother you, you ...

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 + 8?

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.