Monday, March 24, 2014

20 Excel Shortcuts You Need to Know. Number 15 is My Favorite.

Excel is a powerful tool, but you can’t call yourself a power user until you've mastered the essential keyboard shortcuts. I've mostly avoided the obvious and essential shortcuts that also work in other apps (such as Ctrl+Z for undo and Ctrl+C for copy, Ctrl+B for bold, etc.) but besides those here are the 20 Excel shortcuts you need to know:

1. F1: Access the Excel help file
Press F1 to access the Excel help file. Excel has hundreds of keyboard shortcuts so one of the most useful features of the help file is to search for ‘keyboard shortcuts.’ You'll find the full list of shortcuts there but the 20 listed here are the ones you’ll keep returning to.


2. Ctrl+`: Show or hide formulas
Not sure which formulas are running in your spreadsheet? Use Ctrl+` (the accent key, to the left of the number 1 key) to see the formulas in the cells rather than their results. 
 


3. Alt: Access the ribbon
Every single Ribbon command in Excel can be accessed via the keyboard. Hit Alt and you’ll see a letter (or a two-letter combination) above each ribbon tab. Type that letter or combo to use it. I've added the camera to the ribbon to quickly take screenshots, using Alt+4, as shown below:



4. Ctrl+;: Enter the current date
Using Ctrl+; saves time checking and entering the date. I find myself using this a lot. Please note this is a fixed date and not the =TODAY() function.

5. Ctrl+PgUp/PgDn: Navigate between worksheets
Complex Excel spreadsheets often have multiple worksheets. Rather than clicking on the bottom-of-screen tabs, use Ctrl+PgUp and Ctrl+PgDn to quickly navigate between sheets.

6. Ctrl/Shift+Space: Select an entire row or column
For even more selection power, Ctrl+Space selects an entire column. Shift+Space selects an entire row. You can then use the shift keys plus the arrow keys as appropriate to select additional rows or columns. Remember, C=Column=Crtl.

7. Alt+ =: Sum function
Here is the fastest way to sum your data in Excel: after entering your data in the column, click the first empty cell in that column and enter ALT+= (equals key), then click Enter. It will add up the numbers in all cells above.

8. CRTL+ UP/DOWN: Jump to top or bottom
This tip is particularly useful when you’re dealing with large number of rows. Use this method instead of endless scrolling to save time. Enter CTRL + ↑ (upward arrow key) to jump to the top cell or CTRL +↓(downward arrow key) to jump to the last cell before an empty cell.

9. Alt+Enter: Multiple lines in one cell (line break)
In some cases you may want multiple lines of data or text you typed into a cell to appear on several lines (also called inserting a line break). Instead of entering the text in another cell, press ALT+ENTER. That way you'll start a new line while typing or editing data.

10. CRTL+0/9: Hide columns or rows
To quickly hide a row / rows use CTRL+9. To hide a column / columns use CTRL+0. 

11. F6: Switch between tools
For all those anti-mouse users out there, F6 is the ultimate shortcut. It allows you to switch between the worksheet, the ribbon, task pane, and zoom controls.

12. CRTL + ‘: Copy cell above selected 
If you type Ctrl+' it looks at the cell above the selected cell and copies it into the current cell. 

13. ESC: Cancel changes
Press ESC while you are editing the text or formula in a cell to exit the cell and cancel any changes that you may have made.

14. Shift + F3 : List of functions
Not sure if you need to use a SUMIF or COUNTIF? You know there’s probably a function for what you need to do but can’t remember the name? Open the list of available functions using Shift+F3. 

 


15. Crtl+g; Create bookmarks and other
Use Ctrl+g > Special to do things like Select all cells with comments, Select all cells containing formulas, etc. You can also create a bookmark within a large spreadsheet by naming a cell. Then use CTRL+g to quickly navigate to that cell.

 

 



16. CRTL+1: Format cells
Do you constantly find yourself formatting a cell by changing the font border and fill? Use CRTl+1 to display the Format Cells dialog box. 
 


17. F4: Toggle a reference
Use F4 to toggle a reference between A1, $A$1, A$1 and $A1 while editing a formula (When NOT editing a formula, F4 is an alternative to Ctrl+y which is "repeat" or "redo").
 



18. CRTL+ALT+SHIFT+F9: Update formulas
CTRL+ALT+SHIFT+F9 rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated. Very useful when you have user defined functions. 

19. CTRL+SHIFT+~: Change date to number format
When Excel automatically formats a number as a date, you can change it back to a number using the shortcut CTRL+SHIFT+"~" (or change the date format using CRTL+SHIFT+#).

20. Alt + F11: Open macro editor
One of my personal favorite and most used Excel shortcuts is ALT+F11 to open the macro editor. Some of my most used macros are how to create folders from Excel and combine multiple Excel files.

Here's a quick summary of all the shortcuts that you can print off or Pin for quick reference later:



Learning to use keyboard shortcuts is one of the best ways to increase your productivity with Microsoft Excel. Are there any Excel shortcuts you often use that I’ve failed to list here? Please let me know!

1 comment:

  1. Is it possible to have an INDIRECT formula that can also increment (+1) with respect to the row range being referenced? In the ex below, I'd like for E277:Z277 to become E278:Z278, E279:Z279, etc., when I drag down.

    =SUMPRODUCT(COUNTIF(INDIRECT("'"&NSS_UnR_Sht_Names&"'!E277:Z277"),"alert"))

    ReplyDelete

I'd love to hear from you!
-Nick