MrExcel.com - Your One Stop for Excel Tips & Solutions

Return to the First Column After Typing the Last Column

August 26, 2021 - by

Return to the First Column After Typing the Last Column

Problem: I learned in “Automatically Move the Cell Pointer in a Direction After Entering a Number” how to set up the cell pointer to move right after I press Enter. This works great. I just typed figures for Q1, Q2, Q3, and Q4 (see below). So I can quickly enter all four quarters, is there any way to make Excel jump to cell B3 after I type in cell E2?

There are a list of cities in column A. You need to type in data for Q1, Q2, Q3, and Q4 in columns B, C, D, and E. Excel has been changed to move to the right after pressing Enter. Read the following text to learn how to jump from Q4 back to Q1.
Figure 59. Can Excel jump to the first column in the next row?

Strategy: Yes! Here’s what you do:

  • 1. Set up Excel to move right as described in the previous topic.

  • 2. Select the range before you start typing the data. For example, in the figure above, you might select B3:E99. Although you have selected a rectangular range, B3 is the active cell.

  • 3. Type 123 and press Enter. Excel will move to B4.

  • 4. Repeat this to fill in the numbers for Q2, Q3, and Q4. When you press Enter in cell E3, Excel will move to B4.

By pre-selecting the data input range of B3:E20, you can simply press Enter to move to the next cell, including jumping from Q4 back to Q1.
Figure 60. Excel will jump to B4 from E3

Alternate Strategy: There is another way to handle this situation, although it’s not as straightforward as the method just described. Whereas the method just described requires you to use Right as the Move Selection Direction, this strategy requires that setting to be set to Down:

  • 1. Select File, Options, Advanced. Open the dropdown for After Pressing Enter, Move Selection Direction, and choose Down. Click OK.

  • 2. Select cell B5. Type a value and press Tab. Excel will jump to C5.

  • 3. Type a value for Q2 and press Tab. Excel will jump to D5.

  • 4. Type a value for Q3 and press Tab. Excel will jump to E5.

  • 5. Type a value for Q4 and press Enter. Excel will jump back to cell B6!

I have no idea how Excel knows how to do this. Apparently, Microsoft programmed in a bit of logic to remember the first column you tabbed out of. When you switch from Tab to Enter, Excel will jump down one row and back to that column. Amazing. If you make a mistake in a previous cell, use Shift+Tab to move backwards through the list.

This article is an excerpt from Power Excel With MrExcel

Title photo by Mick Haupt on Unsplash