Saving Movement on Enter with a Workbook

by Allen Wyatt
(last updated April 19, 2014)


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

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


Printing Document Properties

Word maintains quite a bit of information about a document in a special collection of items called "properties." You can ...

Discover More

Changing Paper Size for a Complete Workbook

If you need to change the size of paper on which your worksheets will be printed, it can be bothersome to make the change ...

Discover More

Highlighting the Rows of Selected Cells

If you lose your place on the screen quite often, you might find it helpful to have not just a single cell highlighted, ...

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)

Disappearing Status Bar

Ever had your Excel status bar disappear unexpectedly? Here's some ideas on why this may be happening.

Discover More

Picking a Workbook Format

Need to share workbook information with a wide number of people? It can be puzzling to figure out which version of Excel ...

Discover More

Seeing Excel's Program Window

Have you ever opened Excel to find that the window you saw yesterday is not the same as it is today? Sometimes, for ...

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 four more than 2?

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.