Donna asked a question at one of my Excel 2007 seminars: There used to be a way in Excel 2003 to prevent the active cell from moving downwards after pressing Enter. In today's Episode 838 - Where Is It Wednesday, I will take a look at how this feature works, where it is in Excel 2007, and how to simulate the feature using a shortcut key.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, hey, alright.
Well, it's Where Is It Wednesday, want to welcome you back to MrExcel netcast.
Thanks to George Wood for Where Is It Wednesday theme.
I was doing a Excel seminar and someone came up and said, "You know, had a great feature that I loved in Excel 2003.
I can't figure out where it is in Excel 2007." You know, normally if we enter a number here and press ENTER, it goes down.
There used to be way in Tools, Options, on the Edit tab, to be able to control whether we went to the right, or this person didn't want it to go anywhere-- they just wanted this to stay in the current cell.
Well, now if you just needed to do this for one cell, you can hold down Ctrl while you press ENTER, and that keeps things in the current cell.
So, that's a good thing to know.
It's also possible to enter the same value in many cells using that trick.
So, 1, 2, 3, press Ctrl+ENTER, and it enters that same value or formula in the entire selection.
But what we want to do here is figure out how to get back in Excel 2007 to control that cool feature called "Move cell pointer direction after ENTER".
So what we're going to do is, we're come up here to the Office button-- it's basically the old file menu-- and at the bottom of this fly-out menu, we have Excel Options.
Now, on the left hand side we have many categories-- Popular Formulas, Proofing, Save, Advanced, and, you know, all the good stuff's up here on Popular, and all the stuff that they couldn't find any place to fit is on Advanced.
The Advanced tab, generally, is about a mile long-- sections and sections-- but luckily what we need here is right up at the top-- Editing options, "After pressing Enter, move selection".
Direction, well, if you don't want it to move anywhere at all, just uncheck that, click OK.
And now when we press 1, 2, 1, ENTER, it just stays exactly in the current cell; but if you want it to move to the right-- Tools, Excel Options, again go to the Advanced tab, and turn the feature back on and say, "After pressing ENTER, we're going to move right.
Your choices are down-right, upper-left.
Click OK.
And now, if we're entering this data-- 1, 2, 3, and 1, 2, 3, ENTER, 2, 3, 4, ENTER, 3, 4, 5, ENTER-- we can enter data going across and then just to finish off the tip, if you needed to fill in Q1 through Q4 for these rows, pre-select the area and then basically, as we go through it will jump back to Q1 after entering Q4.
So, a great way to speed up a little bit of data entry-- you know I hate to do data entry, but every once in a while it's something we got to do.
By using those features under Excel options, you can speed things up dramatically.
Well, I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel.
[ music ]
Well, it's Where Is It Wednesday, want to welcome you back to MrExcel netcast.
Thanks to George Wood for Where Is It Wednesday theme.
I was doing a Excel seminar and someone came up and said, "You know, had a great feature that I loved in Excel 2003.
I can't figure out where it is in Excel 2007." You know, normally if we enter a number here and press ENTER, it goes down.
There used to be way in Tools, Options, on the Edit tab, to be able to control whether we went to the right, or this person didn't want it to go anywhere-- they just wanted this to stay in the current cell.
Well, now if you just needed to do this for one cell, you can hold down Ctrl while you press ENTER, and that keeps things in the current cell.
So, that's a good thing to know.
It's also possible to enter the same value in many cells using that trick.
So, 1, 2, 3, press Ctrl+ENTER, and it enters that same value or formula in the entire selection.
But what we want to do here is figure out how to get back in Excel 2007 to control that cool feature called "Move cell pointer direction after ENTER".
So what we're going to do is, we're come up here to the Office button-- it's basically the old file menu-- and at the bottom of this fly-out menu, we have Excel Options.
Now, on the left hand side we have many categories-- Popular Formulas, Proofing, Save, Advanced, and, you know, all the good stuff's up here on Popular, and all the stuff that they couldn't find any place to fit is on Advanced.
The Advanced tab, generally, is about a mile long-- sections and sections-- but luckily what we need here is right up at the top-- Editing options, "After pressing Enter, move selection".
Direction, well, if you don't want it to move anywhere at all, just uncheck that, click OK.
And now when we press 1, 2, 1, ENTER, it just stays exactly in the current cell; but if you want it to move to the right-- Tools, Excel Options, again go to the Advanced tab, and turn the feature back on and say, "After pressing ENTER, we're going to move right.
Your choices are down-right, upper-left.
Click OK.
And now, if we're entering this data-- 1, 2, 3, and 1, 2, 3, ENTER, 2, 3, 4, ENTER, 3, 4, 5, ENTER-- we can enter data going across and then just to finish off the tip, if you needed to fill in Q1 through Q4 for these rows, pre-select the area and then basically, as we go through it will jump back to Q1 after entering Q4.
So, a great way to speed up a little bit of data entry-- you know I hate to do data entry, but every once in a while it's something we got to do.
By using those features under Excel options, you can speed things up dramatically.
Well, I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel.
[ music ]
