Different Cell Movement in a Single Worksheet

Written by Allen Wyatt (last updated March 15, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


Patrick wonders if there is a way to have the cell movement move to the right on one worksheet in a workbook and down on another worksheet in the same workbook. The only thing he can find is a global setting for all the worksheets in the workbook.

Patrick is correct; this setting is something that is handled globally in Excel. You can change the setting 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 "After Pressing Enter, Move Selection" check box is checked. (It should be checked, by default.)
  5. Using the Direction drop-down list, change the direction as desired.
  6. Click OK.

This changes the direction of movement for all workbooks you open in Excel. If you want the movement to vary for a single worksheet, you can accomplish this through the use of a few event handlers. Two of these are attached to the worksheet you want to be handled differently; just right-click on the worksheet's tab and select Code from the resulting Context menu. You can then paste the following into the Code window:

Private Sub Worksheet_Activate()
    Application.MoveAfterReturnDirection = xlToRight
End Sub
Private Sub Worksheet_Deactivate()
    Application.MoveAfterReturnDirection = xlDown
End Sub

These event handlers are triggered, respectively, when the worksheet is activated and when you deactivate the worksheet by selecting a different worksheet. Note that upon activation, the direction is set to xlToRight, which means that movement will by to the right. When the worksheet is deactivated, the direction is set to xlDown, which is assumed to be the default direction.

You will also need a third event handler; this one should be added to the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.MoveAfterReturnDirection = xlDown
End Sub

This is necessary because if you close Excel while the "move right" worksheet is active, then the movement direction remains set to xlToRight. This means that the next time you start Excel, that becomes the default direction. By including the BeforeClose event handler, you ensure that the default is set as it should be.

There is only one small potential gotcha with this approach: When you first open a workbook, the Activate event is not triggered for whatever worksheet is first displayed. This means that if your "move right" worksheet is displayed for the workbook, by default, the movement won't be changed to xlToRight; it will still be set to the default, which is presumably xlDown. To force the event handler to run, you'll need to display a different worksheet in the workbook and then come back to the original worksheet. If you would rather not do this manually, you could add a fourth event handler, this one also in the ThisWorkbook module:

Private Sub Workbook_Open()
End Sub

This macro selects the worksheet named Sheet2 and then immediately selects the one named Sheet1. You should, of course, change the worksheet names to reflect the ones appropriate for your workbook—just make sure that the last worksheet selected is the "move right" worksheet.


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 (13640) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 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. ...


Checking for Valid Hyperlinks

If you have a document containing a lot of hyperlinks, it would be cool if there was a way to check all those hyperlinks ...

Discover More

Starting a New Section on an Odd Page Number

A common pagination convention used in publishing is for new chapters (and sometimes new sections) to start on an ...

Discover More

Skipping Rows when Filling

Using the fill handle is a great way to quickly fill a range of cells with values. Sometimes, however, the way to fill ...

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)

Setting the Calculation Default

Excel can recalculate your worksheets either automatically or manually. The default is to calculate them automatically, ...

Discover More

Changing the Ribbon's Size and Look

The Ribbon, while debatably handy, can be downright difficult to use for those with a sight impairment. Here are some ...

Discover More

Getting Audible Feedback

Want to get a little bit of sound with your data? Excel can provide audible feedback that you may find helpful. Here's how.

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}] (all 7 characters, in the sequence shown) 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 + 4?

2024-03-17 00:34:05


There is another little-known Excel cursor movement behaviour that I often use when entering several records (rows of of data):

When you press <TAB> after entering a content of a cell, the cursor will move one cell to the right. When you are finished with that row, press <ENTER> and the cursor will jump to the cell below the first cell you entered data in that first row.
This way it is easy to fill several columns of data, row by row, especially if you are using the numeric keypad.
The rows may have different number of columns. The only requirement is to use only <TABs> between cells, then <ENTER> at the end of the row.
Pressing <Shift><TAB> to move back to previous cell for correction doesn't break this functionality.

2024-03-15 05:24:30

Mike J

An alternative to the 4th macro, which does not change the open sheet when loading the workbook could be:

Private Sub Workbook_Open()
OpenSheet = ActiveSheet.Name
End Sub

"Normal" to be replaced with any Move Down sheet in the workbook.

2019-06-10 02:56:22


@Willy: It's a matter of personal preference, and it depends on the data. My reason is that if one is entering a row or column of numbers using the numeric keypad for entry, the Enter key is quick and easy to hit; the arrow keys are further away, and one has to hit the correct one each time. If the work is not that repetitive, I often use the arrow key to end the entry in a cell and move on.

2019-06-09 11:12:31

Willy Vanhaelen

Why not uncheck "After pressing Enter move selection" and simply press the arrow key for the direction you want. And when you press Enter the cellpointer stays put (what I often want). I already work more than 20 years with Excel and I never checked this option.

2019-06-09 04:30:35

Chris van Zyl

@Allen: Thank you for the reference to the other tip - it contained the answer to my question about the use of variables.

@Roger: The following code will trap a right arrow key movement (The other keys are, as one would expect, {UP}, {DOWN} and {LEFT} ). Hopefully you will be able to modify it using the knowledge in this tip to get at least this aspect of Supercalc back. I don't remember this aspect , but I do miss the way the row and column of the currently active cell stood out, so that it was very easy to see where one was.

Sub Trap_Right_Arrow()
Application.OnKey "{RIGHT}", "Do_Right_Arrow"
End Sub

Sub Do_Right_Arrow()
MsgBox "Right arrow pressed"
' Turn trapping off
Application.OnKey "{RIGHT}"
SendKeys "{RIGHT}"
End Sub

2019-06-08 10:40:48


How about the way SuperCalc used to handle this (by default): whichever way the cursor LAST moved is used after ENTER, until the direction is changed. So if I use the RIGHT arrow to move from any cell, anytime I hit ENTER after that - the cursor would move right; if I then used the UP arrow the cursor would be moved up until the direction changed.

It may sound wierd, but it really is handy once you get used to it, and it is the one thing that I (still) miss most after I switched from SuperCalc to Excel.

How would you code that?

2019-06-08 08:53:22



This tip (as it says) deals with modifying movement after Enter on a worksheet basis. If you are wondering how to do things on a workbook basis (as you imply), see the following:



2019-06-08 08:46:25

Chris van Zyl

Thanks, as ever, Allen. My understanding of how the different kinds of variables work in VBA is imperfect, hence the following question:

Would it be possible to have the Workbook_Open sub store the movement direction in force when a workbook is opened in a variable, and then have a Workbook_Close sub restore that movement direction using the value stored in the variable when the workbook was opened?

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.