Saving Movement on Enter with a Workbook

by Allen Wyatt
(last updated May 4, 2019)


Kathy uses several Excel workbooks daily. In some, she wants the cursor to move down after entering the data and in others she wants the cursor to move right. She wonders if there is a way to save the cursor direction with each individual workbook.

The direction in which the selected cell moves after you press Enter is determined on an application level by following these steps:

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

  4. Under Editing Options, make sure that the checkbox for "After pressing Enter, move selection" is checked (it should be by default).
  5. Using the Direction drop-down list, change the direction as desired. Changing the direction affects how Excel behaves in all workbooks.
  6. Click on OK.

Since it is handled at an application level, the setting is not stored with individual workbooks. You can, however, create a macro that can be stored with the workbook, and the macro can modify the direction. Ideally, the macro would modify the direction when a workbook was activated and restore the direction when the workbook was deactivated—meaning, when a different workbook was activated, or the program exited.

Public MouseDirection As Long

Private Sub Workbook_Activate()
    MouseDirection = Application.MoveAfterReturnDirection
    Application.MoveAfterReturnDirection = xlToRight
End Sub
Private Sub Workbook_Deactivate()
    Application.MoveAfterReturnDirection = MouseDirection
End Sub

These are actually two macros and they should be added to the ThisWorkbook object in the VBA Editor. As shown, theWorkbook_Activate macro sets the direction to be towards the right after Enter is pressed. If you prefer, you could replace xlToRight with any of these: xlDown, xlUp, or xlToLeft.


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13073) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.

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. ...


Discovering the RGB Value of a Custom Text Color

Word allows you to easily change the color of the text in your document. If you get a document from someone else, you may ...

Discover More

Better Use of Heading Space

If your column headings are too large to work well in your worksheet, why not turn them a bit? Here's how.

Discover More

Nesting IF Worksheet Functions

The IF worksheet function is very handy to make conditional evaluations. You are not limited to a single IF comparison, ...

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)

Determining Your Version of Excel

Want to find out exactly what version of Excel you are using? Here's how to get to the info.

Discover More

Losing Data in a Shared Workbook

When you create a shared workbook, you run the risk of losing some of the data in that workbook. Here's a discussion ...

Discover More

Making Pane Settings Persist

When you freeze panes in a worksheet, those panes should persist even though you save the workbook and reload it. There ...

Discover More

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

View most recent newsletter.


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 five more than 4?

2019-03-26 06:04:42


Hi Allen, maybe off topic but I have seen that many people have this problem and found no solution whatsoever online.
I am working on a workbook that suddently makes me hit enter twice in order to move from the cell. It's not happening in other workbooks and it didn't happen before on this one.
Do you have any idea what can be the cause and the solution?

Manu thanks

2016-05-18 22:05:14


For everyone screaming about a simpler way to move cells, you are missing the point. While you are so very correct concerning hot keys, those hot keys do not exist on a 10key! This question comes from someone who has to enter lots of numbers and has to move across columns to complete a job, but we do not want right cell movement to be the default for every workbook for ever and ever!

2015-08-06 12:29:03


You are perhaps missing the purpose of this macro - it actually works very well with some automated devices (e.g. thickness measuring equipment) where the only 'button' on the device acts as a virtual enter key. This macro allows you to transform and input data in a sideways fashion from these kinds of devices instead of always down. Sometimes it's not as simple as "Just use the arrow keys".


2015-02-21 09:37:34


I have following problem.
If I am editing the cell end then press enter, the editing process finished but selected cell stay the same. Just after second press of enter the selected cell moves in defined directions. So I have to press enter twice. Can anybody help?

2014-04-22 02:37:05

Rudra Sharma

Hi Guys,
In order to move right simply press tab and to move left press Shift + Tab.Or if you are not in editing mode you can simply use arrow keys as discussed in some of the comments.
With Regards

2014-04-20 10:18:06


Thanks, Chuck. That was what I meant, but perhaps didn't state clearly enough. I use the arrow keys INSTEAD of the Enter keys.

You also raise a good point about having to use the Enter key in a few cases, such as editing a cell. That is why I also change the default move upon pressing Enter.

2014-04-19 19:30:33

Peter Atherton

Dave, you write them in the Visual Basic Editor. Press ALT + Fll to open the editor. Generally macros are entered in a module (Insert, Module) but un this case the macros go in the workbook module. To access this choose View, Project. You will see a list of the worksheets in your book followed by an Excel Icon. Double click this to open the module.

You will see two dropdown boxes. Click this and choose Workbook. Then click the right dropdown to see a list of built-in options. As some of these need to specifiy variables it is always better to use this method when starting writing a Workbook or a Worksheet event macro.


2014-04-19 15:30:26

Chuck Trese

Actually, there is one thing even easier.......

It is also possible to simply press the arrow key INSTEAD OF the Enter key, to tell Excel which direction to go.

However, this does not work if you're entering data by using the formula bar or doing "in-cell editing" (in those cases, the arrow keys are already used for moving around inside the cell).

2014-04-19 13:02:49


Why not just take the simple approach, as I do. In different spreadsheets I want the cursor to move in different directions, so I just set the default to not move after pressing Enter (do this by un-checking the box for cursor movement in the dialog box described above) so it will stay in the same cell if you press Enter. Then I simply use the arrow keys instead of Enter after putting data in a cell. If I want to move the cursor down, I press the down arrow, etc.

Simple, no macros to write. And I can move the cursor in any direction I want, even back to the previous cell if I want. I can customize the direction with each data entry. What could be easier than that.

2014-04-19 11:05:23


What I would like to know:

I'm sitting in front of my PC with my spreadsheet open, what do I do now to create the macro?

2014-04-19 11:02:37


Must admit I'm a novice, but, where di I go to enter this macro?
And, exactly what do I type from the info above?

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

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.