Excel Keystrokes
I used to work for a financial analysis company developing Excel macros/applications in VBA. There were — and still are — some 10th level Excel blackbelts that work there. I picked up quite a few keystroke tricks while working there. Even though I don’t work there anymore, I use these every time I’m in Excel.
- ctrl-Space - select column
- shift-Space - select row
- ctrl-Arrow Key - depending on which arrow key you hit, it will move the cursor along a series of cells*
- ctrl-Page Up, ctrl-Page Down - cycles through worksheets
- ctrl-Home - selects A1 on the current worksheet
*Longer explanation: Say that A2 is selected, and column A is populated from A1 to A50. If you hit ctrl-Down Arrow cell A50 will now be selected. If you then hit ctrl-Up Arrow it will take you to A1. The same holds true for left and right. Let’s also say that cells A60-A100 are populated. If, with A1 still selected, you hit ctrl-Down Arrow once (taking you to A50) and then a second time, it jumps over the blank gap from A51-A59 and selects A60. If you hit it a third time you will be at A100. It will probably make a little more sense if you tinker around with it a little.
What are your favorite Excel keystrokes?
Feb 20th 2007
F2 F2 F2 F2
Hit the F2 key to edit a cell. When I first discovered this keystroke, it was like the clouds parted and the light of God beamed down on my keyboard. Give it a shot right now if you haven’t used the F2 key before. (Hint: it will allow you to edit the data in the selected cell)
Feb 20th 2007
Command-T on a mac, or F4 on a PC.
This changes a reference from relative to absolute or vice-versa. Repeating the command will cycle through all potential variants of partial and full references. Don’t know how people live without this one.
Feb 20th 2007
My favourites:
Ctrl+1: Brings up the “Format cells” dialog box for your current selection
Ctrl+Shift+4 (i.e. Ctrl+$): Formats your current selection as currency
Ctrl+Shift+5 (i.e. Ctrl+%): Formats your current selection as a percentage
Ctrl+Shift+6 (i.e. Ctrl+^): Formats your current selection as scientific notation
F4 (when editing a formula), as mentioned above
Ctrl-Shift-Arrow key: As per the description of Ctrl-Arrow above, but it also selects all the cells in between. The Ctrl-Arrow, Shift-Arrow, Ctrl-Shift-Arrow and Ctrl-Backspace combinations also work in most Windows apps for word-wise movement, selection, word-wise selection and word-wise deletion respectivelyc.
Feb 20th 2007
@Jack:
The Ctrl-$ and Ctrl-% are brilliant! I’m going to have to find a reason to use those today.
Another combination I use very regularly before saving sets the focused cells on every tab to A1. First you’d select your last (or first) tab. Then while holding Ctrl down alternate between Home and Page Up (or Page Down). This cycles you through every tab moving it to A1. I’ll do this before I send a workbook out for review.
Feb 20th 2007
@Marshall:
As you are probably aware, the F4 (cmd-T) keystroke you cite is when you have the referred to cell highlighted in your formula. Another use of the F4 keystroke is to repeat the last action. If you insert a row, column, or cell then hit F4, it will duplicate the action that just took place. Quite handy when you need to insert 10 rows without having to do them one at a time.
Feb 20th 2007
Very useful. I do a lot of work with database output into Excel. A favourite keystroke combination of mine is select a formula, F2 (edit), F9 (calculate), converting the formula to a value. Often use ‘&’ in a formula to concatenate text, then F2, F9, ctrl+c to copy it so I can paste a “sentence” into other apps.
Feb 20th 2007
Using F4 for the absolute/relative edit doesn’t seem to work in Excel 2007 — or am I missing something?
Feb 20th 2007
OMG!! F2! F2! *faints on desk*
Feb 20th 2007
Ctrl+PgUp and Ctrl+PgDn — to navigate through worksheets within a workbook — was briefly mentioned by JASon above, and is huge.
Ctrl+Minus and Ctrl+Shift+Plus do the Delete and Insert commands respectively, and are extra-huge if you use them immediately after pressing Shift+Space or Ctrl+Space to select entire rows or columns.
I like Ctrl+Shift+8 to highlight the active range.
Ctrl+9 and Ctrl+Shift+9 hides and unhides rows.
Ctrl+0 and Ctrl+Shift+0 hides and unhides columns.
Basically, Ctrl+Shift+[any key on the top row of your keyboard] does something interesting!
Feb 20th 2007
I know it involves the mouse but i find it very helpful
Ctrl + moving scroll wheel on the mouse up or down adjust the zoom of the sheet.
Feb 20th 2007
Ctrl + D
This copies whatever is in the above cell or if you select a number of cells and use Ctrl + D it will copy what is in all of the above cells into those selected.
Feb 20th 2007
One keyboard shortcut that I have been looking for but have not found is paste formulas. (as in edit/paste special, formula button) This is very handy and a value or a formula without disturbing formatting. I always make a macro on my home machine to do this but miss it on other machines.
Feb 20th 2007
@Derek:
If I’m reading your comment correctly, you are wanting to paste the value of the cell over the top of the formula that created that value. While this solution isn’t exactly a single keystroke, it does achieve your end goal rather quickly (this assumes you have already copied the cell(s)): Alt-E (opens the Edit menu), S (chooses the Paste Special option from the menu), Alt-V (selects the Value option from the form), Enter. When you actually use it you can go pretty quick, Alt-E S Alt-V Enter. Hope that helps out some.
Feb 21st 2007
ALT+= (equal sign): Insert the AutoSum formula
Feb 21st 2007
Wish I’d found this three weeks ago, after I had just broke my hand and found using a mouse a bit painful!
F2! Sheesh…
Feb 21st 2007
Ctrl+” is another way to do the fill in exactly as above, great when you are filling in some redundant data.
Feb 21st 2007
Wonderful post, thanks… I wish I had found this yesterday, as things at the office have been miserably slow this week, I’ve decided to teach myself more about Excel.
Does anyone know if there’s a keyboard shortcut for auto-sorting a column? And if there’s a modifier to make it do only the column and not the ones beside it (I am really new to Excel, obviously)? In fact, anything keyboardy and related to filtering and sorting would be ideal (I remember there being a “show all” key for Filter Mode but I’ve forgotten it).
Guess where I’m writing all of these shortcuts? Excel!
Feb 21st 2007
Matthew, you are my hero!!! F2! Actually this was a question that I’ve been wondering about half a year!
Feb 21st 2007
If you liked [F2], you’re going to love [ / ].
I’m surprised nobody else has mentioned this. It’s a holdout from when Lotus 1-2-3 was the standard spreadsheet, and Excel was the plucky newcomer.
When you hit that little forward slash key [ / ], it moves your cursor up to the menu bar. You can now get at all the commands on the pull down menus using the underlined letters you see in each of the menus.
So Will M, you can do a sort by hitting [ / ] [ D ] [ S ]
And JASon’s example can also be done with [ / ] [ E ] [ S ] [ V ]. It’s one that I use all the time. It’s faster in my mind because you don’t have to worry about doing finger gymnastics to find the [ ALT ] key.
If it isn’t working, or you don’t like the “/” key, check the “Tools | Options | Transition” menu. The “Microsoft Office Excel menu key” lets you enter any character you want in here.
Feb 22nd 2007
At least of French Windows Excel version I use these shortcuts, probably slightly different on Mac and/or english versions
Ctrl + : inserts the current hour
Ctrl + ; inserts the current date
Ctrl + End selects the last bottom right cell of the edited zone
Feb 22nd 2007
Insert a new row…
1 - Shift Space to select the row
2 - Control + to insert a new row
Delete a row
1 - Shift Space to select the row
2 - Control - to remove row
Feb 24th 2007
Derek, Jason, Chris
paste the value of a formula in the same cell as the formula by F2, F9
You can also use the F9 to check pieces of a nested formula. Highlight the piece(s) you want to test, press F9, it will replace that portion of the formula with the value. Then hit ESC to get your formula back.
Mar 2nd 2007
Actually, to be honest: Ctrl + Alt + $ or % or anything else is pretty sweet, it formats the cell accordingly automatically.
Mar 2nd 2007
Calling a halt to the dorky commenting and all associated follow-ups. Not that I don’t appreciate getting comments, but the point of this is to help people to work more efficiently, not to bag on anyone because they may or may not be a dork.
Now, any new nuggets of Excel wisdom?
Mar 3rd 2007
F2 for the Mac?
Recent Mac convert here.. and Excel whiz on the PC. Not knowing the keyboard shortcuts for Mac is killing me, though.
F2 only works for the PC and seems to be a “copy” command on the Mac. What keystroke let’s me enter the cell on a Mac? Any help appreciated!
Terry - Washington, DC.
Mar 4th 2007
@Terry in DC:
I use a Mac at home, but rarely use Excel on it so I hadn’t caught this nuance before. I did some digging, and the keystroke you are looking for is ctrl-U. Also, if you launch the Excel help, and search for “keyboard shortcuts,” the first returned value (at least for me) is a very extensive list of Excel shortcuts. Hope this brightens your day a little.
Mar 9th 2007
Ctrl+F is basic of course, but how many of us were saved hours of needlessly searching pages of code, when one of the most useful of the keystrokes was right before us?
Ctrl+F. It finds EXACTLY what you’re looking for.
What a lifesaver.
Apr 18th 2007
JASon thanks for the alt e s alt v tip — i was looking for the key strokes for copying and paste special formula but ms doesn’t show the key stroke. ctrl c, alte, s, alt f, enter for those following along at home.
Apr 19th 2007
dont think i saw this one.
ctrl+shift+any directional arrow = selects from the active cell to the last cell with data in the selected direction.
May 7th 2007
I am a billing person and I create bills from a spreadsheet of information. I use a macro to extract information from the spreadsheet and paste it into a billing template form. But I have not found a way to insert into the macro a function that will ensure that the data format matches the destination format. When I do this manually, I use the mouse to click on the Match Destination Format option in the Paste Options dialog box. But I cannot use a mouse click in the macro. The options in the Paste Options dialog box have the first letter underlined, so I am thinking that there must be a way to access them via keystroke, but how? Thank you so much to anyone who can help — this is a time monster!
May 30th 2007
When we are pasting something copyed from excel. We can find one more menu like paste option what is the short cut for to move on to that menu.
Jun 20th 2007
Whenever I select a range that spans multiple pages, I frequently use Ctrl +. (period) to check the four corners of the range to make sure I’ve selected what I intended. Each time you press Ctrl+. it jumps to the next corner in a round-robin fashion.
Jul 13th 2007
The Excel shortcut sheet at the site below contains all shortcuts mentioned in the posts above, all in a nice one page PDF layout.
http://www.navigatorpf.com/Downloads/Downloads/Excel_Shortcuts_/
Jul 21st 2007
Maybe someone can confirm this one I found as I tried out some of the keystrokes I learned here.. It is not documented in Excel Help’s extensive list or on this page: ctrl+> (ctrl+shift+.) .. it appears to copy the cell to the left and paste into currently selected cell, or for a selected range, copy the top-left cell in selection and paste into each cell in selection. The corresponding ctrl+
Jul 21st 2007
I forgot to encode my < sign there. The last sentence should read, “The corresponding ctrl+< however does not appear to be implemented.”
Jul 21st 2007
Actually I’m incorrect about the exact behavior of that keystroke. To be honest I don’t understand it.
Jul 29th 2007
Is there a shortcut key or combination to go back to the last cell you were looking at (ie the equivalent of SHIFT + F5 in Word)?
Aug 15th 2007
Anybody know what the keystroke is for jumping into edit mode in the tab holders? Say you’re in a cell and you want to change the name of your worksheet tab. I usually click on the tab then F2 –or– simply double-click. Is there a keystroke that takes you from the cell to the tab then into edit mode? Would that take 2 keystrokes? One keystroke would be awesome. LOL I’m just being lazy. Thanks!
Aug 15th 2007
Actually, i only double-click. I don’t click-and-F2. I don’t know why I said that. That doesn’t work.
Aug 29th 2007
I have seen people add one row to another with a few keystrokes. For example, adding A1:A4 to C1:C4 resulting in C1 containing +A1, C2 containing +A2, etc. Any ideas on how this is done?
Aug 30th 2007
In Excel, I want to wrap with a keystroke shortcut. Even though the W is underlined, I can’t get anything to work.
Sep 5th 2007
Does anyone know a shortcut for “insert comment”??? any ideas would be greatly appreciated!!!
Sep 10th 2007
Shift + F2 is “insert comment”. Esc twice exits comment edit. To delete a comment, the right click button on the keyboard if you have it, then ‘m’.
Sep 19th 2007
I agree with project finance consultant above, the following PDF answers most questions in this forum and is also easy to print.
http://www.navigatorpf.com/Downloads/Downloads/Excel_Shortcuts_/
Oct 16th 2007
I would like to be able to select the autofilter option on the cell and have it open with a keystroke.
I am currently building a macro that requires this step, and I am having no luck so far. I do not want it in VBA language if possible.
Oct 17th 2007
Your excel keystrokes topic contain good shortcuts for excel. And new user takes more advantage after reading your article.
Thank you
Damon Thomas
http://www.excelfilerepair.com
Oct 17th 2007
All the shortcuts are good but I used ” ctrl-Page Up, ctrl-Page Down - cycles through worksheets” a lot.
Thank you
http://www.excelfilerecovery.com
Oct 24th 2007
I often work with data in scientific notation. Is there a shortcut for entering numbers using only the number section of the keyboard? (i.e., entering 1.7E+09 without using the ‘e’ character)
Nov 26th 2007
Any suggestions for the following: I have a set of cells, say 10 and need to get the mean +/- s.d. While I can do this in two separate steps, i.e., mean in one cell, then s.d. in another, is there a way that I could combine the two in one cell? I tried using a semi-colon in the formula bar, but :(.
Thanks.
Nov 30th 2007
Thanks, TechJive, for the excellent time-savers. The Edit shortcut, [F2], has been one of my favorites for years but is often unknown even by the experts. I also like [Alt] + [Enter] to move to a new line in the same cell when typing long text entries. For a handout of Excel keyboard shortcuts for moving and selecting plus other Excel tips and tricks go to:
http://www.softwarepro.com/tips/handouts.htm#excel
Feb 24th 2008
I get cramps clicking “check boxes” in Pivot Tables anyone know how to choose a range in stead of hundreds one after another.
Feb 26th 2008
Does anyone know of a shortcut for repeating a command or macro. I seem to recall in older versions of office there was some simply keystroek that you display a dialog box that you simply typed in the number of times to repeat a command. Once the quantity was entered, the command was selected and it was repeated that number of times. Does this ring a bell for anyone?
Thanks!!
Mar 3rd 2008
for the mac users, f2 doesn’t work. to edit cell contents right in the cell, press ctrl-u instead.
Mar 19th 2008
kind of a shortcut, well I like it. Found this gem on google a while ago. Useful when you need to do a lot of autofilling on large worksheets.
highlight blanks that need to be ‘copied into from above’ (F5 special, blanks)
then just type
=
up (up arrow)
ctrl-enter
voila, all data copied down without having to do it individually.
Nik
Apr 8th 2008
ctrl+space is not working on excel 2007 on my desktop. help please
Apr 22nd 2008
I was desperate to find out how to cycle through the sheets. Thanks much.
My favorite shortcut - Ctrl+5 for strike through formating.
-Shrini
Apr 24th 2008
Here are some more tips on navigation with your mouse.
http://www.wikihow.com/Navigate-in-Excel-Without-Using-Scrollbars-or-Arrows
May 7th 2008
Nothing beats the good old F11 to create a graph! Just select the range, press F11 and you are done. If you want it to be more pretty, simply modify your default chart. To delete, press Alt-E-L, but be careful not to delete the wrong sheet (you can’t undo!)
May 7th 2008
Shift-F11 inserts a new worksheet
May 8th 2008
if you have one of those apple wireless keyboards ( the ones that don’t have the PgUp, PgDn etc)
then you might like this….on the Ctrl+PgUp and Ctrl+PgDn — to navigate through worksheets within a workbook tip do fn+option+left/right-Arrow for it to work…big find for me
May 10th 2008
I can’t use my arrow keys to navigate cells in a sheet. Pushing an arrow key just scrolls the sheet over and leaves the selected cell the same. What happened and how can I change this back? I’m very frustrated. Thanks
AW
May 10th 2008
To AW:
You have Scroll Lock on. Turn it off.
HTH,
JP
Aug 1st 2008
“Command-T on a mac, or F4 on a PC.
This changes a reference from relative to absolute or vice-versa. Repeating the command will cycle through all potential variants of partial and full references. Don’t know how people live without this one.”
as to the PC keystroke, dude that’s frakn funny.
nice folllow up as well.
oh what a wicked web we weave…
Aug 8th 2008
Not sure if this is where I ask a question? But here goes.
Column A = 1500 Column B=800 Column C needs to return the difference (if any) OR 0 (if no difference)
Aug 21st 2008
Every time I paste on Excel there’s a drop down menu (paste options), which is not too bad. The only thing is that sometimes I’m pasting too much desiring having less clicks on this menu. For example, today I’d like to leave it in “Match Destination Formatting” as default, instead of opening and clicking it for every paste I do.
Is there somehow that I can do this?
Thanks in advance!
Aug 26th 2008
Carolyn,
The way you have described it suggests that you could simply do A1-A2 to get the difference, but I am guessing that what you are really after is something like =MAX (A1-B1,0) ?
Hope it helps
Rickard