Excel 2007 Pocket Guide, 2nd Edition

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:

• General program and navigational shortcuts (Table 4-7 and Table 4-8)

• Data entry and formatting (Table 4-9, Table 4-10, Table 4-11, Table 4-12, through Table 4-13)

• Manipulating shortcut menus (Table 4-14)

• Creating and manipulating charts (Table 4-15)

• Working with macros (Table 4-16)

• Displaying and hiding items in a PivotTable (Table 4-17)

• Using Smart Tags (Table 4-18)

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.