Dawn Bjork Buzbee
The Software Pro®
Microsoft Certified Trainer (MCT)
Certified Microsoft Office 2010 Specialist (MOS 2010) Master Instructor
Certified Microsoft Office 2007 Specialist (MOS 2007) Master Instructor
Microsoft Certified Application Specialist (MCAS) Instructor
Certified Microsoft Office Expert
Certified Women's Business Enterprise (WBE)
WOSB (Women-Owned Small Business) Certified
>>Back to Articles Home
Whether you are a beginning Excel user or an
expert, you will likely discover something new among these essential
Microsoft Excel tips.
1. Display Formulas
display worksheet formulas for auditing and printing, press [Ctrl] + ~
(called a tilde and found on many keyboards to the left of the #1 key). Use this
shortcut again to revert back to worksheet results. In Excel 2010 and Excel
2007, this option, Show Formulas, is 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 now that Excel 2010 & 2007 support up to
1,024 worksheets, an increase from the limit of 255 worksheets in Excel 2003?
For another way to easily navigate to a worksheet in an Excel workbook,
right-click on any of the 4 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. The option for More Sheets… displays
as needed for a complete list of worksheets in the workbook.
3. Fast Data Entry
Save time with these quick and easy ways to enter data into an Excel
- Enter current date: [Ctrl] + ;
- Repeat data from the cell immediately above: [Ctrl] + ' (single
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 2010 or 2007
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
- 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. Just follow the steps below for your version of Microsoft Excel:
- Excel 2010: Pick the File tab and then select Options
from the bottom of the screen.
Excel 2007: Click the Office button and pick Excel Options
(at the bottom right).
- 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
- 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
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
If most or all of your Excel worksheet will have the same format (font style,
size, number, etc.), you can 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.
© Dawn Bjork Buzbee, MCT, The Software Pro®
Dawn Bjork Buzbee
is The Software Pro®
and a Microsoft Certified Trainer (MCT) as well as a certified Microsoft Office
Specialist (MOS) Master Instructor, certified Microsoft Applications Specialist
(MCAS) Instructor, and a certified Microsoft Office expert. Dawn shares smart
and easy ways to effectively use software through her work as a software
speaker, trainer, consultant, and author of 8 books.
This article and
more can be reprinted at no charge in your publications and website with
copyright and attribution.
more about how easy it is to share these valuable tips, tricks, and techniques.
More Microsoft Excel Tips, Tricks & Handouts Too!
More Smart & Easy Software Tips, Tricks &
Techniques for You
<<Back to Articles
Be an Excel Expert!
How effectively are you using Excel to create, format,
and analyze your important data?
Learn the shortcuts and secrets you need to quickly and
easily save time and money. Discover the Excel Tips,
Tricks, and Techniques Everyone Should Know!
Click Here for More Valuable Excel Tips!
Hold [Ctrl] and click to
select multiple worksheets for formatting a group.
|Switch to the
Page Layout view (View > Page Layout) in Excel 2010 and
Excel 2007 to enter a header or footer directly into your worksheet.