O'Reilly logo

Excel 2007 Pocket Guide, 2nd Edition by Curtis D. Frye

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 4. Excel Reference

This chapter provides reference information that is hidden within Excel, including:

  • Useful Excel commands that are not on any toolbar or menu by default (Table 4-1)

  • Native data and graphic file formats (Table 4-2 and Table 4-3)

  • Startup switches to control how Excel launches (Table 4-4)

  • Wildcard characters used in Excel searches and filters (Table 4-5)

  • Default locations of important files and folders (Table 4-6)

This chapter also lists default keyboard shortcuts for the following types of tasks:

Command Reference

There are hundreds of commands available in Excel 2007; there wasn't room for them all on the Ribbon. Table 4-1 lists some of the useful commands that you can find in the "Commands Not in the Ribbon" list in the Excel Options dialog box's Customize page. (To display that list, click Office Button → Excel Options → Customize. Click the "Choose commands from" down arrow, and click "Commands Not in the Ribbon".) You can use the techniques in Chapter 2 to make these commands available. You can find any others in Help.

Table 4-1. Useful Excel commands

Command

Action

Suggested uses

Publish As Web Page

Displays the Publish dialog box accessed from the Save As Web Page dialog box.

Add button to the Quick Access Toolbar to facilitate publishing worksheets to the Web.

Web Page Preview

Displays the worksheet as if it were a web page.

Add button to the Quick Access Toolbar to facilitate publishing worksheets to the Web.

Select Visible Cells

Selects only the visible cells (those not hidden by a filter).

Add button to the Quick Access Toolbar to avoid selecting hidden cells.

Constrain Numeric

Requires users to enter numbers into the selected cells.

Add to the Quick Access Toolbar when setting data validation rules.

Cycle Font Color

Change cell text to the next color in the Font Color palette.

Add to the Quick Access Toolbar.

Scenario

Displays a list of scenarios available for the active worksheet.

Add list box to the Quick Access Toolbar for use in presentations.

Lighting

Changes the lighting sources and characteristics for objects on a worksheet.

Add button to the Quick Access Toolbar when you edit drawing objects.

Calculator

Displays the Windows calculator for quick calculations.

Add button to the Quick Access Toolbar for calculations that don't involve data in the worksheet.

Close All

Closes all open workbooks but does not exit Excel.

Add button to the Quick Access Toolbar if you work with lots of workbooks at a time.

New

Creates a new workbook without displaying the New Workbook dialog box.

Add button to the Quick Access Toolbar to create new workbooks quickly.

Native Formats

Excel 2007 supports many common structured data and graphic formats without the need to filter the files. Table 4-2 lists all of the structured data formats Excel understands without the need for conversion, and Table 4-3 displays the native graphics formats. If you can't find a filter for the file format you want to use in Excel, look on the Office web site, the Office installation disk, and the program manufacturer's web site to see if a filter is available.

Table 4-2. Native file formats

Format

File extension

Microsoft Excel 2007 Workbook

.xlsx

Microsoft Excel 2007 Macro-enabled Workbook

.xlsm

Microsoft Excel 2007 Template

.xltx

Microsoft Excel 2007 Macro-enabled Template

.xltm

Microsoft Excel 2007 Binary Workbook

.xlb

Microsoft Excel 97–2003 Workbook

.xls

Microsoft Excel 5.0/95 Workbook

.xls

Microsoft Excel 97–2000 & 5.0/95 Workbook

.xls

Microsoft Excel 4.0/3.0/2.1 Worksheet

.xls

Microsoft Excel 4.0 Workbook

.xlw

Microsoft Excel 97–2003 Template

.xlt

XML Data

.xml

XML Spreadsheet

.xml

Web Page

.mht

Web Archive

.htm

Text Files

.txt

Comma Separated Values

.csv

Data Interchange Format

.dif

Symbolic Link Format

.slk

Table 4-3. Native image file formats

Format

File extension

Graphics Interchange Format

.gif

Joint Photographic Experts Group

.jpg

.jpeg

Portable Network Graphics

.png

Windows bitmap

.bmp

Tagged Image File Format

.tiff

Windows Enhanced Metafile

.emf

Windows Metafile

.wmf

Vector Markup Language

.vml

Microsoft Windows Media

.avi, .asf, .asx, .rmi, .wma, .wax, .wav

Startup Switches

As with most programs, you can start Excel from the command line (Start → Programs → Accessories → Command Prompt). At the command prompt, type excel.exe to run Excel just as you would if you ran the program by selecting it from the Programs menu. When you run Excel from the command prompt, however, you have the option of adding startup switches that change how the program opens.

Table 4-4 lists the startup switches available to you for Excel (e.g., excel.exe /e).

Table 4-4. Startup switches

Startup switch

Description

workbook path/file name

Open a specific workbook.

/r workbook path/file name

Open a specific workbook as a read-only file.

/e

Prevent display of the Excel startup screen and a new blank workbook.

/p folder path/folder name

Specify the working folder.

/s

Start Excel in Office Safe Mode, which runs the main program with no add-ins or templates.

/a progID

Starts Excel and loads the named add-in.

/t workbook path/file name

Starts Excel and loads the named file as a template.

Wildcards in Filters and Searches

When you need to find particular kinds of values in your worksheets, you can use the "Find and Replace" dialog box (opened to the Find tab page by clicking Home → Find & Select → Find, or to the Replace tab page by clicking Home → Find & Select → Replace) or filters. Excel extends your search power by letting you use wildcards, which are characters that can take on multiple values (for example, any letter or any character). Table 4-5 lists the wildcard characters you can use in Excel searches and filters.

Table 4-5. Excel wildcard characters

Character

Description

?

Any single character (e.g., "fr?e" finds "frye" and "free").

*

Any group of characters (e.g., "f*" finds "frye", "fair", and "foul").

~ followed by ?, *, or ~ finds a question mark, asterisk, or tilde

"Frye~?" finds "Frye?".

Default File Locations

Table 4-6 lists the locations of important files and folders for Excel 2007. You can change some of the locations; others you can't. (You may have configured your Documents folder differently or installed Office in an unusual location. This table lists the defaults.)

Table 4-6. Default file locations

File or location

Operating system

Path

User-definable

Document Storage

Windows Vista

C:\Documents and Settings\<username>\Documents

Yes

Windows XP

C:\Documents and Settings\<username>\My Documents

Yes

User Templates

Windows XP and Vista

C:\Documents and Settings\<username>\Application Data\Microsoft\Templates

Yes

AutoRecover Files

Windows XP and Vista

C:\Documents and Settings\<username>\Application Data\Microsoft\Excel

Yes

Startup directory

Windows XP and Vista

C:\Documents and Settings\<username>\Application Data\Microsoft\Excel\XLSTART

Yes

Program Files

Windows XP and Vista

C:\Program Files\Microsoft Office\Office12

No

History of recently opened documents

Windows XP and Vista

C:\Documents and Settings\<username>\Application Data\Microsoft\Office\Recent

No

Keyboard Shortcuts

Excel has literally hundreds of predefined key combinations that let users who prefer to use the keyboard perform a wide variety of tasks. The most common shortcuts—Ctrl-S to save a workbook, and Ctrl-P to print—are there, as are many others.

The following tables are grouped to make finding shortcuts easier. Each group concentrates on a related set of activities, such as navigating a workbook, entering data, or selecting and editing data.

Note

If you define custom keyboard shortcuts using any of the listed combinations, your custom shortcut will override the default shortcut.

Table 4-7. General program shortcuts

Key

Action

Ctrl-N

Create a new workbook.

Ctrl-O or Ctrl-F12

Open a workbook.

Ctrl-S or Shift-F12

Save a workbook (the Save As dialog box appears if this is the first time you're saving the workbook).

F12

Open the Save As dialog box to specify the name and location of the workbook.

Ctrl-W or Alt-F4

Close the active workbook. If it is the only open workbook, close Excel as well.

F1

Open Help, display the Microsoft Excel Help task pane, or open the Office Assistant.

F7

Run the Spelling checker.

F10

Display keyboard shortcuts for the visible Ribbon commands.

Shift-F10

Open a shortcut menu.

Ctrl-F9

Minimize the workbook.

Ctrl-F10

Restore or maximize the workbook.

Ctrl-P or Ctrl-Shift-F12

Open the Print dialog box.

Alt-Tab

Switch to the next program.

Alt-Shift-Tab

Switch to the previous program.

Ctrl-Esc

Open the Windows Start menu.

Prtscn

Copy a picture of the screen to the clipboard.

Alt-Prtscn

Copy a picture of the active window to the clipboard.

Table 4-8. Navigate a worksheet

Up arrow

Move the active cell up one row.

Down arrow

Move the active cell down one row.

Left arrow

Move the active cell left one column.

Right arrow

Move the active cell right one column.

Home

Move to the beginning of the current row.

Ctrl-Home

Move to the beginning of the worksheet (usually cell A1).

Ctrl-End

Move the last cell in the worksheet (at the intersection of the last used row and last used column).

Page Up

Scroll up one screen.

Page Down

Scroll down one screen.

Alt-Page Up

Scroll right one screen.

Alt-Page Down

Scroll left one screen.

Ctrl-Page Up

Move to the previous worksheet in the workbook.

Ctrl-Page Down

Move to the next worksheet in the workbook.

Shift-F11

Insert a new worksheet.

Ctrl-Shift-Page Down

Select the current and next sheet.

Ctrl-Shift-Page Up

Select the current and previous sheet.

Ctrl-F6

Go to the next open workbook.

Ctrl-Shift-F6

Go to the previously viewed open workbook.

F6

Move between the split panes of a workbook.

Shift-F6

Move back to the previously viewed pane of a split workbook.

Ctrl-G

Open the GoTo dialog box.

Ctrl-F

Open the Find page of the "Find and Replace" dialog box.

Ctrl-H

Open the Replace page of the "Find and Replace" dialog box.

Ctrl-Alt-Right arrow

Move clockwise between nonadjacent selections.

Ctrl-Alt-Left arrow

Move counterclockwise between nonadjacent selections.

Table 4-9. Selecting data and cells

Key

Action

Ctrl-C

Copy a cell's contents or the selection on the formula bar.

Ctrl-X

Cut a cell's contents or the selection on the formula bar.

Ctrl-V

Paste the contents of the clipboard into the cell or onto the formula bar.

Ctrl-Space

Select the entire column.

Shift-Space

Select the entire row.

Ctrl-A

Select the entire worksheet.

Shift-Backspace

With multiple cells selected, select only the active cell.

Ctrl-Shift-Space

With an object selected, select all objects on a sheet. With a cell selected, select all cells.

Ctrl-6

Alternate between hiding objects, displaying objects, and displaying placeholders for objects.

Ctrl-Shift-8 or Ctrl-*

Select the current region around the active cell (the data area enclosed by blank rows and blank columns). In a PivotTable report, select the entire PivotTable report.

Ctrl-Shift-O

Select all cells that contain comments.

Ctrl-\

In a selected row, select all cells with different values than the active cell.

Ctrl-Shift-|

In a selected column, select all cells with different values than the active cell.

Ctrl-[

Select all cells that contain a value that affects the value of any cell in the selection.

Ctrl-Shift-{

Select all cells that contain a value that directly or indirectly affects any cell in the selection.

Ctrl-]

Select all cells that contain a formula that directly references the active selection.

Ctrl-Shift-}

Select all cells that contain formulas that directly or indirectly reference the active cell.

Alt-;

Select the visible cells in the current selection.

Shift-Right arrow

Expand the selection one cell to the right.

Shift-Left arrow

Expand the selection one cell to the left.

Shift-Up arrow

Expand the selection up one cell.

Shift-Down arrow

Expand the selection down one cell.

Ctrl-Shift-Right arrow

Expand the selection right to the last nonblank cell on the row.

Ctrl-Shift-Left arrow

Expand the selection left to the last nonblank cell on the row.

Ctrl-Shift-Up arrow

Expand the selection up to the last nonblank cell in the column.

Ctrl-Shift-Down arrow

Expand the selection down to the last nonblank cell in the column.

Shift-Home

Expand the selection to the beginning of the row.

Ctrl-Shift-Home

Expand the selection to the beginning of the worksheet.

Ctrl-Shift-End

Expand the selection to the end of the worksheet.

Shift-Page Down

Extend the selection down one screen.

Shift-Page Up

Extend the selection up one screen.

Ctrl-F1

Show or hide the Ribbon toolbar.

Shift-F8

Add another range of cells to the selection.

Table 4-10. Data entry

Key

Action

Enter

Add data to a cell and move to the cell below the active cell.

Alt-Enter

Insert a line break in the active cell.

Ctrl-Enter

Fill selected cells with the value in the active cell if the formula bar is active.

Shift-Enter

Add data to a cell and move to the cell above the active cell.

Tab

Add data to a cell and move to the cell to the right of the active cell.

Shift-Tab

Add data to a cell and move to the cell to the left of the active cell.

Esc

Cancel cell entry.

Arrow keys

When a cell is selected, move to an adjacent cell. When editing within a cell, move one character in the direction of the arrow.

Home

Move to the beginning of the line.

F4 or Ctrl-Y

Repeat the last action.

Ctrl-D

Fill from the active cell down to the last cell in a selection.

Ctrl-R

Fill from the active cell to the right-most cell in a selection.

Ctrl-K

Insert a hyperlink.

Ctrl-;

Insert the date.

Ctrl-Shift-:

Insert the time.

Ctrl-Z

Undo the last action.

Table 4-11. Working with formulas

Key

Action

=

Begin entering a formula.

Enter

Complete cell entry and move to the cell below the active cell.

Esc

Cancel cell entry.

Shift-F3

When entering a formula, display the Insert Function dialog box.

Ctrl-A

When the insertion point is to the right of a function name, display the Function Arguments dialog box for that function.

Ctrl-Shift-A

When the insertion point is to the right of a function name, insert the argument names and parentheses into the formula.

F3

Paste the name of a named range into the formula.

Alt-=

Insert a SUM formula into the cell.

F9

Recalculate all formulas in all open workbooks.

Table 4-12. Edit data

Key

Action

F2

Position the insertion point after the last character in the selected cell.

Backspace

Clear the contents of the active cell, or delete one character to the left of the insertion point.

Delete

Delete the character to the right of the insertion point, or delete the contents of the active cell.

Ctrl-Delete

Delete text to the end of the line.

F7

Open the Spelling dialog box.

Shift-F2

Edit a cell comment.

Table 4-13. Format data

Key

Action

Alt-'

Open the Style dialog box.

Ctrl-1

Open the Format Cells dialog box.

Ctrl-Shift-~

Format the cell's contents with the General number format.

Ctrl-Shift-$

Format the cell's contents with the Currency number format.

Ctrl-Shift-%

Format the cell's contents with the Percentage number format.

Ctrl-Shift-#

Apply the Date format as day, month, and year.

Ctrl-Shift-@

Apply the Time format with hour, minutes, and AM or PM.

Ctrl-Shift-!

Apply the Number format with two decimal places, thousands separator, and minus sign for negative values.

Ctrl-B

Apply or remove bold formatting.

Ctrl-I

Apply or remove italics.

Ctrl-U

Apply or remove underlining.

Ctrl-5

Apply or remove strikethrough.

Ctrl-9

Hide selected rows.

Ctrl-Shift-(

Unhide hidden rows within the selection.

Ctrl-0

Hide selected columns.

Ctrl-Shift-)

Unhide hidden columns within the selection.

Ctrl-Shift-&

Add an outline border to the selected cells.

Ctrl-Shift-_

Remove all borders from the selected cells.

Table 4-14. Manipulating shortcut menus

Shift-F10

Display the shortcut menu for the selected item.

Alt-Space

Display the Excel control menu.

Down arrow

When a menu is open, select the next command.

Up arrow

When a menu is open, select the previous command.

Left arrow

Select the menu to the left; in a submenu, switch between the main menu and submenu.

Right arrow

Select the menu to the right; in a submenu, switch between the main menu and submenu.

Home

Select the first command on the open menu.

End

Select the last command on the open menu.

Esc

Close the open menu. In a submenu, close the submenu but keep the menu open.

Table 4-15. Create and manipulate charts

Key

Action

F11 or Alt-F1

Create a chart using the data in the current range.

Ctrl-Page

Down Select the next sheet in the workbook.

Ctrl-Page Up

Select the previous sheet in the workbook.

Down Arrow

Select the previous group of elements in a chart.

Up Arrow

Select the next group of elements in a chart.

Right Arrow

Select the next element in a group.

Left Arrow

Select the previous element in a group.

Table 4-16. Work with macros

Key

Action

Alt-F8

Open the Macro dialog box.

Alt-F11

Open the Visual Basic Editor.

Ctrl-F11

Insert a Microsoft Excel 4.0 macro sheet.

Table 4-17. Display and hide items in a PivotTable

Key

Action

Up arrow

Select the previous item in the range.

Down arrow

Select the next item in the range.

Right arrow

For an item that has lower-level items available, display the lower-level items.

Left arrow

For an item that has lower-level items displayed, hide the lower-level items.

Home

Select the first visible item in the list.

End

Select the last visible item in the list.

Enter

Close the list and display the selected items.

Space

Check, double-check, or clear a checkbox in a list. Double-check selects both an item and all of its lower-level items.

Tab

Switch among the list, the OK button, and the Cancel button.

Table 4-18. Smart Tags

Key

Action

Alt-Shift-F10

Display the menu or message for a Smart Tag. If more than one Smart Tag is present, move to the next Smart Tag and display its menu or message.

Down arrow

Select the next item in a Smart Tag menu.

Up arrow

Select the previous item in a Smart Tag menu.

Enter

Perform the action for the selected item in the Smart Tag menu.

Esc

Close the Smart Tag menu or message without taking action.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required