Top 21 Excel shortcuts
October 27, 2009 by Sam NarisiPosted in: HR Tech, In this week's e-newsletter - Tech, Latest News & Views
Few people enjoy working in Microsoft Excel. Unfortunately, HR folks often have to.
To make it easier on yourself, here are keyboard shortcuts for 21 common commands:
CTRL — Shift & Outline border to the selected cells.
CTRL Shift # — Change Date format with day, month and year.
CTRL Shift @ — Change Time format with hour and minute, AM or PM.
CTRL Shift : – For the current time.
CTRL ; — For current date.
CTRL 9 — Hide selected rows.
CTRL 0 — Hide selected columns.
CTRL ` — Display Formulas/Alternate in the worksheet.
CTRL+K — To insert Hyperlink.
CTRL+H — Opens up the Find and Replace dialog box.
CTRL+R — Uses the Fill Right command to copy the contents and format of the leftmost cell of a selected range into the cells to the right.
CTRL+7 — Hides or displays the standard toolbar.
ALT+F1 — This creates a chart out of the data in the current range (selected cells). This is the most well-known feature of Excel, and now you know a new way to use it!
ALT+SHIFT+F1 — Inserts a new worksheet into a workbook.
F6 — Switches between the worksheet, Ribbon, task pane, and Zoom controls. This is probably the most useful shortcut of all. This cool shortcut is also the most beloved to all the anti-mouse users out there.
SHIFT+F6 – The reverse of the F6 shortcut.
CTRL+F6 – Switches to the next workbook window when multiple workbook windows are open.
CTRL+SHIFT+$ – Applies the currency format to the selected cells.
CTRL+`– Alternates between showing the value of a cell and the formula in the worksheet. Great for complex worksheets.
CTRL+D – Fill down command on the selected cells. Fill down copies the content and format of the topmost cell into the cells below.
CTRL+R – Fill right command on the selected cells. Fill right copies the content and format of the leftmost cell to the cells to the right.
Tags: Excel, Microsoft, shortcuts, spreadhseets
October 20th, 2009 at 1:31 pm
There are only 20 shortcuts here. CTRL + R is listed twice.
October 20th, 2009 at 1:43 pm
That’s because they used a ’shortcut’ and didn’t bother to proofread the work!!
October 20th, 2009 at 2:09 pm
Go Lynn. Maybe those people who can’t count are the ones who don’t enjoy working in Excel.
I beg to differ with the opening line. I LOVE Excel. It is the greatest program EVER. My favorite part of my job is when I can enter my payroll data into my spreadsheets, or when the CFO asks for a special project analyzing salaries or something.
Why would you want to memorize all those shortcuts when you can just grab the mouse and point and click on the icon that does these things? You are making an easy task difficult.
Rather than memorizing a bunch of commands, just customize your toolbar to contain the tasks you use most often. Click on Tools, Customize, go to the Commands tab, and just scroll thru the commands under each category. You can drag the commands you need up into your toolbar, and put them in the order that is most convenient for you. You can also drag unwanted/unused commands FROM the toolbar INTO the customize box to remove them, and make room for the ones you need.
I use Excel for everything. Lists, signs, resumes, even letters sometimes. It is SO much easier to manipulate than Word.
DO NOT slander my Excel!!!
October 20th, 2009 at 2:30 pm
Many would also be amazed at the options that pop up when you “right-click.”
October 20th, 2009 at 2:47 pm
Ditto to S Allen – I love it….when I have formulas set up and I can watch the magic happen by entering a number it just makes my heart go “a-twitter”
October 20th, 2009 at 3:04 pm
One more thing – in your “tags” spreadsheet is spelled wrong.
October 20th, 2009 at 3:33 pm
Don’t forget the “magic” of macros! I hit a button and walk away while the report formats itself like MAGIC!
October 20th, 2009 at 4:05 pm
I like Excel except for the ridiculous return of “2+2″ when you enter 2+2 instead of returning “4.” I assume that the late-great Lotus 1-2-3 must have obtained a copyright on the latter and MicroSoft couldn’t program the logical thing.
October 20th, 2009 at 4:16 pm
ctrl ` is also on there twice. Down to 19…
October 20th, 2009 at 4:56 pm
15 years ago, this might have been useful.
October 20th, 2009 at 6:47 pm
pssst Chuck…… if you put an “=” before the “2+2″….you’ll get what you’re looking for
type it in like this: “=2+2″ hit enter and see what happens!
October 21st, 2009 at 8:28 am
Jim O.
That’s my complaint. You have to type a totally unnecessary + or =.
October 21st, 2009 at 8:31 am
Chuck, I think you must not write very complicated formulas or this wouldn’t be an issue for you. Welcome to the 21st century.
October 21st, 2009 at 12:37 pm
Thanks for the list.
I love Excel, and as an engineer, use it everyday!
So there are a couple duplicates – seems everyone wants to be an editor (refer to the previous column about the 10 annoying habits people demonstrate at work).
Here are some shortcuts (and hints) I use (besides the obvious copy/paste, etc.)
CTRL + TAB (Shifts between open files)
CTRL + Z (undo)/CTRL + Y (redo) (You can use it to track/verify changes, etc. when modifying files)
ALT + ENTER – allows you to insert a “hard return” inside a cell – very useful when you want to include bullet points, or notes, without all the hassle.
-Add “Select visible cells only” to the toolbar (find the icon in the Edit part of the Customize toolbar), works great with AutoFilter to extract only the data you want to work with.
-Use ‘ (apostrophe) at the beginning of whatever you enter in a cell – Excel treats the information as TEXT, even when numbers or “-” are used (think easy bullet points)
-Use macros to automate reduntant tasks, or compiling data into charts, etc – this turns a 5 minute job into a 2 second job… (such as adding a new employee to a sheet, or compiling overtime, or days lost, or keeping a history of a large group of numerical data).
p.s. I have to agree with Chuck to some degree – why can’t Excel read my mind when I want to put in a formula? Also, it is a bit annoying to have to type in “= AVERAGE”, instead of “AVG”, and “=IF” when “if” should work.
October 22nd, 2009 at 6:56 am
Lynn,
You must not build any large spreadsheets where one has to combine several numbers or formulas in each cell. 100 cells = 100 extra keystrokes that should not be necessary. It is as if (as Doug says) it is automatically putting an apostrophe first in every cell when obviously that is not called for with numbers unless you want them as text, which is not usually the case.
October 27th, 2009 at 10:53 am
I have to agree with Chuck and Doug…I came from the Lotus/WordPerfect era when 2+2 actually returned 4. Now having to type “=sum” and “@if”, etc. becomes very cumbersome. Excel is a great program but I’m afraid Lotus had it right; much more user friendly. Lotus knew if you typed “12345″ that is what you wanted in your cell and didn’t return “#NAME”. I guess it’s all in what you get used to.
October 27th, 2009 at 2:04 pm
After all the comments above, you printed/listed this again? REALLY?
October 27th, 2009 at 4:03 pm
Don’t forget the Pivot Table! It allows me to run all kinds of quick summaries of data to the amazement of colleagues that are still intimidated by the variety of tasks that Excel can automatically do (if you know the magic key combinations).
October 27th, 2009 at 6:09 pm
Thanks for the laughs. I don’t know many HR folks who have time during the day to read these!
November 6th, 2009 at 10:58 am
I started out with handwritten spreadsheets and was thrilled when I first got Visicalc. I used all the Lotus versions from 1.0 up to 5.0. I usually pushed the program to its limit and found while each new version was a big improvement I also often lost something.
Both Lotus and Excel are great products and have made a huge difference in my career.
I found Lotus a better all around product except for data import and the pivot tables. The Lotus Macros were much easier to use than Excels although I think Excels may be more powerful.
S Allen – You have no idea how much slower the mouse is compared to keyboard commands. I could print much faster hitting “/ppag” than taking my hands off the keyboard, finding the mouse, finding the icon and clicking.
The graphical interface (Icons and Mouse) “dumbs down” both products making them slower but easier to use for the average user. Those of us who are/were considered “power users” whine a lot when upgrades seem to go backwards in our eyes.
Thanks for the Key strokes.
November 10th, 2009 at 5:02 pm
My favorites are:
F11 – it automakes a chart to a new tab
Alt-Enter that Doug mentioned
CTRL PG UP/DN to toggle worksheets.
I just wish I could find one to quickly copy one worksheet into many. The one spot F4 doesn’t work.
November 16th, 2009 at 3:28 pm
This has all been very informative. I too use excel and have amazed myself at what I have done with a few basic some formulas. Do you all remember the DOS days? I miss them sometimes… can of worms?
November 18th, 2009 at 12:19 pm
Chuck, it’s much simpler to simply put the + sign in front of the first number instead of an = sign. That way it is all done with the 10 key section. But the point he was making was that Lotus was much better because it anticipated the + sign and didn’t make the extra keystroke necessary. Lotus had much more flexibility and the macros were much easier to use. Having to enter and = sign which is not on the 10 key pad in entering numbers with secondary calculations is a major flaw in Excel, and very inefficient.
And you can still use the @sum and @if, etc commands from lotus instead of = sum etc.
November 19th, 2009 at 3:05 pm
Don’t you love that excel allows you to easily type (without the ‘) 0123 (example, account numbers, SSN, etc.)?
Excel can be fun, but the programmers could make it a little more fun / improvements are needed.