Excel Return to the First Column After Typing the Last Column
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
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?
Figure 64 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.
Figure 65 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.