Sometimes it’s the little things that make a big difference so today I’m going to share with you a few of my favourite Excel shortcuts. They're not necessarily all keyboard shortcuts either.
These are the type of skills that you pick up over the years watching your colleagues wield their Excel swords.
So, without further ado and in no particular order:
1. Copy and Paste as a value from a cell containing a formula – F2, F9, ENTER. For example you have a formula in a cell like this:
F2 to edit the cell:
F9 to calculate the formula:
Press ENTER to keep it as a value:
Note: you can only do this with one cell at a time.
2. F3 – Paste a named range into a formula. This is handy if you can’t remember the name you’re looking for. Pressing F3 while writing the formula brings up a list of your names and you simply choose the one you want:
Of course if you know the name of your range you can start typing it out and then select it from the list using the arrow keys or mouse, and then tab or double click to select it:
3. F11 – insert a chart. Select your chart source data, press F11. This will insert a chart on a new worksheet in the file. If you want your chart on the same sheet as your data you can use ALT + F1.
4. F12 – Save As. Remember ‘Save As’ is only necessary the first time you save the file, after that you can just use CTRL+S to save updates. This works in Word and PowerPoint too :-). Actually it probably works in other Microsoft Programs but I stopped testing at PowerPoint.
5. F4 – Repeat/re-do. This works in other Microsoft Programs too.
6. F4 also inserts the dollar signs for absolute references when editing a formula.
Tip: you don’t have to select the whole cell or range reference to set it, just select part of what you want to absolute. For example; in the image below you can see I have only selected from 2:D:
Then press F4 and it will absolute both D2 and D13:
Pressing F4 again and again will scroll through the levels of absolute reference from column to row, or all.
7. CTRL+Z – undo
8. CTRL+* or CTRL+A to select the current range while at least one cell is the range is selected.
9. CTRL+# to apply Date format and CTRL+SHIFT+! For two decimal format.
10. CTRL+; Insert today’s date.
11. CTRL+O – open file.
12. Select all objects (charts, shapes, pictures etc.) – left-click on one and then press CTRL+A to select them all.
13. CTRL+Pg Up/Pg Down to scroll through worksheets.
Or, if you’ve got a lot of worksheets you can right-click on the scroll buttons for the sheets to reveal a list of sheets – click one to (Activate) jump to it or select ‘More Sheets’ and double click the one you want to go to.
14. Double click the edge of the selected cell to jump to the end of the data. For example; double click the bottom of the selected cell to go to the end of the range of data. Or do the same using the keyboard - press CTRL + down/up/left/right arrow.
15. CTRL+SHIFT+ down arrow to select the column of data. Likewise with the other arrow keys.
16. CTRL+TAB to toggle between workbooks.
17. CTRL+1 opens Format Cells dialog box….I could go on all day with the CTRL shortcuts as there are tons of them.
18. ALT+= for AutoSum.
19. Auto insert totals: select table of cells including a blank row/column.
Press ALT+= to insert totals:
20. If you enter a lot of numbers and sums using the numeric keypad you might find it quicker to start your formulas with the plus sign instead of the equals sign.
For example: you can type +20+10 then press ENTER and Excel will complete the equals sign for you like this: =+20+10 Perfect if you don’t want to reach all the way over to the equals sign!
Likewise you can do this +VLOOKUP(A8,A2:D13,2,FALSE) and Excel will complete it like this =+VLOOKUP(A8,A2:D13,2,FALSE).
That’s just a few (well, 20 actually) that come to mind. You can find more in my 100 Excel Tips & Tricks eBook.
Thanks
I’d like to thank my friend, Shannon Hommel, as she sent in quite a few of these handy tips.
What are your favourite shortcuts or tips? Please take a few minutes to share them in the comments below.
F4 is great for quickly performing the same action on a series of targets (eg removing the whole row containing a selected cell) but pressing F4 on the wrong target means you have to ctrl-Z to undo and then recreate the original action to start F4-ing again
but no!
select the next correct target and alt-enter will repeat the last F4-ed action and off you go again
wuuh!
Now that does deserve a Wuuh! Thank you for sharing, Jim. That has bugged me for my entire Excel life 🙂
Great shortcuts! In case any of you are Mac users, I created a simple keyboard product that shows you the 20 most commonly used Mac Excel keyboard shortcuts.
I used to be a PC Excel user (trained on Excel 2003) and have shifted to the Mac for other reasons, and was always stumped on why the shortcuts are not the same (or don’t exist). After doing research, I found that most of the keyboard shortcuts we do on the PC are indeed available on the Mac.
Check us out at http://www.thekeycuts.com to learn more.
Thanks, Al. Quite a few of our members use a Mac.
#17: Ctrl+1 opens the formatting dialog or task pane for whatever object is selected.
Oh, yes. Great tip. Cheers Jon 🙂
Hi Mynda,
Here are 20 keyboard shortcuts I’ve posted on LinkedIn almost 2 years ago(!).
I believe that most of them are quite new to many Excel users.
Another advantage: they’re not dependent upon the UI (like the ALT+letter, which work only in an English user interface).
P.S.
I’ve posted on that thread at least 70(!) additional shortcuts.
If you’re interested, I’ll post them here again…
❤❤❤
Best Regards,
Meni Porat
Hi Meni,
Thanks for that but unless you’re part of the LinkedIn Group I don’t think you can access that link.
Perhaps 70 is too many, I’d rather you just picked the ones you find most useful, or even better would be the ones that are useful but people typically don’t know about. e.g. a great example of this is Joseph’s CTRL+C, CTRL+V, CTRL, V to paste values for multiple cells.
I’m looking forward to your list 🙂
Cheers,
Mynda.
Hi Mynda,
You’re absolutely right. Not everyone has access to LinkedIn….
So here are some of my favourite keyboard shortcuts.
I hope our readers will find most/all of them useful.
1. Alt+Shift+F1 = insert a new worksheet
2. Alt+Backspace = Undo
3. Ctrl+Shift+U = expand/collapse the formula bar (toggle)
4. Ctrl+9 = hide selected rows
5. Ctrl+Shift+9 = unhide rows within selection
6. Ctrl+0 = hide selected columns
7. Ctrl+Shift+0 = unhide columns within selection
8. Ctrl+Shift+L = enable/disable filtering (toggle)
9. Ctrl+F6 = switch to the next workbook (when more than one is open) (toggle)
10. Ctrl+Tab = same as 9. switch to the next workbook (when more than one is open) (toggle)
11. F10 = enable/disable key tips (toggle) [same as Alt key]
12. Ctrl+F9 = minimize workbook’s window
13. Ctrl+F10 = maximize workbook’s window
14. Ctrl+Shift+” = copy the value from the cell above (i.e, if the active cell is A5, the value of A4 will be copied into A5)
15. Ctrl+- = display the “Delete” dialogue box
16. Ctrl+L = display the “Create Table” dialogue box
17. Ctrl+T = same as 16. display the “Create Table” dialogue box
18. Alt+Shift+Page Down = Go to next screen [rightwards] (without changing the active cell)
19. Alt+Shift+Page Up = Go to previous screen [leftwards] (without changing the active cell)
20. Ctrl+Shift++ = display the “Insert” dialogue box
Best Regards,
Meni Porat
Hi Meni,
Thanks for sharing your list 🙂
I use tip 10 in Excel but tip 9 in Word since 10 doesn’t work in Word.
Cheers,
Mynda.
Hi Mynda,
Really enjoy reading this post as every day with new tricks added to it. Many of them are really good! 🙂
btw, Ctrl Space may not be working for some regions (like HK, where I come from) as it is the default way to switch language input method.
Cheers!
Thanks, MF. It’s handy to know when regional settings are different.
Cheers
Mynda
Great list Mynda!
My favorite is similar to #1, but works on a range of cells:
Select Range, Ctrl+C, Ctrl+V, Ctrl, V
Awesome, Joseph. Great trick. I will certainly use that.
I use Ctrl+Shift+8 (or Ctrl+*) quite often to highlight entire bodies of data.
I press Alt then W then W to view open files to switch between windows … sometimes I toggle through them using Alt+Tab, but if there’s too many files open, it’s quicker to go with Alt-W-W.
I use Alt-E-E to find and replace.
I use Alt-A-T to apply autofilter.
I use Ctrl+Y to redo … didn’t know about F4 listed above.
I use Ctrl+Home to go to the top left corner of a spreadsheet, or a frozen pane.
Also use the Shift+Space and Ctrl+Space to highlight rows/columns as noted below.
Some of these are old keystrokes left over from pre-Office 2007.
Cheers, Melody.
I’ve not heard of Alt-E-E I use CTRL+H for Find and Replace.
I like the Alt-W-W. I’ll use that!
Thanks for sharing.
Mynda.
Using Shift+Space or Ctrl+Space inside of a Table range will select the row or column in the table. Using the keystroke again (i.e. Shift+Space Space) will expand the table row or table column into a full sheet row or sheet column.
Thanks for sharing this, actually shortcut 12 was very new to me..
I just want to add a tip that I got earlier from ExcelTips site: If you want to paste values to a filtered range, you need to GoTo>Special>visible cells only, and then paste your values…
Regards,
Mohammad
@ Mohammad – Very handy. Thanks for sharing.
@ Paul S – Great tip. I too use that all the time.
@ Lory – I like paste values too. If only the keyboard shortcut didn’t require so many keys 🙁
@ Michael Rempel – Glad you liked them. I had to stop myself using + to start a formula as I thought it might confuse people reading my tutorials. It was a hard habit to break!
@ Kayakbob – another ‘wuut’ trick I didn’t list is double click the fill handle to copy a formula down a column.
Cheers,
Mynda.
Selecting visible cells only can be achieved using alt + ;
Huh, that one doesn’t work for me 🙁 Must be doing something wrong.
My favorite shortcut is using copy & paste: copy your information, right click on the top left cell of your paste area and select the “Keep Source Column Widths (W)” option. This works especially well if you are copying from one workbook to another.
CTRL Tab where have you been all my life? Been missing you since windows 95!!!
Hi Tiffany,
CTRL+Tab still works right through to Windows 8. Why are you missing it?
Mynda.
I don’t have a short shortcut, but reading the rest of these brought this to mind. I found a trick for when I want the exact same formula in another cell. I can’t just move the formula because I want it in two different cells and I don’t want to have to type the whole formula again.
Click on the first cell, highlight the formula in the formula bar, press CTRL +C, press ESC (to get out of edit mode in the first cell), click on the cell where you want it copied to, press CTRL+V. The exact same formula is now in both places. This has saved me lots of time (and headaches) in those situations.
Most of these I knew, but the first and the last are new to me. Thanks! These are great!
Hi, Mynda, I really enjoy your tips. Here’s one of my favorite tips. You can easily make an entire range adjustable after the fact.
Have you ever created a spreadsheet with a column of formulae, only to decide later that you need to adjust it slightly? Perhaps you forgot to add a factor, or maybe you just want to ratio everything to account for something different. This tip will show you a quick way to handle this.
Setup: Start with a new spreadsheet. Enter the numbers 1-20 sequentially into the first 20 rows of Col A. Now enter the formula =pi()*A1 into B1, and copy or drag it down to fill the corresponding rows of column B. (Or just double-click the fill handle to have it done automatically.)
Tweaking: Assume this is what you originally had, except now you decide you really need to multiply everything by 3. Enter 3 into a blank cell, say D2, then select cell D2, copy it (use Ctrl-C), and then select cells B1:B20. Now go to the Home tab and select Paste/Paste Special. (You need to select the down arrow in the Paste box and then Paste Special at the bottom of the dialog box.) In the popup dialog box, under “Operation,” select Multiply and hit “OK.” Now look at the formula in B1; it now reads = (pi()*A1)*3. Each cell in the range has a multiplicative constant of 3 added to the formula. Note that you have the option to specify operations other than multiplication. (Excel throws the extra parentheses to ensure the operation is applied to the entire formula.)
But wait! There’s more!
Select Undo to undo the multiplicative copy. Now go to cell D3 and enter =$D$2 into it. (We want $D$2 so that both the row and the column content of the address are absolute. Cell D2 should still have 3 in it, so now both cells will show a value of 3.) Now select cell D3, copy, and then select cells B1:B20 again. Paste /Special/ Multiply again, and you can see that you now have a multiplicative factor based on the contents of D2 applied to each cell; B1 contains =((PI()*A1))*($D$2). This means you can specify a value in cell D2 which will be applied to the entire range. Instant fudge factor! Changing D2 changes the factor applied. Try it. Enter 4 into D2 and the entire range B1:B20 changes to match. This is a fairly powerful yet easy way to make a large range adjustable after the fact.
Hi GJ,
I knew about your tip up to the ‘but wait! There’s more!’ sentence.
However, I’ve never thought to put a reference in the cell to copy. Brilliant!
Thanks for sharing.
Mynda.
its not working, can u please help me understand well
For me, your #6 tip – using the F4 key to cycle absolute reference ($) is my personal favorite short cut.
But to get a reaction from others that may be looking over your shoulder, I would pick tip #3 – instant chart creation with F11. It usually gets a “wuuut?” look. 🙂
I use CTRL+shift+1 this puts a number in to 2 decimal places with , for thousands. Good list of shortcuts in this post thank you.
Barbara
Cheers, Barbara. I like my numbers formatted with commas. Great tip.
Dang, every time I thought I had one you didn’t list, I kept reading and there it is!
Probably the only thing I use daily that’s not on your list is Ctrl + – (minus) to delete cells. Ctrl + Shift + + (plus) inserts cells, but for some reason I never end up using that shortcut.
Also handy is Shift + Space (select row) and Ctrl + Space (select column). I think I spend my day doing nothing but Shift+Space then Ctrl+-!
Oh, and you can’t forget F11 to get into the VBA IDE! I use that so much that I usually have to think for a bit to remember how to get there through the menus.
🙂 You’ve got some brilliant ones there, Bryan. Thanks!
Thanks Mynda! I realized I made an oops though: should be Alt+F11!
Ctrl+5 to apply Strikethrough! Great for crossing things off of your to-do list.
🙂 Love it. Cheers, Bill.
Hello Mynda,
Thank you for sharing your list.
Jérémy
Cheers, Jeremy 🙂 Actually reading your list reminded me of some others I use too, like F7 for spell check.
CTRL SHIFT L is my recent favorite
It is to apply Auto Fillter
Cheers, MF. I’ve not used that one before 🙂
oh really? Glad that i can contribute to your post. 😛