I know many seasoned professionals that still despise all keyboard shortcuts. Some will never be convinced, but for those on the fence I want to share with you some of the keyboard shortcuts that I find most useful in Excel.
CTRL+1 This is the same as using the menu Format > Cells... It brings up the format cells dialog box. Just select a cell or range and a quick shortcut brings up this useful formatting tool. While many of the formatting tools of this dialog box are available as tool buttons, there are some reasons that you are better served by going directly to this box. Here are two:
- Sometimes columns with headers or multiple formatting types will misbehave. Have you ever had a column of numbers that you wanted to decrease the number of decimal places shown by one? Most of the time, you can use the 'decrease decimal' tool button. But when your data contains more than one type, multiple decimal settings, or some types of headers, this will not work. Occasionally, you can click 'increase decimal' once or twice until all are the same and follow it with decreasing until you get what you want. Using the Format Cells dialog box will work every time - the first time.
- When you have a column of numbers that are formatted as 'general' they will not display comma separated 1000's. If you use the comma toolbutton then you get more than you bargained for - you get two decimal places as well. So often you find yourself using the comma, then two 'decrease decimals' to get what you wanted. As an alternative use CTRL+1, change the type to number, drop the decimals to 0, and check the 'use 1000s separator' box. Now the more astute may have counted and are now saying "But that means more steps." Yes, but as mentioned above, this will always work and avoids the buggy problems you may otherwise encounter.
CTRL+SHIFT+; Enters the current time. As with the date stamp, formatting will ensure that you get what you want. Perfect for timesheets.
CTRL+SHIFT+" copies the information from the cell above with formatting. This is one of the most appropriate shortcuts ever - the ditto mark. Not including SHIFT will copy the information without formatting which for dates will return a 5 digit number.
F2 - is the universal edit key. This is useful particularly if you are moving through a spreadsheet using the arrow keys instead of the mouse and then you need to edit a value in a cell. Where you would normally click in the cell with the mouse, you can simply hit F2 and make your change. If this is news to you then you are in for a treat. Since this is a windows shortcut not exclusive to Excel, it works in all kinds of places. Looking at files in Windows explorer? Select a file and hit F2 to rename it.
CTRL+Home moves to the top of the worksheet. This can be very helpful when you are examining row 57,345 and need to scroll up to the top.
CTRL+ARROWDOWN selects the first blank cell in a column. This works whether there are 10 cells in the column or 10,000. Remember back before you knew this shortcut and you would scroll 3,000 rows past where you intended? Well, Office 2007 solved that problem, but this shortcut is still faster and more reliable. Although statistically most data is stored in columns, the same concept works for rows. Just use ARROW RIGHT or LEFT.
CTRL+SHIFT+ARROWDOWN similar to above but this shortcut is much more useful. It will select all of the cells between the current active cell and the first blank.
CTRL+SPACEBAR selects an entire column. Now it is as simple as CTRL+SPACEBAR, CTRL+C, CTRL+V. Who knew you could copy and paste a column so easily?
CTRL+B,I,U These are pretty basic and have been around since the beginning but as I was bolding the previous shortcut I decided I would be remiss if I didn't mention it. Use B for Bold, I for italics, and U for underline. If you have something selected then that will be changed. If nothing is selected, then the formatting you selected will be toggled on.
OK, there are lots more but if you memorize and master each of these you'll have a great foundation. Just two more that are ,again, Windows shortcuts which you can use in any program.
CTRL+TAB this will cycle between tabs. In Excel this will cycle between SHEET1, 2, and 3.
And finally, ALT+TAB. This goes back to the beginning of Windows. It toggles between the active program and the previously active window. Entering information in Excel, but need to verify something in another program? ALT+TAB to switch to the last program, then ALT+TAB to switch back. Need to switch to a different program? Keep ALT held down and all open programs will be available. Hit TAB as many times as necessary to select the program you want.
Comments