Need to save time with Microsoft Excel? Whether you are a beginning Excel user or an expert, you will likely discover new shortcuts among these essential Microsoft Excel tips to navigate worksheets, edit cells, and format your key Excel data.
1. Display Formulas
To display worksheet formulas for auditing and printing, press [Ctrl] + ` (called a grave accent or back quote and found on many keyboards to the left of the #1 key). Use this shortcut again to revert back to worksheet results. This option, Show Formulas, is also found under the Formulas tab.
2. Fast Moves Between Worksheets in a Workbook
Try these keyboard shortcuts to toggle or cycle between worksheets:
- Next worksheet: [Ctrl] + [Page Down]
- Previous worksheet: [Ctrl] + [Page Up]
These shortcuts work great in smaller workbooks but how do you quickly move to a specific worksheet especially because Excel supports up to 1,024 worksheets. For another way to easily navigate to a worksheet in an Excel workbook, try one of my favorite tricks: right-click on any of the triangle navigation buttons (located immediately to the left of the worksheet tabs) to display a pop-up menu of worksheet names. Just choose the worksheet you want. Depending on your version of Excel, the option for More Sheets… displays as needed for a complete list of worksheets in the workbook.
3. Fast Data Entry
Want to stay away from the Ribbon and mouse? Save time with these quick and easy keyboard shortcuts to enter data into an Excel worksheet:
- Enter current date: [Ctrl] + ;
- Repeat data from the cell immediately above: [Ctrl] + ‘ (single quote) or press [Ctrl] + D (think duplicate)
- Create a SUM function for cells next to your current location: [Alt] + =
4. Wrapping Text
When entering titles or long paragraphs of text, the [Enter] key will move to a new cell. Instead, press [Alt] + [Enter] to create a line break or new line in the same cell. With this shortcut, you can decide where the text breaks for column headings, text descriptions, and Excel notes and comments.
Not every long text entry needs this specific control, and so, you can anticipate and automatically format lengthy entries to wrap to multiple lines by applying the Wrap Text alignment feature to entire columns or rows even if some of your cells won’t need it. To format a selection in Excel to wrap text, pick the Wrap Text command from the Alignment group in the Home tab.
5. Go To Shortcut
Quickly move to a specific cell or range by pressing [F5] or [Ctrl] + G (Go To).
6. Editing Cells
Excel has lots of choices for editing text, values, or a formula in a worksheet cell including:
- Typing a new entry in the cell (no need to delete previous contents)
- Double-click on cell to open “in-place” or direct editing
- Press [F2] to open the current cell for editing
- Click once into the Formula Bar (above column headings)
- Press [Delete] to clear contents (cell is empty but retains formatting)
When a cell is opened for editing, click with the mouse or use arrow keys, [Backspace], [Delete], [Home] and [End] to navigate within the cell.
7. Keyboard Shortcut to Go To the Formula Bar
The previous Excel tip included the option to edit a cell by pressing the [F2] function key which, by default, positions the cursor in the cell instead of the Formula bar. If you really like keyboard shortcuts over the mouse, you can change the [F2] function key so it moves directly to the Formula bar:
- Pick the File tab and then select Options from the bottom of the screen.
- Select Advanced in the left pane.
- Uncheck the Allow editing directly in cell option. OK to apply.
8. Easy Cell Formatting
Try these quick Excel keyboard shortcuts to change formatting for your current selection:
- Open the Format Cells dialog box: [Ctrl] + 1
- Apply the currency format: [Ctrl] + $
- Apply the percentage format: [Ctrl] + %
9. Quickly See a Sum or Average
Do you want to see a quick total or other calculation? Just highlight some numbers in a worksheet. Next, look at the status bar at the bottom of the window. As part of a feature called AutoCalculate, Excel displays the average of the numbers, a count of the cells, and the sum. The average, count, and sum in the status bar get updated each time you click another cell. By the way, when you use this tip, Excel ignores any cells you click that contain text or graphics instead of numbers. Although you can’t automatically create fixed results from these calculations, this is an easy way to audit or review your Excel worksheets. You can also use this trick with non-adjacent cells. Just click on one cell, and then hold down the [Ctrl] key while you click on additional cells you want to add to the other selected values.
10. Quick Select All
And finally, try these Excel tips to quickly select data in an Excel worksheet. If most or all of your Excel worksheet will have the same format (font style, size, number, etc.), apply global formatting to the cells even before you type text, data, or formulas into the cells. To select all of the cells in the current range or data list, press [Ctrl] + A. Press again to select the entire worksheet (Select All) or by clicking once on the upper-right intersection (the blank rectangle) of Column A and Row 1. Then choose the cell formatting options that you want to see in your worksheet.
Discover more Excel tips and tricks plus download a handout of keyboard shortcuts at TheSoftwarePro.com/Excel.
© Dawn Bjork, MCT, MOSM, CSP®, The Software Pro®
Microsoft Certified Trainer, Productivity Speaker, Certified Speaking Professional
