12 Excel Keyboard Shortcuts for Every User
Microsoft Excel, Microsoft Office
Keyboard shortcuts are a great way to improve the speed at which documents are built, regardless of the application. It seems like there is a keyboard shortcut for just about every feature Excel contains; and there may be that one guru in the office that knows them all. But most of us fall somewhere between Guru and Labrador retriever (hopefully, closer to the former.)
The good news is that it’s not an “all or nothing” proposition when it comes to keyboard shortcuts. Knowing just a few of the most productive keyboard shortcuts will serve you far better than knowing none at all.
So let’s get this show on the road!
- CTRL+SHIFT+L – Turn On/Off Filter Controls
Filters are of tremendous use when analyzing large numbers of records in a table, but you are only interested in a select set of records that met a specific criteria. Activating your filters is just a CTRL-SHIFT-L away. This keyboard can also be used to turn off all of the filters and display the entire list. (Filters are on by default when you convert a straight table to a Data Table, and not always desired.) Finally, if you hit the “L” key twice (CTRL-SHIFT-L & L) you can effectively clear the current filters to start fresh with a new filter query.
- CTRL+1 – Format Cells
Formatting the look of data is something you can never get away from. Everybody likes a pretty spreadsheet, both designer and view alike. Highlighting a cell, or range of cells, and then Pressing CTRL+1 will take you immediately to the Format Cells dialog box. Go ahead; give it a try. You’ll never get there using your old ways again.
- CTRL+T – Insert a Data Table or Convert to a Data Table
One of the greatest additions to Excel came in version 2007 with the introduction of Data Tables. The power and automation present in Data Tables makes them one of the most used and indispensable tools in the Excel arsenal.
To convert a list of information to a Data Table, highlight the range of data and press CTRL+T. From that point forward, you will have access to the Table Tools – Design ribbon that contains all of the tools used for managing both logic and artistry of the table.
- CTRL-SHIFT-1, 2, 3, 4, 5, and 6 – Number Formats
Using the CTRL and SHIFT keys wile pressing the numbers 1 through 6 will give you access to the following number formats:
1 Comma style (!)
2 Time style (@)
3 Short date style (#)
4 Currency style ($)
5 Percent style (%)
6 Scientific notation style (^)
Luckily, some of the symbols represent their formats; the rest, you just have to memorize. It won’t take that long, I promise.
- ALT-= – Instant AutoSUM Function
This is one of my personal favorites. If you click directly below a column of numbers (or directly to the right) and press ALT-=, you will be presented with an instant SUM function just waiting for you to accept the proposed cell selection.
- F2 – Edit a cell
It just doesn’t get any simper than this. Pressing the F2 key places you in edit mode where you can fix errors in formulas, edit text, and even apply color to text (try that one out; you probably never knew about that one. We’ll consider that a “tip within a tip”.)
- F3 – Display Named Ranges
Naming cells or ranges of cells is a great way to turn ugly formulas into elegant works of art (perhaps I exaggerate a tad). Trying to remember all of the named ranges you’ve created is where the trouble creeps in. While editing a formula, press the F3 key and a list of all of your named ranges is presented. From here you can select the name without typing, which also reduces needless typos.
- SHIFT-ALT-RIGHT ARROW or LEFT ARROW – Create or Remove Groups
Groups (sometimes known as “outlines”) aren’t a feature that when you have that list of related data, turning it into an outline really helps increase readability as well as providing a means of hiding lower-level information and creating summaries.
- SHIFT-8
While we’re speaking of Outlines, this one is a real “Black Ops” kind of shortcut. When you have an outline created and you have pressed the myriad of plus and minus buttons to create that “perfect look” that tells the story just the way you want, the last thing you want is someone changing the story. Or maybe you just want to temporarily hide the outline controls so you can see more of your data during the analysis phase. Pressing CTRL-8 hides the outline controls without removing their functionality like a “Clear Outline” would have done.
- ALT-F11 – Launch the Visual Basic Editor
If you write macros to help automate operations in Excel, the Visual Basic Editor is one of your best friends. There are several ways to launch this editor, but ALT-F11 is probably the fastest.
- CTRL-UP, DOWN, LEFT, or RIGHT – Navigate to the Edges of Data
When you need to get to the last row or column (or back to the first), pressing the CTRL key in conjunction with one of the four arrow keys will get you there fast as lightning. Keep in mind, if you have blank rows or columns in your data, this will stop at those blank points. Therefore, you may need to press the arrow key multiple times to reach your final destination.
- F9 – Partial Formula Evaluation
If you have ever been curious as to what just a portion of a formula is returning, try this one on for size. Enter Edit Mode (remember F2?) and then highlight the portion of the formula that you’re interested in. Press the F9 key and you will change the question to the answer. BEWARE: this will actually change the formula, which would be bad in most cases because you probably don’t want to hardcode a static answer in your dynamic formula. Remember to either hit ESC or press CTRL-Z to remove the evaluated answer and restore the original formula.
Speaking of CTRL-Z, you should use that one EVERYWHERE; not just in Excel. It’s the fastest way to invoke the UNDO command; and who doesn’t hit UNDO at least 500 times per day?
Want to learn more about Excel? See our Microsoft Excel Training Classes.