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 (Tables 4-2 and 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 (Tables 4-7 and 4-8)
Data entry and formatting (Tables 4-9 through 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.
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.
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.
/rworkbook 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.
/pfolder 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.
/aprogID
Starts Excel and loads the named add-in.
/tworkbook 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
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.)
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
Key
Action
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.
Table 4-9. Selecting data and cells (continued)
Key
Action
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.
Table 4-10. Data entry (continued)
Key
Action
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.
Table 4-12. Edit data (continued)
Key
Action
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.
Visual Studio .NET
