• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Quick Reference

Quick Reference

Chapter 1 Getting to Know Excel

Page 4 To open a workbook

1.
On the Standard toolbar, click the Open button.

2.
Click the Look In down arrow, and select the hard disk where you stored the file.

3.
Locate and double-click the target folder to display its contents.

4.
Double-click the target file to open it.

Page 5 To save a workbook

1.
On the Standard toolbar, click the Save button.

2.
Navigate to the folder where you want to save the workbook.

3.
In the File name box, delete the existing file name and type the name for your file.

4.
Click Save.

Page 5 To save a workbook with a different file name, location, and format

1.
On the File menu, click Save As.

2.
Navigate to the folder where you want to save the workbook.

3.
In the File name box, delete the existing file name and type the name for your file.

4.
Click the Save as type down arrow to expand the list, and click the desired type.

5.
Click Save.

Page 5 To set workbook properties

1.
On the File menu, click Properties.

2.
On the Summary tab page, type values for workbook properties in the boxes.

3.
Click the Custom tab.

4.
Click the property for which you want to assign a value, and type the value in the Value box.

5.
Click OK.

Page 8 To move to a worksheet

In the lower left corner of the Microsoft Excel window, click the appropriate sheet tab.

Page 8 To select one or more cells

Click the first cell to be selected, and drag to the last cell to be selected.

Page 6 To select a noncontiguous group of cells

While holding down the key, click the cells to be selected.

Page 10 To select one or more columns or rows

1.
Click the column or row head for the column or row to be selected.

2.
If necessary, drag to the row or column head at the edge of the group to be selected.

Page 13 To create a new workbook

On the Standard toolbar, click the New button.

Page 14 To enter data manually

1.
Click the cell in which you want to enter the data.

2.
Type the data, and press .

Page 14 To quickly enter a series of data

1.
Click the first cell in which you want to enter data.

2.
Type a value, and press .

3.
In the new cell, type the second value in the series.

4.
Grab the fill handle, and drag it to the last cell to be filled with data.

Page 16 To enter data in multiple cells

1.
Click a cell, and type the data to appear in multiple cells.

2.
Select the cells in which you want the data in the active cell to appear.

3.
Press .

Page 17 To find specific data

1.
On the Edit menu, click Find.

2.
In the Find what box, type the word or text you want to find, and then click Find Next.

3.
Click Find Next again to find subsequent occurrences of the text.

Page 18 To replace specific data

1.
On the Edit menu, click Replace.

2.
In the Find what box, type the word or text you want to replace.

3.
In the Replace with box, type the word or text you want to substitute for the text in the Find what box.

4.
Click Find Next.

5.
Click Replace to replace the value in the highlighted cell.

Page 5 To replace cell data manually

1.
Click the cell with the data to be replaced.

2.
Type the new data, and press .

Page 6 To modify cell data manually

1.
Click the cell with the data to be modified.

2.
Click anywhere in the formula bar.

3.
Edit the cell contents in the formula bar, and press .

Page 20 To change an action

1.
Click the Undo button to remove the last change.

2.
Click the Redo button to reinstate the last change you removed.

Page 20 To check spelling

On the Standard toolbar, click the Spelling button.

Page 21 To improve word choice using the Thesaurus

1.
On the Tools menu, click Research.

2.
In the Research task pane, type the word to look up in the Search For box.

3.
Click the Reference down arrow, select Thesaurus: English (U.S.) from the list, and click the Start Searching button.

Page 21 To use online research tools

1.
On the Tools menu, click Research.

2.
In the Research task pane, type the word to look up in the Search For box.

3.
Click the Reference down arrow, select the source in which you want to research from the list, and click the Start Searching button.

Chapter 2 Setting Up a Workbook

Page 29 To name a worksheet

1.
In the lower left corner of the workbook window, right-click the desired sheet tab.

2.
From the shortcut menu that appears, click Rename.

3.
Type the new name for the worksheet, and press .

page 29 To reposition a worksheet

Click the sheet tab of the worksheet you want to move, and drag it to the new position on the tab bar.

Page 26 To change the default number of worksheets

1.
On the Tools menu, click Options.

2.
In the Options dialog box, click the General tab, and, in the Sheets In New Workbook box, type the number of worksheets you want in your new workbooks.

3.
Click OK.

Page 29 To adjust column width

Position the mouse pointer over an edge of the column head of the column to be resized, and drag the edge to the side.

Page 29 To adjust row height

Position the mouse pointer over an edge of the row head in the row to be resized, and drag the edge up or down.

Page 30 To merge cells

1.
Select the cells to be merged.

2.
On the Formatting toolbar, click the Merge and Center toolbar button.

Page 26 To add cells to a worksheet

1.
On the Insert menu, click Cells.

2.
In the Insert dialog box, select the option button indicating whether to shift the cells surrounding the inserted cell down (if your data is arranged as a column) or to the right (if your data is arranged as a row).

3.
Click OK.

Page 28 To move cells within a worksheet

1.
Select the cells and click the Cut toolbar button.

2.
On the Insert menu, click Cut Cells.

3.
In the Insert Paste dialog box, select the option button indicating whether to shift the cells surrounding the inserted cell down (if your data is arranged as a column) or to the right (if your data is arranged as a row).

4.
Click OK.

page 28 To delete cells from a worksheet

1.
Select the cells to delete and, on the Edit menu, click Delete.

2.
In the Delete dialog box, select the option button indicating whether to shift the cells surrounding the deleted cells up (if your data is arranged as a column) or to the left (if your data is arranged as a row).

3.
Click OK.

Page 30 To add a row or column

1.
Click any cell in the row below which you want the new row to appear, or click any cell in the column to the right of which you want the new column to appear.

2.
On the Insert menu, click Rows or Columns.

Page 30 To hide a row or column

1.
Select any cell in the row or column to be hidden.

2.
On the Format menu, point to Row or Column and then click Hide.

Page 31 To unhide a row or column

On the Format menu, point to Row or Column and then click Unhide.

Page 33 To prevent text spillover

1.
Click the desired cell.

2.
On the Format menu, click Cells.

3.
If necessary, click the Alignment tab.

4.
Select the Wrap Text check box, and click OK.

Page 33 To control how text appears in a cell

1.
Click the desired cell.

2.
On the Format menu, click Cells.

3.
Use the controls in the Format Cells dialog box to change the appearance of the cell text.

Page 34 To freeze column headings

1.
Click the first cell in the row below the rows you want to freeze.

2.
On the Window menu, click Freeze Panes.

Page 34 To unfreeze column headings

On the Window menu, click Unfreeze Panes.

page 35 To add a picture to a worksheet

1.
Click the cell into which you want to add the picture.

2.
On the Insert menu, point to Picture and then click From File.

3.
Navigate to the folder with the picture file, and then double-click the file name.

Page 36 To change a picture’s properties

1.
Right-click the graphic, and from the shortcut menu that appears, click Format Picture.

2.
Use the controls in the Format Picture dialog box to change the picture’s properties.

Page 37 To control contrast of an image

1.
Right-click the graphic, and from the shortcut menu that appears, click Format Picture.

2.
Click the Picture tab.

3.
In the Image Control section of the dialog box, clear the contents of the Contrast box, and type the new contrast value.

Page 37 To control the brightness of an image

1.
Right-click the graphic, and from the shortcut menu that appears, click Format Picture.

2.
Click the Picture tab.

3.
In the Image Control section of the dialog box, clear the contents of the Brightness box, and type the new brightness value.

Page 37 To scale and resize graphics

1.
Right-click the graphic, and from the shortcut menu that appears, click Format Picture.

2.
Click the Size tab.

3.
Select the Lock Aspect Ratio check box if you want to maintain the relationship between the image’s height and its width.

4.
Type the percentage value you would like the new image to be in the Height box.

5.
Click OK.

Page 37 To rotate an image

1.
Right-click the graphic, and from the shortcut menu that appears, click Format Picture.

2.
Click the Size tab.

3.
Type the number of degrees to rotate the image in the Rotation box.

Page 37 To crop an image

1.
Right-click the graphic, and from the shortcut menu that appears, click Format Picture.

2.
Click the Picture tab.

3.
In the Crop From section of the tab page, type the amount of the image you want to crop in the Top, Bottom, Left, and Right boxes.

Page 37 To add a background image to a worksheet

1.
On the Format menu, point to Sheet, and click Background.

2.
In the Sheet Background dialog box, click the image that you want to serve as the background pattern for your worksheet, and click OK.

Chapter 3 Performing Calculations on Data

Page 42 To name a range of cells

1.
Select the cells to be included in the range.

2.
Click in the Name box.

3.
Type the name of the range, and press .

Page 43 To name a range of cells using adjacent cell labels

1.
Ensure that the desired name for the cell range is in the topmost or leftmost cell of the range.

2.
Select the cells to be part of the range.

3.
On the Insert menu, point to Name and then click Create.

4.
Select the check box indicating the location of the cell with the name for the range, and then click OK.

Page 48 To write a formula

1.
Click the cell into which the formula will be written.

2.
Type an equal sign, and then type the remainder of the formula.

Page 48 To enter a range into a formula

1.
Click the cell into which the formula will be written.

2.
Type an equal sign, and then type the first part of the formula.

3.
Select the cells to be used in the formula.

4.
Finish typing the formula.

page 49 To copy a formula to another cell

1.
Click the cell containing the formula.

2.
On the Standard toolbar, click the Copy button.

3.
Click the cell into which the formula will be pasted.

4.
On the Standard toolbar, click the Paste button.

Page 49 To create a formula with a function

1.
Click the cell where you want to create the formula.

2.
On the Insert menu, click Function.

3.
Click the function you want to use, and then click OK.

4.
Type the arguments for the function in the argument boxes, and then click OK.

Page 50 To create a formula with a conditional function

1.
Click the cell where you want to create the formula.

2.
On the Insert menu, click Function.

3.
In the Select A Function list, click IF, and then click OK.

4.
In the Logical_test box, type the test to use.

5.
In the Value_if_true box, type the value to be printed if the logical test evaluates to true. (Enclose a text string in quotes.)

6.
In the Value_if_false box, type the value to be printed if the logical test evaluates to false. (Enclose a text string in quotes.)

Page 53 To trace precedents or dependents

1.
Click the cell from which to trace precedents or dependents.

2.
On the Tools menu, point to Formula Auditing and then click Trace Precedents or Trace Dependents.

Page 54 To remove tracer arrows

On the Tools menu, point to Formula Auditing and then click Remove All Arrows.

Page 54 To use the Error Checking tool

1.
Click the cell containing the error.

2.
On the Tools menu, click Error Checking.

3.
Use the controls in the Error Checking dialog box to examine the formula containing the error.

page 54 To evaluate a formula

1.
Click the cell containing the formula.

2.
On the Tools menu, point to Formula Auditing and then click Evaluate Formula.

3.
Use the controls in the Evaluate Formula dialog box to examine the formula containing the error.

Page 53 To watch how the value in a cell changes

1.
Click the cell you want to watch.

2.
On the Tools menu, point to Formula Auditing and then click Show Watch Window.

3.
Click Add Watch.

4.
Click Add.

Page 55 To delete a watch

1.
If necessary, on the Tools menu, point to Formula Auditing and then click Show Watch Window.

2.
In the watch window, click the watch you want to delete.

3.
Click Delete Watch.

Chapter 4 Changing Document Appearance

Page 61 To change the default font settings

1.
On the Tools menu, click Options.

2.
Click the General tab.

3.
Click the Standard Font down arrow, and select the font to use.

4.
Click the Size down arrow, and select the size for the default font.

Page 60 To change cell formatting

1.
Click the cell you want to change.

2.
On the Formatting toolbar, click the button corresponding to the formatting you want to apply.

Page 61 To add cell borders

1.
On the Formatting toolbar, click the Borders button’s down arrow and then, from the list that appears, click Draw Borders.

2.
Click the cell edge on which you want to draw a border.

3.
Drag the mouse pointer to draw a border around a group of cells.

page 61 To add cell shading

1.
Click the cell to be shaded.

2.
On the Formatting toolbar, click the Fill Color button.

3.
In the Fill Color color palette, click the desired square, and then click OK.

Page 61 To change row or column alignment

1.
Click the header of the row or column you want to change.

2.
On the Formatting toolbar, click the button corresponding to the alignment you want to apply.

Page 63 To create a style

1.
On the Format menu, click Style.

2.
In the Style name box, delete the existing value and then type a name for the new style.

3.
Click Modify, and define the style with the controls of the Format Cells dialog box.

4.
Click OK in the Format Cells dialog box and the Styles dialog box.

Page 64 To copy a format

1.
Click the cell with the format to be copied.

2.
On the Standard toolbar, click the Format Painter button.

3.
Click the cell or cells to which the styles will be copied.

Page 64 To apply an AutoFormat

1.
Select the cells to which you want to apply the AutoFormat.

2.
On the Format menu, click AutoFormat.

3.
Select the AutoFormat you want to apply, and then click OK.

Page 66 To format a number

1.
Click the cell with the number to be formatted.

2.
On the Format menu, click Cells.

3.
If necessary, click the Number tab.

4.
In the Category list, click the general category for the formatting.

5.
In the Type list, click the specific format, and then click OK.

Page 58 To format a number as a dollar amount

1.
Click the cell with the number to be formatted.

2.
On the Formatting toolbar, click the Currency Style button.

page 68 To create a custom format

1.
On the Format menu, click Cells.

2.
In the Category list, click Custom.

3.
In the Type list, click the item to serve as the base for the custom style.

4.
In the Type box, modify the item, and then click OK.

Page 71 To create a conditional format

1.
Click the cell to be formatted.

2.
On the Format menu, click Conditional Formatting.

3.
In the second list box, click the down arrow and then click the operator to use in the test.

4.
Type the arguments to use in the condition.

5.
Click the Format button, and use the controls in the Format Cells dialog box to create a format for this condition.

6.
Click OK.

Page 72 To set multiple conditions for a cell

1.
Click the cell to be formatted.

2.
Create a conditional format, and then click Add.

3.
Create a new condition and format in the spaces provided.

Page 74 To add a header or a footer

1.
On the View menu, click Header and Footer.

2.
Click the Custom Header or Custom Footer button.

3.
Add text or images, and click OK.

Page 74 To add a graphic to a header or footer

1.
Create a header or footer.

2.
Click anywhere in one of the section boxes, and then click the Insert Picture button.

3.
Navigate to the folder with the image file, double-click the file name, and then click OK.

Page 78 To change margins

1.
On the Standard toolbar, click the Print Preview button.

2.
Click Margins.

3.
Drag the margin lines in the window to the desired positions.

page 79 To change page alignment

1.
On the File menu, click page Setup.

2.
If necessary, click the Page tab.

3.
Select the appropriate alignment option.

Chapter 5 Focusing on Specific Data Using Filters

Page 85 To find the top ten values in a list

1.
Click the top cell in the column to filter.

2.
On the Data menu, point to Filter and then click AutoFilter.

3.
Click the down arrow that appears, and then click (Top 10...) in the list.

4.
In the Top 10 AutoFilter dialog box, click OK.

Page 86 To find a subset of data in a list

1.
Click the top cell in the column to filter.

2.
On the Data menu, point to Filter and then click AutoFilter.

3.
Click the down arrow that appears, and from the list of unique column values that appears, click the value to use as the filter.

Page 87 To create a custom filter

1.
Click the top cell in the column to filter.

2.
On the Data menu, point to Filter and then click AutoFilter.

3.
Click the down arrow, and then click (Custom?) in the list.

4.
In the upper left box of the Custom AutoFilter dialog box, click the down arrow, and from the list that appears, click a comparison operator.

5.
Type the arguments for the comparison in the boxes at the upper right, and click OK.

Page 87 To remove a filter

On the Data menu, point to Filter and then click AutoFilter.

Page 87 To filter for a specific value

1.
Click the top cell in the column to filter.

2.
On the Data menu, point to Filter and then click AutoFilter.

3.
Click the down arrow, and then, from the list of unique column values that appears, click the value for which you want to filter.

page 88 To select a random row from a list

1.
In the cell next to the first cell with data in it, type =RAND()<#%, replacing # with the number that represents the approximate percentage of rows you want to mark as TRUE.

2.
Press

3.
Click the cell into which you entered the RAND() formula, grab the fill handle, and drag to the cell next to the last cell in the data column.

Page 88 To extract a list of unique values

1.
Click the top cell in the column to filter.

2.
On the Data menu, point to Filter and then click Advanced Filter.

3.
Select the Unique records only check box, and then click OK.

Page 90 To find a total

Select the cells with the values to be summed. The total appears on the status bar, in the lower right corner of the Excel window.

Page 90 To edit a function

1.
Click the cell with the function to be edited.

2.
On the Insert menu, click Function.

3.
Edit the function in the Function dialog box.

Page 93 To set acceptable values for a cell

1.
Click the cell to be modified.

2.
On the Data menu, click Validation.

3.
In the Allow box, click the down arrow, and from the list that appears, click the type of data to be allowed.

4.
In the Data box, click the down arrow, and from the list that appears, click the comparison operator to be used.

5.
Type values in the boxes to complete the validation statement.

6.
Click the Input Message tab.

7.
In the Title box, type the title for the message box that appears when the cell becomes active.

8.
In the Input Message box, type the message the user will see in the message box.

9.
Click the Error Alert tab.

10.
In the Style box, click the down arrow, and from the list that appears, choose the type of box you want to appear.

11.
In the Title box, type the title for the message box that appears when a user enters invalid data.

12.
Type a reminder in the Error message box explaining the restriction.

13.
Click OK.

Page 93 To allow only numeric values in a cell

1.
Click the cell to be modified.

2.
On the Data menu, click Validation.

3.
In the Allow box, click the down arrow, and from the list that appears, click Whole number.

4.
Click OK.

Page 94 To circle invalid data in a worksheet

1.
On the Tools menu, point to Formula Auditing, and click Show Formula Auditing Toolbar.

2.
On the Formula Auditing toolbar, click the Circle Invalid Data button.

Page 94 To hide data validation circles

1.
On the Tools menu, point to Formula Auditing, and click Show Formula Auditing Toolbar.

2.
On the Formula Auditing Toolbar, click the Clear Validation Circles button.

Chapter 6 Combining Data from Multiple Sources

Page 99 To delete a worksheet

On the tab bar, in the lower left corner of the workbook window, right-click the tab of the sheet to be deleted, and from the shortcut menu that appears, click Delete.

Page 99 To save a document as a template

1.
On the File menu, click Save As.

2.
Click the Save as type down arrow, and click Template (.xlt).

Page 100 To edit a template

1.
Click the template you want to edit, and click Open.

2.
Edit the template as if it were any other file.

page 101 To change the default location for templates

1.
On the Tools menu, click Options.

2.
If necessary, click the General tab.

3.
In the At startup, open all files in box, type the path of the folder where Excel should look for the files.

4.
Click OK.

Page 103 To open multiple workbooks

1.
On the Standard toolbar, click the Open button.

2.
Hold down while you click the files to open, and then click Open.

Page 104 To change how a workbook is displayed in Excel

1.
Open the files to be displayed.

2.
On the Window menu, click Arrange.

3.
In the Arrange Windows dialog box, click the option button corresponding to the desired display pattern and click OK.

Page 104 To insert a worksheet in an existing workbook

1.
On the tab bar, right-click the tab of the sheet to move, and then, from the shortcut menu that appears, click Move or Copy.

2.
Click the To book down arrow, and then, from the list that appears, click the book to which you want to move the worksheet.

3.
In the Before sheet list, click the sheet to appear behind the moved sheet.

4.
At the bottom of the Move or Copy dialog box, select the Create a copy check box.

5.
Click OK.

Page 106 To change worksheet tab colors

1.
On the tab bar, right-click the tab to be changed, and then, from the shortcut menu that appears, click Tab Color.

2.
Click the square of the desired color, and click OK.

Page 108 To link to a cell in another worksheet

1.
Click the cell from which to link, and then type =.

2.
Click the title bar of the workbook containing the cell to link to.

3.
Click the cell to link to.

4.
Click the title bar of the workbook from which to link, and then press .

page 109 To fix a broken link

1.
In the alert box that appears when you open a workbook with a broken link, click Update.

2.
Click Edit Links.

3.
Click Change Source.

4.
Click the workbook that is the new source of the linked cell.

5.
In the Edit Links dialog box, click Close.

Page 112 To consolidate data

1.
Open all of the files to be consolidated.

2.
On the Data menu, click Consolidate.

3.
On the Window menu, click the name of a file with data to be consolidated.

4.
Select the cells to consolidate, and click Add.

5.
Repeat steps 3 and 4 to choose corresponding cells in other worksheets.

6.
On the Window menu, click the name of the file that will hold the data summary.

7.
In the Consolidate dialog box, click OK.

Page 114 To save workbooks in a workspace

1.
Open the files to be saved in the workspace.

2.
On the File menu, click Save Workspace.

3.
In the File name box, type the name of the workspace, and click Save.

Page 115 To open a workspace

1.
On the Standard toolbar, click the Open button.

2.
Double-click the workspace.

Chapter 7 Reordering and Summarizing Data

Page 121 To sort a data list

1.
Select the column of cells to be sorted.

2.
On the Standard toolbar, click the Sort Ascending or Sort Descending button.

Page 122 To sort a data list by multiple columns

1.
Select the columns of cells to be sorted.

2.
On the Data menu, click Sort.

3.
If necessary, click the Sort by down arrow, and then, from the list that appears, click the first column to sort by.

4.
Click the Then by down arrow, and then, from the list that appears, click the next column to sort by.

5.
Repeat step 4 with the next Then by down arrow.

6.
Click OK.

Page 123 To set a custom sort order

1.
Type a custom list and highlight its cells.

2.
On the Tools menu, click Options.

3.
Click the Custom Lists tab.

4.
Click Import, and click OK.

Page 127 To find a subtotal

1.
Select the rows for which you want to calculate a subtotal.

2.
On the Data menu, click Subtotals.

3.
Click OK.

Page 128 To create an outline

1.
Select the row heads of the rows to be included in the outline.

2.
On the Data menu, point to Group and Outline and then click Group.

Page 128 To create an outline with multiple levels

1.
Select the row heads of the rows to be included in the first, smaller level of the outline.

2.
Select the row heads of the rows to be included in the second, larger level of the outline.

Page 128 To hide levels of detail

Click the Hide Detail button for the level you want to hide.

Page 129 To show levels of detail

Click the Show Detail button for the level you want to show.

Chapter 8 Analyzing Alternative Data Sets

Page 133 To create a scenario

1.
On the Tools menu, click Scenarios.

2.
In the Scenario Manager dialog box, click Add.

3.
In the Scenario name box, type the name of the new scenario.

4.
At the right edge of the Changing cells box, click the Collapse Dialog button.

5.
Delete the contents of the Add Scenario dialog box, and then hold down while you click the cells to include in the scenario.

6.
At the right edge of the Changing cells box, click the Expand Dialog button.

7.
Click OK.

8.
In the Scenario Values dialog box, enter the alternative values for each cell in the scenario.

9.
Click OK, click Show, and then click Close.

Page 134 To edit a scenario

1.
On the Tools menu, click Scenarios.

2.
In the Scenario Manager dialog box, click the name of the scenario to be edited.

3.
Click Edit.

4.
To change the scenario name, edit the text in the Scenario name box.

5.
To add or delete cells from the scenario, at the right edge of the Changing cells box, click the Collapse Dialog button.

6.
Click OK.

7.
In the Scenario Values dialog box, enter the alternative values for each cell in the scenario.

8.
Click OK, and click Close.

Page 136 To create multiple scenarios

1.
On the Tools menu, click Scenarios.

2.
In the Scenario Manager dialog box, click Add.

3.
In the Scenario name box, type the name of the new scenario.

4.
At the right edge of the Changing cells box, click the Collapse Dialog button.

5.
Delete the contents of the Add Scenario dialog box, and then hold down while you click the cells to include in the scenario.

6.
At the right edge of the Changing cells box, click the Expand Dialog button.

7.
Click OK.

8.
In the Scenario Values dialog box, enter the alternative values for each cell in the scenario.

9.
Click OK.

10.
Repeat steps 2 through 9 for each additional scenario.

page 134 To view scenarios

1.
On the Tools menu, click Scenarios.

2.
In the Scenarios list, click the name of the scenario to show.

3.
Click Show.

Page 137 To summarize scenarios

1.
On the Tools menu, click Scenarios.

2.
Click the Summary button.

3.
In the Result cells box, click the Collapse Dialog button.

4.
Select the cells to appear in the summary.

5.
In the Result cells box, click the Expand Dialog button.

6.
Click OK.

Page 140 To find required values for reaching a target value

1.
Click the cell to hold the target value.

2.
On the Tools menu, click Goal Seek.

3.
In the To value box, type the target value for the active cell.

4.
In the By changing cell box, type the address of the cell to vary.

5.
Click OK.

6.
In the Goal Seek Status dialog box, click OK.

Page 143 To install an Add-In

1.
On the Tools menu, click Add-Ins.

2.
Select the check box next to the Add-In you want to install.

3.
Click OK.

Page 143 To process a Solver problem

1.
On the Tools menu, click Solver.

2.
Click in the Set Target Cell box, and click the cell you want to solve for.

3.
Select the option button indicating whether you want to minimize the target cell value, maximize the target cell value, or set the cell to a particular value.

4.
Click in the By Changing Cells box, and select the cells Solver should vary to change the value in the target cell.

5.
Click Add to display the Add Constraint dialog box.

6.
Click the cell to which you want to add the constraint.

7.
Click the down arrow in the middle box, and select the operation you want to use in the constraint.

8.
Click in the Constraint box, and either type in the value for the constraint, or click the cell with the value to be used as the constraint.

9.
Click Add.

10.
Repeat steps 6 through 9 as necessary to add further constraints.

11.
Click Cancel to return to the Solver dialog box.

12.
Click Solve.

13.
Click Cancel to close Solver without saving your changes, click Save Scenario to save the solution as a scenario, or click OK to keep the Solver solution.

Page 147 To use the Analysis ToolPak

1.
On the Tools menu, click Data Analysis.

2.
Click the item representing the type of analysis you want to perform.

3.
Click OK.

4.
Use the controls in the dialog box that appears to set up your analysis.

5.
Click OK.

Chapter 9 Creating Dynamic Lists with PivotTables

Page 157 To create a PivotTable

1.
Click any cell in the data list.

2.
On the Data menu, click PivotTable and PivotChart Report.

3.
Ensure that the Microsoft Excel list or database option button is selected in the top pane, identifying your worksheet as the data source, and that the PivotTable option button is selected in the bottom pane.

4.
Click Next to move to the next page of the wizard.

5.
Ensure that the proper cell range appears in the Range box.

6.
Click Next to move to the next page of the wizard.

7.
Click Finish.

8.
From the PivotTable Field List dialog box, drag the fields for the horizontal axis to the Drop Column Fields Here box.

9.
From the PivotTable Field List dialog box, drag the fields for the vertical axis to the Drop Row Fields Here box.

10.
From the PivotTable Field List dialog box, drag the data field to the Drop Data Field Here box.

11.
From the PivotTable Field List dialog box, drag the fields for the page area to the Drop Page Fields Here box.

Page 164 To filter a PivotTable

1.
Click the down arrow at the right edge of any field heading.

2.
From the list of values that appears, click the value to use as the filter.

3.
If the list appears as a list of values with check boxes next to the values, select the check boxes beside the values to appear in the PivotTable.

4.
Click All from the list to remove a filter.

Page 158 To format PivotTable data

1.
Select the cells in the PivotTable data area.

2.
On the Format menu, click Cells.

3.
Use the controls in the Format Cells dialog box to format the cells in the PivotTable, and click OK.

Page 158 To apply a predefined format to a PivotTable

1.
If the PivotTable toolbar is hidden, right-click any toolbar and then, from the shortcut menu that appears, click PivotTable.

2.
Click any cell in the PivotTable.

3.
On the PivotTable toolbar, click the Format Report button.

4.
Click the desired AutoFormat.

Page 164 To add a field to a PivotTable

1.
Click any cell in the PivotTable.

2.
If the PivotTable toolbar is hidden, right-click any toolbar and then, from the shortcut menu that appears, click PivotTable.

3.
If the PivotTable Field List dialog box is hidden, on the PivotTable toolbar, click the Show Field List button.

4.
From the PivotTable Field List dialog box, drag the new field to the desired area of the PivotTable.

Page 165 To change a PivotTable’s layout

1.
On the PivotTable toolbar, click PivotTable and then click Wizard.

2.
Click Layout.

3.
Drag fields to new areas.

4.
Click OK, and click Finish.

5.
You can also drag fields directly on the PivotChart to change the layout.

Page 166 To refresh PivotTable data

1.
Click any cell in the PivotTable.

2.
If the PivotTable toolbar is hidden, right-click any toolbar and then, from the shortcut menu that appears, click PivotTable.

3.
On the PivotTable toolbar, click the Refresh External Data button.

Page 166 To show or hide underlying PivotTable data

Double-click a column or row head in a PivotTable to collapse or expand the rows or columns defined by the column head.

Page 166 To create a link to a PivotTable field

1.
Click the cell you want to link to the PivotTable field, and type =

2.
On the tab bar, click the sheet tab of the worksheet with the PivotTable.

3.
Click the PivotTable cell to supply the data for the other cell.

4.
Press to accept the GETPIVOTDATA formula Excel creates.

Page 169 To import a text file

1.
On the Data menu, point to Import External Data and then click Import Data.

2.
Navigate to the folder with the file to be imported, and double-click the file name.

3.
If necessary, select the Delimited or Fixed Width option button to identify how columns are marked in the text file. Click Next to accept the Text Import Wizard’s summary of the text file’s data, and move to the second page of the wizard.

4.
If necessary, select the check box next to the proper delimiter for the text file. Click Next to accept the Text Import Wizard’s analysis of the text file’s data, and move to the third page of the wizard.

5.
Click Finish to accept the values and data types as assigned by the wizard.

6.
Click OK to paste the imported data into the active worksheet, beginning at the active cell.

Chapter 10 Creating Charts

Page 177 To create an embedded chart

1.
Select the cells to provide data for the chart.

2.
On the Standard toolbar, click the Chart Wizard button.

3.
In the Chart type section, click the desired chart type; and then, in the Chart subtype section, click the desired subtype.

4.
Click Next to move to the next wizard page.

5.
Verify that the axis and data series names are correct.

6.
Click Next to move to the next wizard page.

7.
In the Chart title box, type the name of the chart and then press .

8.
Type names for the chart title and axes in the boxes provided, and then click Next.

9.
Click Finish to accept the default choice to create the chart as part of the active worksheet.

Page 178 To resize a chart

Grab the sizing handle at the edge of the chart, and drag it to resize the chart.

Page 179 To change a chart’s background

1.
Right-click anywhere in the Chart Area of the chart, and then, from the shortcut menu that appears, click Format Chart Area.

2.
In the Area section of the Format Chart Area dialog box, click the Fill Effects button.

3.
Click the Texture tab to display the Texture tab page.

4.
Click the desired texture.

5.
Click OK twice to close the Fill Effects dialog box and the Format Chart Area dialog box.

Page 182 To customize chart labels

1.
Double-click the chart label to be customized.

2.
Use the controls in the dialog box that appears to customize the chart label.

3.
To change the text of a chart label, click the label and edit it in the text box that appears.

Page 182 To customize chart number formats

1.
Double-click the axis of the chart with the numbers to be customized.

2.
In the Format Axis dialog box that appears, click the Number tab.

3.
Use the controls on the Number tab page to format the chart numbers.

4.
Click OK.

page 185 To perform trendline analysis

1.
In a chart, right-click a data point in the body of the chart and then, from the shortcut menu that appears, click Add Trendline.

2.
If necessary, in the Trend/Regression type section, click Linear.

3.
Click the Options tab.

4.
In the Forecast section, type the number of horizontal axis units to look ahead in the Forward box. Then click OK.

Page 188 To create a PivotChart

1.
Click any cell in the data list.

2.
On the Data menu, click PivotTable and PivotChart Report.

3.
Ensure that the Microsoft Excel list or database option button is selected in the top pane, identifying your worksheet as the data source, and that the PivotChart report (with PivotTable report) option button is selected in the bottom pane.

4.
Click Next to move to the next page of the wizard.

5.
Ensure that the proper cell range appears in the Range box.

6.
Click Next to move to the next page of the wizard.

7.
Click Finish.

8.
From the PivotTable Field List dialog box, drag the fields for the horizontal axis to the Drop Column Fields Here box.

9.
From the PivotTable Field List dialog box, drag the fields for the vertical axis to the Drop Row Fields Here box.

10.
From the PivotTable Field List dialog box, drag the data field to the Drop Data Field Here box.

11.
From the PivotTable Field List dialog box, drag the fields for the page area to the Drop page Fields Here box.

Page 189 To save a PivotChart as a custom chart type

1.
On the Chart menu, click Chart Type.

2.
If necessary, click the Custom Types tab to display the Custom Types tab page.

3.
In the Select from section, select the User-defined option button and then click Add.

4.
In the Name box, type a name for the chart type.

5.
In the Description box, type a description for the chart type. Then click OK.

page 190 To change a PivotChart’s chart type

1.
Open the Chart menu, and click Chart Type.

2.
Click the Standard Types tab.

3.
In the Chart type section, click the desired chart type. Then click OK.

Page 192 To add a diagram to a worksheet

Open the Insert menu, and click Diagram.

Page 194 To reformat a diagram element

1.
Click the diagram element you want to reformat.

2.
Use the controls on the diagram type’s toolbar (e.g., the Organization Chart toolbar) or the Formatting toolbar to reformat the element.

Chapter 11 Printing

Page 202 To preview a worksheet

On the Standard toolbar, click the Print Preview button.

Page 202 To change printer orientation

1.
On the Standard toolbar, click the Print Preview button.

2.
Click Setup.

3.
In the Orientation pane, select the Landscape or Portrait option button.

4.
Click OK.

Page 202 To zoom in on part of a page

1.
On the Standard toolbar, click the Print Preview button.

2.
Click the Zoom button.

Page 203 To preview and change page breaks

1.
On the Standard toolbar, click the Print Preview button.

2.
Click Page Break Preview.

3.
Drag the page break line to the desired location on the page.

4.
On the View menu, click Normal.

Page 204 To change page printing order

1.
On the File menu, click Page Setup.

2.
Click the Sheet tab to display the Sheet tab page.

3.
In the Page order section, select the option button for the desired page order.

page 204 To print a multipage worksheet

1.
On the File menu, click Print.

2.
In the Print range section, select the All option button.

3.
Click Print.

Page 204 To print nonadjacent worksheets in a workbook

1.
On the tab bar, hold down while you click the sheet tabs of the worksheets to print.

2.
On the Standard toolbar, click the Print button.

Page 204 To suppress error messages when printing

1.
On the File menu, click Page Setup.

2.
Click the Sheet tab to display the Sheet tab page.

3.
In the Print section, click the Cell errors as box and then, from the list that appears, click the desired representation.

Page 206 To print selected pages of a multipage worksheet

1.
On the File menu, click Print.

2.
In the Print range section, select the Page(s) option button.

3.
In the From box, type the first page to print.

4.
In the To box, type the last page to print.

5.
Click OK.

Page 207 To print a worksheet on a specific number of pages

1.
On the File menu, click Page Setup.

2.
Click the Page tab.

3.
In the Scaling section, select the Fit to option button and then type the desired number of pages in the page(s) wide by and tall boxes.

4.
Click OK.

Page 207 To define a print area and center it on a page

1.
Select the cells to be printed.

2.
On the File menu, point to Print Area and then click Set Print Area.

3.
On the Standard toolbar, click the Print Preview button.

4.
Click the Setup button.

5.
Click the Margins tab to display the Margins tab page.

6.
In the Center on page section, select the Horizontally check box and the Vertically check box.

7.
Click OK.

Page 208 To hide columns or rows during printing

1.
Select the column or row heads of the columns or rows to be hidden.

2.
On the Format menu, point to Columns or Rows and then click Hide.

Page 208 To unhide columns or rows during printing

On the Format menu, point to Columns or Rows and then click Unhide.

Page 206 To repeat rows or columns at the top or left of printed pages

1.
On the File menu, click Page Setup.

2.
Click the Sheet tab to display the Sheet tab page.

3.
Click the Collapse Dialog button next to the Rows to repeat at top or Columns to repeat at left box.

4.
Select the rows or columns to repeat.

5.
Click the Expand Dialog button.

6.
Click OK.

Page 210 To print a chart without printing the worksheet

1.
Click the chart.

2.
On the File menu, click Print.

3.
In the Print what section, ensure that the Selected Chart option button is selected.

4.
Click OK to print the chart.

Page 210 To print a worksheet without printing a chart

1.
Right-click the Chart Area of the chart, and then, from the shortcut menu that appears, click Format Chart Area.

2.
Click the Properties tab.

3.
Clear the Print object check box, and then click OK.

4.
Click on the worksheet so the chart is no longer selected, and then print the worksheet.

Page 212 To print a chart at its actual size

1.
Click the chart to select it.

2.
On the Standard toolbar, click the Print Preview button.

3.
Click the Setup button.

4.
Click the Chart tab.

5.
Select the Custom option button, and then click OK.

Chapter 12 Automating Repetitive Tasks with Macros

Page 218 To open and view a macro

1.
Open a workbook with a macro attached.

2.
Click Enable Macros to allow macros to run.

3.
On the Tools menu, point to Macro and then click Macros.

4.
In the Macro Name pane, click the name of the macro to view.

5.
Click Edit.

6.
Click Close to close the macro.

Page 219 To step through a macro

1.
On the Tools menu, point to Macro and then click Macros.

2.
In the Macro Name list, click the name of the macro to step through.

3.
Click Step Into.

4.
Right-click the taskbar, and then, from the shortcut menu that appears, click Tile Windows Vertically.

5.
Press to execute each macro step.

6.
After the last macro step, in the Microsoft Visual Basic Editor, click the Close button.

Page 220 To run a macro

1.
On the Tools menu, point to Macro and then click Macros.

2.
In the Macro Name list, click the name of the macro to run.

3.
Click Run.

Page 221 To create a macro

1.
On the Tools menu, point to Macro and then click Record New Macro.

2.
In the Macro name box, delete the existing name and then type a name for the new macro.

3.
Click OK.

4.
Execute the steps that make up the macro.

5.
On the Stop Recording toolbar, click the Stop Recording button.

page 221 To modify an existing macro

1.
On the Tools menu, point to Macro and then click Macros.

2.
In the Macro name list, click the macro name and then click Edit.

3.
Change the VBA code that makes up the macro.

4.
On the Visual Basic Editor’s Standard toolbar, click the Save button to save your change.

5.
Click the Close button.

Page 225 To create a toolbar

1.
On the Tools menu, click Customize.

2.
If necessary, click the Toolbars tab to display the Toolbars tab page.

3.
Click New.

4.
In the Toolbar name box, type the name of the new toolbar.

5.
Click OK.

Page 225 To add a macro button to a toolbar

1.
On the Tools menu, click Customize.

2.
In the Customize dialog box, click the Commands tab.

3.
In the Categories list, click Macros.

4.
Drag the Custom Button command to the target toolbar.

5.
On the Custom Macros toolbar, right-click the Custom button and then, from the shortcut menu that appears, click Name.

6.
Type a name for the button, and then press .

7.
On the target toolbar, right-click the new button and then, from the shortcut menu that appears, click Assign Macro.

8.
Click the name of the macro to be assigned to the button.

9.
Click OK.

10.
In the Customize dialog box, click Close.

Page 226 To delete a custom toolbar

1.
On the Tools menu, click Customize.

2.
If necessary, click the Toolbars tab.

3.
In the Toolbars list, click the name of the toolbar to be deleted.

4.
Click Delete.

5.
Click OK in the warning dialog box that appears.

6.
Click Close to close the Customize dialog box.

Page 228 To create a new menu

1.
On the Tools menu, click Customize.

2.
If necessary, click the Commands tab to display the Commands tab page.

3.
In the Categories list, click New Menu.

4.
Drag New Menu from the Commands list to the spot on the main menu bar where you want the new menu to appear.

5.
Right-click the New Menu heading, and then, from the shortcut menu that appears, click Name.

6.
Type a new name for the menu, and then press .

7.
In the Customize dialog box, click Close.

Page 229 To add a macro to a menu

1.
On the Tools menu, click Customize.

2.
If necessary, click the Commands tab to display the Commands tab page.

3.
In the Categories list of the Customize dialog box, click Macros.

4.
In the Commands list, drag the Custom Menu Item command to the new menu head. When a box appears under the menu head, drag Custom Menu Item onto it.

5.
On the new menu, right-click Custom Menu Item and then, from the shortcut menu that appears, click Name.

6.
Type a name for the item, and then press

7.
On the new menu, right-click the new menu item and then, from the shortcut menu that appears, click Assign Macro.

8.
In the Macro name box, click the name of the macro to assign to the menu item.

9.
Click OK.

10.
Click Close to close the Customize dialog box.

Page 229 To delete a custom menu

1.
On the Tools menu, click Customize.

2.
Right-click the menu head of the menu to be deleted, and then, from the shortcut menu that appears, click Delete.

3.
In the Customize dialog box, click Close.

page 230 To run a macro when a workbook is opened

1.
On the Tools menu, point to Macro and then click Record New Macro.

2.
In the Macro name box, type a name that begins with Auto_, such as Auto_Open.

3.
Click OK.

4.
Carry out the steps to be saved in the macro.

5.
On the Stop Recording toolbar, click the Stop Recording button.

Chapter 13 Working with Other Microsoft Office Programs

Page 235 To link to an external document

1.
On the Insert menu, click Object.

2.
Click the Create from File tab to display the Create from File tab page.

3.
Click Browse.

4.
Navigate to the target folder, and double-click the file to include in the workbook.

5.
Select the Link to file check box.

6.
Click OK.

Page 236 To edit a linked file

Right-click the linked file, and then, from the shortcut menu that appears, point to Presentation Object (in the case of a Microsoft PowerPoint presentation) and then click Edit.

Page 238 To store a workbook as part of another file

1.
Open the other Office file.

2.
On the Insert menu, click Object.

3.
Select the Create from File option button.

4.
Click the Browse button.

5.
Navigate to the target folder, and double-click the workbook to include in the file.

6.
Click OK.

Page 239 To edit an embedded workbook

Right-click the linked file, and then, from the shortcut menu that appears, point to Worksheet Object (in the case of a PowerPoint presentation) and then click Edit.

page 243 To create a hyperlink to another location within the same document

1.
Right-click the cell into which you want to insert the hyperlink, and then, from the shortcut menu that appears, click Hyperlink.

2.
Click the Place in This Document button.

3.
In the Or select a place in this document box, click the target for the hyperlink.

4.
If desired, type a cell reference in the Type the Cell Reference box.

5.
In the Text to display box, type the text to be shown as the link.

6.
Click OK.

Page 242 To create a hyperlink between documents

1.
Right-click the cell into which you want to insert the hyperlink, and then, from the shortcut menu that appears, click Hyperlink.

2.
If necessary, click the Existing File or Web Page button.

3.
Navigate to the folder with the target file.

4.
Click the name of the target file.

5.
In the Text to display box, type the text to be shown as the link.

6.
Click OK.

Page 244 To paste a chart into another document

1.
Right-click a blank spot on the chart, and then, from the shortcut menu that appears, click Copy to copy the chart image to the Clipboard.

2.
Open the file into which the chart will be pasted.

3.
Right-click a blank spot in the active document, and from the shortcut menu that appears, click Paste.

Chapter 14 Working with Database Data

Page 250 To find a value

1.
Create a data range in which the leftmost column contains a unique value for each row.

2.
In a cell, type =VLOOKUP(cell2, range, column, FALSE) (where cell2 is the cell for someone to enter a value for Excel to find in the leftmost column, range is the range or name of the range, and column is the number of the column—counting from the left—for the value to be returned), and press FALSE finds only exact matches, while TRUE would find the nearest match equal to or less than the specified value.

3.
In cell2, type the value to be found in the named range, and press .

Page 253 To define a new Microsoft Access data source

1.
On the Data menu, point to Import External Data and then click New Database Query.

2.
If necessary, click the Databases tab in the Choose Data Source dialog box.

3.
Click <New Data Source>, and then click OK.

4.
In the first box, type the name of the source.

5.
In the second box, click the down arrow and then, from the list that appears, click Microsoft Access Driver (*.mdb).

6.
Click Connect.

7.
Click Select.

8.
Navigate to the target folder, click the target database, and then click OK.

9.
Click OK again.

10.
In the fourth box, click the down arrow and then, from the list that appears, click the default table for the data source.

11.
Click OK.

Page 254 To create a database query

1.
On the Data menu, point to Import External Data and then click New Database Query.

2.
If necessary, click the Databases tab in the Choose Data Source dialog box.

3.
Click the name of the data source, and then click OK.

4.
Add the table columns you want to use in your query by clicking the column name and then clicking Add.

5.
Click Next.

6.
In the Column To Filter pane, click the name of the column by which you want to filter the results.

7.
In the first comparison operator box, click the down arrow and then, from the list that appears, click the comparison operator to be used.

8.
In the first value box, type the first value to use in the comparison.

9.
If necessary, type a second value in the second value box.

10.
Click Next.

11.
In the Sort by box, click the down arrow and then, from the list that appears, click the name of the column by which to sort the query results, and click Ascending or Descending.

12.
Click Next.

13.
Click Save Query.

14.
In the File name box, type a name for the query and then click Save.

15.
Click Finish.

16.
In the Import Data dialog box, click OK.

Page 258 To summarize data with a database function

1.
Create a data list.

2.
Above or to the side of the data list, copy the headings from the columns in the data list.

3.
In the cells directly below the copied headings, type the rules to use as criteria to limit the list rows considered by the database formulas.

4.
In any blank cell above or to the side of the data list, type the database formula following the pattern DFUNCTION(data, <;$QD> field <;$QD>, criteria), where:

  • DFUNCTION is the database function (e.g., DSUM or DAVERAGE)

  • data is the range of cells containing the data list (including column headings)

  • <;$QD>field<;$QD> is the name of the field (enclosed in quotes) to be used in calculating the formula’s result

  • criteria is the range of cells containing the rules (including column headings) to be used in limiting the cells considered by the formula.

Chapter 15 Publishing Information on the Web

Page 265 To save a workbook as an HTML document

1.
On the File menu, click Save as Web Page.

2.
If necessary, in the Save pane, select the Entire Workbook option button.

3.
Enter a file name in the File name box, and then click Save.

Page 265 To view a workbook saved as an HTML file

1.
Start Microsoft Internet Explorer.

2.
In Internet Explorer, open the File menu and then click Open.

3.
Click Browse.

4.
Navigate to the target folder, and then double-click the file to be viewed.

5.
Click OK.

page 267 To publish a worksheet on the Web

1.
On the File menu, click Save as Web Page.

2.
Navigate to the directory to which you want to publish the worksheet.

3.
In the Save section of the dialog box, select the Selection: Sheet option button to publish the active worksheet on the Web.

4.
In the Publish as section of the dialog box, click the Change button.

5.
In the Title box, type a new title for the page and then click OK.

Page 268 To update Microsoft Excel Web pages automatically

1.
On the File menu, click Save as Web Page.

2.
In the Save section of the dialog box, select the Selection: Sheet option button to publish the active worksheet on the Web.

3.
In the Publish as section of the dialog box, click the Change button.

4.
In the Title box, type a new title for the page and then click OK.

5.
Select the AutoRepublish every time this workbook is saved check box.

Page 268 To edit a workbook over the Web

1.
On the File menu, click Save as Web Page.

2.
Navigate to the Web directory to which you want to publish the workbook.

3.
In the Save section of the dialog box, select the Selection: Sheet option button to publish the active worksheet on the Web.

4.
In the Save section of the dialog box, select the Add interactivity check box and then click OK.

5.
Open the file in Internet Explorer.

6.
Use the tools on the Interactivity toolbar to edit the worksheet.

Page 271 To save a PivotTable to the Web

1.
Click any cell in the PivotTable.

2.
On the File menu, click Save as Web Page.

3.
In the Save section of the dialog box, select the Selection: Sheet option button.

4.
In the Save section of the dialog box, select the Add interactivity check box and then click Publish.

5.
If necessary, click the Choose down arrow, and then, from the list that appears, click Items on Pivot.

6.
In the list below the Choose box, click the item beginning with PivotTable.

7.
Click Publish.

Page 272 To work with a PivotTable via the Web

1.
Open the Web page with the PivotTable in Internet Explorer.

2.
Use the down arrows and column heads to modify the PivotList’s organization.

Page 274 To link to Web data

1.
In Internet Explorer, open the Web page with the table data to which you want to link.

2.
Select the table data, and then press to copy the data to the Clipboard.

3.
In Excel, click the desired cell, and then, on the Standard toolbar, click the Paste button.

4.
Click the Paste Options button, and then, from the list that appears, click Create Refreshable Web Query.

5.
Click the table icon next to the data you want to import, and then click Import.

Page 276 To acquire real-time data

1.
On the Tools menu, click AutoCorrect Options.

2.
Click the Smart Tags tab to display the Smart Tags tab page.

3.
Select the Label data with smart tags check box.

4.
Verify that the five check boxes in the Recognizers list are selected.

5.
Select the Embed smart tags in this workbook check box.

6.
Click Check Workbook.

7.
Move the mouse pointer over a cell with the Smart Tag indicator.

8.
Click the Smart Tag Actions button, and then, from the list that appears, click the desired action.

9.
Select the Starting at cell option button, verify that the proper cell appears in the Starting at cell box, and then click OK.

Page 278 To export an Excel document as XML

1.
On the File menu, click Save As.

2.
Click the Save as type down arrow, and then, from the list that appears, click XML Spreadsheet (*.xml).

3.
Click Save.

4.
Click Yes to clear the message box and save the workbook as an XML spreadsheet.

page 279 To import an XML file into Excel

1.
On the Standard toolbar, click the Open button.

2.
Navigate to the target folder, and double-click the target file with the .xml extension.

Page 283 To apply an XML data map to a worksheet

1.
On the Data menu, point to XML, and then click XML Source.

2.
Click Workbook Maps.

3.
Click Add.

4.
Navigate to the folder, click the .xsd file that contains the schema, and click Open.

5.
If necessary, click the entry representing the schema, and click OK.

6.
Drag the elements you want to add from the XML Source task pane to the body of the worksheet.

Page 284 To import XML data into a worksheet with a data map

1.
On the Data menu, point to XML, and then click Import.

2.
Click the name of the file that contains the XML data, and click Import.

Page 284 To change the viewing options of an XML data map

1.
Click a cell in the list you want to modify.

2.
On the Data menu, point to XML, and then click XML Source.

3.
Click Options in the XML Source task pane, and click the viewing option you want to apply to the list.

Page 284 To remove an element from an XML data map

1.
On the Data menu, point to XML, and then click XML Source.

2.
In the XML Source task pane, right-click the element to remove and click Remove Element.

3.
Click OK to acknowledge that the deletion will cause data to be lost.

Page 284 To remove an XML data map assignment

1.
On the Data menu, point to XML, and then click XML Source.

2.
In the XML Source task pane, click Workbook Maps.

3.
Click the data map to remove, click Delete, and click OK to clear the message box that appears.

Chapter 16 Collaborating with Colleagues

page 289 To turn workbook sharing on

1.
On the Tools menu, click Share Workbook.

2.
Select the Allow changes by more than one user at the same time check box, and then click the Advanced tab.

3.
Click OK to accept the default settings.

4.
Click OK if a message box appears, and then save the workbook.

Page 289 To send a workbook to colleagues by e-mail

1.
From the Microsoft Windows Start menu, open Microsoft Outlook Express.

2.
Click the New Mail button.

3.
Click the Attach button.

4.
Navigate to the target folder, and double-click the file to attach.

5.
Click Send.

Page 292 To add a comment

1.
Click the cell to which to add the comment.

2.
On the Insert menu, click Comment.

3.
In the comment field, type the comment.

4.
Click a different cell to close the comment.

Page 292 To view a comment

Move the mouse pointer over a cell with a comment.

Page 292 To delete a comment

Right-click the cell with the comment, and then, from the shortcut menu that appears, click Delete Comment.

Page 294 To turn on change tracking

1.
On the Tools menu, point to Track Changes and then click Highlight Changes.

2.
Select the Track changes while editing check box.

3.
If necessary, clear the When check box.

4.
If necessary, clear the Who check box.

5.
If necessary, select the Highlight changes on screen check box.

6.
Click OK.

page 296 To accept or reject changes in a single workbook

1.
On the Tools menu, point to Track Changes and then click Accept or Reject Changes.

2.
Click OK to save the workbook and clear the message box that appears.

3.
Verify that the When check box is selected and that Not yet reviewed appears in the When box.

4.
Click OK.

5.
Click Accept to accept the change, or click Reject to reject the change.

Page 295 To add a history worksheet to a workbook

1.
On the Tools menu, point to Track Changes and then click Highlight Changes.

2.
Select the List changes on a new sheet check box.

3.
Click OK.

Page 297 To merge changes from multiple workbooks

1.
On the Tools menu, click Compare and Merge Workbooks.

2.
Hold down as you click the files with the changes to be merged, and then click OK.

3.
On the Standard toolbar, click the Save button to save your work.

4.
On the Tools menu, point to Track Changes and then click Accept or Reject Changes.

5.
Verify that the When check box is selected and that Not yet reviewed appears in the When box, that the Who check box is cleared, and that the Where check box is cleared.

6.
Click OK.

Page 300 To password-protect a workbook

1.
On the File menu, click Save As.

2.
Click the Tools menu head, and then click General Options.

3.
In the Password to open box, type a password and then click OK.

4.
In the Reenter password to proceed box, type the same password and then click OK.

5.
Click Save.

6.
Click Yes if a message box appears.

page 299 To remove a password from a workbook

1.
On the File menu, click Save As.

2.
Click the Tools menu head, and then click General Options.

3.
In the Password to open box, erase the existing password and then click OK.

Page 301 To password-protect a worksheet

1.
Activate the worksheet to be protected.

2.
On the Tools menu, point to Protection and then click Protect Sheet.

3.
In the Password to unprotect sheet box, type the password and then click OK.

4.
In the Confirm Password dialog box, type the same password in the space provided, and then click OK.

Page 301 To remove protection from a worksheet

1.
On the Tools menu, point to Protection and then click Unprotect Sheet.

2.
In the Password box, type the password and then click OK.

Page 301 To password-protect a range

1.
Select the cells to be protected.

2.
On the Tools menu, point to Protection and then click Allow Users to Edit Ranges.

3.
Click New.

4.
In the Title box, type the name for the new range.

5.
In the Range password box, type a password and then click OK.

6.
In the Confirm password box, type the same password and then click OK.

Page 301 To remove a password from a cell range

1.
On the Tools menu, point to Protection and then click Allow Users to Edit Ranges.

2.
In the Ranges box, click the range to be unprotected and then click Delete.

3.
Click OK.

Page 304 To create a self-signed digital certificate

1.
On the Start menu, click Run.

2.
In the Open box, type C:\Program Files\Microsoft Office\OFFICE11\Selfcert.exe and press If Office was not installed in the default directory, modify the path to reflect the directory in which it was installed.

3.
In the Create Digital Certificate dialog box, type a name for your certificate.

4.
Click OK.

page 305 To digitally sign a workbook

1.
On the Tools menu, click Options.

2.
In the Options dialog box, click the Security tab, and click Digital Signatures.

3.
In the Select Certificate dialog box, click the certificate with which you want to sign the workbook and click OK.

4.
Click OK to close the Digital Signatures dialog box, and again to close the Options dialog box.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint