You know about Copy (CTRL+C) and Paste (CTRL+V), but did you know that almost every menu and operation in Excel can be controlled from the keyboard? Left and right clicks can get a lot done, but knowing your way around the keyboard is often the fastest way to crunch the data in your spreadsheets. Here are the Top 10 keyboard shortcuts in Excel, along with a few extras…
1. Arrow Keys
You’ve probably already figured out that the arrow keys will get you around your spreadsheet cell by cell. But what if you want to move faster?
CTRL+ARROWS will let you leap to the edges of your current data set. If you’re in no-man’s-land, it will find you the edge of the next filled cell.
CTRL+SHIFT+ARROWS will extend your current selection to the edges, and beyond if you tap again. If you need finer control, you can always use SHIFT+ARROWS to adjust your selection cell by cell.
2. Row and Column Select
For a quick select of an entire row or column, you can use these SPACEBAR modifiers.
CTRL+SPACEBAR will select the column of your active cell.
SHIFT+SPACEBAR will select the row of your active cell.
3. Tabbing Between Worksheets
What if you need to get between worksheets?
CTRL+PGUP takes you to the next worksheet on the right.
CTRL+PGDN takes you to the next worksheet on the left.
4. Set and Clear Borders
CTRL+SHIFT+& will add a complete (thin) border around your selected cells.
CTRL+SHIFT+_ (underscore) will remove all the border formatting from your selected cells.
5. Quick-Format Cells
Instead of hunting through the master list of number formats, use these keys to activate the most popular ones.
CTRL+SHIFT+$ will set the Currency format. Example: $1,234.56 / ($1,234.56)
CTRL+SHIFT+! will set the Number format. Example: 1,234.56 / -1,234.56
CTRL+SHIFT+% will set the Percentage format. Example: 123456% / -123456%
CTRL+SHIFT+~ will clear the format, giving you a clean, unformatted cell. Example: 1234.56 / -1234.56
6. Formula Edit Mode and Reference Lookup
Sometimes you need to check in on a formula to make sure it is doing what you intended. With a cell selected, the formula box will show you the contents, but it won’t give you that helpful color context you get when you first enter the formula.
F2 will display the formula in the cell, and it will also helpfully highlight and color-code all the references.
7. Lock and Unlock Cell References
You probably know that putting $ signs before your Column letter and Row number will lock the cell reference in a formula, but a pain to go in and manually set absolute (locked) references.
F4 will cycle through all the combinations of locked and unlocked references when you are editing a formula in a cell.
8. One More Time!
Sometimes, when you’re inserting rows or formatting a cell, you need to do it multiple times in a row. This will save you a bit of time…
F4 or CTRL+Y will repeat your last action.
9. Paste Special
Whether you need to transpose selection or paste only formatting, the Paste Special menu is your best friend. It’s also a pain to get to unless you know this key combo.
CTRL+ALT+V will bring up the Paste Special dialog box.
10. Finding More Shortcuts – The Easy Way!
These shortcuts are a great way to save time and speed your way around Excel, but what if you’re trying to do something that’s not listed? Almost everything that Excel can do is possible through keyboard shortcuts. This is the secret to finding them.
ALT will bring up Key Tips, which will show you the keyboard shortcuts for the menu system.
That way, you’ll know that pressing ALT+H+F+P activates the Format Painter. And pressing ALT+N+C opens up the Column Chart window. The possibilities are endless!