I try to avoid my mouse whenever possible. Sometimes it’s not, but here are some tips that may help you get around Excel a little easier. Feel free to comment on your own favorite navigation tips.
Control+Tab > Cycles through open workbooks. It replaces using the Windows menu. Control+Shift+Tab cylces through in the opposite direction.
Control+PageDown > Moves left to right through sheets in the active workbook. Control+PageUp moves through them in the other direction.
Alt+F8 > Opens the Macro dialog box. It replaces using Tools>Macro>Macros.
Alt+F11 > Opens the Visual Basic Editor. If you do any coding at all, you’ll want to know this one. It replaces Tools>Macro>Visual Basic Editor
VCR buttons > Right clicking these shows a pop up menu of all the sheets in the workbook. Handy if you have so many sheets that you can’t see them all. See the screen shot below.
I couldn’t live without ctrl-down arrow and ctrl-up arrow etc. to move to the end of a block of data. Add the shift key to select… Working with spreadsheets of many thousands of rows these are indispensable.
Try watching someone use the mouse to scroll and select 50,000 rows on a machine that is running sluggishly because the resources are running low… It will drive you crazy.
Shortcuts I can’t live without:
Ctrl-Space: select current column
Shift-Space: select current row
Ctrl-Minus: delete selection
Ctrl-1: open formatting properties dialog
F2: Edit cell value
I completely agree about keyboard navigation. In fact, when I taught Excel at a local college, I often unplugged the mice before the students arrived and forced them to learn how to effectively use Excel via keyboard alone.
I have found the following shortcuts especially helpful:
F3 – Paste Name
CTRL + F3 – Define Name
CTRL + PageUp
CTRL + PageDown
One other handy shortcut key that is not as well-known, and maybe not as useful, but one that I do use on occasion is
ALT + PageUp (or ALT + PageDown)
It moves the cursor horizontally one page, rather than vertically as it would without the ALT key held down.
Have a column of 6 digit numbers on sheet A. Doing a vlookup against a coulmn contaning same numbers in sheet B. Vlookup doesn’t work until I press F2 to edit the column in sheet A, then vlookup gives the disired reslut.
I don’t belive that this is a failure of vlookup.
It has to be some formating of the column in sheet A.
I have run into this before – wanted to write a macro to do F2 to the entire column but can’t figure it out.
Please Help
Hi,
I never knew the Ctrl or Shift + (spacebar) to select the current column or row. It looks really useful.
I’m hoping to add a macro that selects the current column and then runs AutoFilter. Whenever I try this in VBA, I get static column references (eg Columns(“AR:AR”).Select) and not dynamic ones. Do you know how to get around this?
Cheers
Adam –
Use something like
ActiveCell.EntireColumn.Select
to define the range.
– Jon
Speaking of navigation, how about CTRL+. to cycle around the corners of a selected rectangular range?
I took data from Access and placed it into Excel. Some of the shortcuts don’t work like Ctrl- Down Arrow. Instead of going to the last data, it goes to the bottom of the page. The Ctrl key is now moving to the top,bottom, edge of the page instead of the last filled cell. Anyway to “reset” these commands? As you can see I can’t live without Ctrl and direction.
If you press End, then an arrow key, it goes to the cell just before the first blank cell (or if the active cell is blank, it goes to the first nonblank cell). If you hold down Shift while hitting the arrow, it selects all the cells between the initially active cell and the cell it takes you to.
I believe I found out why my problem occured. When I transfered the data from Access 2000 to MS Excel 2000 the data is automatically transferred as Biff5 data, however, if I pasted data as CSV, all the functionality worked. Thanks for your help though. I didn’t know that you could use “End” as well as Ctrl.