Quick Reference

2. Setting Up a Workbook

To open a workbook, page 14

1.	Click the Microsoft Office Button and then click Open.
2.	Navigate to the folder that contains the workbook you want to open.
3.	Click the workbook.
4.	Click Open.

To create a new workbook, page 14

1.	Click the Microsoft Office Button.
2.	Click New.
3.	Click Blank Workbook.

To save a workbook, page 15

1.	On the Quick Access Toolbar, click the Save button.
2.	Type a name for the file.
3.	Click Save.

To set file properties, page 15

1.	Open the file for which you want to assign property values.
2.	Click the Microsoft Office Button.
3.	Point to Finish and then click Properties.
4.	Add information describing your file.

To define custom properties, page 16

1.	Open the file for which you want to assign property values.
2.	Click the Microsoft Office Button.
3.	Point to Finish and then click Properties.
4.	Click the Property Views and Options down arrow.
5.	Click Advanced.
6.	Click the Custom tab.
7.	Type a property name.
8.	Select the type of data contained in the property.
9.	Type a value for the property.
10.	Click Add.
11.	Click OK.

To display a worksheet, page 18

• Click the sheet tab of the worksheet you want to display.
  
  

To create a new worksheet, page 18

1.	Right-click the sheet tab of the worksheet that follows the location where you want to insert a worksheet.
2.	Choose Insert from the shortcut menu.
3.	Double-click Worksheet.

To rename a worksheet, page 18

1.	Double-click the sheet tab of the worksheet you want to rename.
2.	Type the new name of the worksheet and press Enter.

To copy a worksheet to another workbook, page 18

1.	Open the workbook that will receive the new worksheets.
2.	Switch to the workbook that contains the worksheets you want to copy, hold down the Ctrl key, and click the sheet tabs of the worksheets you want to copy.
3.	Right-click the selection.
4.	Choose Move Or Copy from the shortcut menu.
5.	Select the Create A Copy check box.
6.	Click the To Book down arrow.
7.	Click the workbook to which you want the worksheet(s) copied.
8.	Click OK.

To change the order of worksheets in a workbook, page 19

• Drag the sheet tab of the worksheet you want to move.
  
  

To hide a worksheet, page 19

1.	Hold down the key and click the sheet tabs of the worksheets you want to hide.
2.	Right-click any selected worksheet tab and then choose the Hide command.

To unhide a worksheet, page 19

1.	Right-click any worksheet tab.
2.	Click Unhide.
3.	Click the worksheet you want to unhide.
4.	Click OK.

To delete a worksheet, page 19

1.	Hold down the key and click the sheet tabs of the worksheets you want to delete.
2.	Right-click the selection.
3.	Choose Delete from the shortcut menu.

To change a row's height or column's width, page 21

1.	Select the rows and columns you want to resize.
2.	Drag a row or column border until it is the desired size.

To insert a column or row, page 21

1.	Right-click the column header to the right of, or the row header below, where you want the new column or row to appear.
2.	Choose Insert from the shortcut menu.

To delete a column or row, page 22

1.	Select the row or column you want to delete.
2.	Right-click the selection and choose Delete from the shortcut menu.

To hide a column or row, page 22

1.	Select the rows or columns you want to hide.
2.	Right-click a row or column header in the selection and choose Hide from the shortcut menu.

To unhide a column or row, page 22

1.	Click the row or column header of the row above or the column to the left of the rows or columns you want to unhide.
2.	Hold down the key and click the row or column header of the row or column below or to the right of the rows or columns you want to unhide.
3.	Right-click the selection and choose Unhide from the shortcut menu.

To insert a cell, page 22

1.	Select the cells in the spot where you want to insert new cells.
2.	Click the Home tab.
3.	In the Cells group, click the Insert button's down arrow.
4.	Click Insert Cells.
5.	Select the option button representing how you want to move the existing cells to make room for the inserted cells.
6.	Click OK.

To delete a cell, page 22

1.	Select the cells you want to delete.
2.	Click the Home tab.
3.	In the Cells group, click the Delete button's down arrow.
4.	Click Delete Cells.
5.	Select the option button representing how you want the remaining cells to fill in the deleted space.
6.	Click OK.

To move a group of cells to a new location, page 23

1.	Select the cells you want to move.
2.	Move the mouse pointer over the outline of the selected cells.
3.	Drag the cells to the desired location.

To zoom in or out on a worksheet, page 26

• Click the Zoom In control to make your window's contents 10 percent larger per click.
  
  
• Click the Zoom Out control to make your window's contents 10 percent smaller per click.
  
  
• Drag the Zoom slider control, shown in the figure, to the left to zoom out, or to the right to zoom in.
  
  

To zoom in or out to a specific zoom level, page 26

1.	On the View tab, in the Zoom group, click Zoom.
2.	Select the Custom option.
3.	Type a new zoom level in the Custom field.
4.	Click OK.

To change to another open workbook, page 27

1.	On the View tab, in the Window group, click Switch Windows.
2.	Click the name of the workbook you want to display.

To arrange all open workbooks in the program window, page 27

1.	On the View tab, in the Window group, click Arrange All.
2.	Select the desired arrangement.
3.	Click OK.

To add a button to the Quick Access Toolbar, page 28

1.	Click the Customize Quick Access Toolbar button.
2.	Click More Commands.
3.	Click the Choose Commands From down arrow.
4.	Click the category from which you want to choose the command.
5.	Click the command you want to add.
6.	Click Add.
7.	Click OK.

To move a button on the Quick Access Toolbar, page 28

1.	Click the Customize Quick Access Toolbar button.
2.	Click More Commands.
3.	Click the command you want to move.
4.	Click the Move Up button or the Move Down button.

To remove a button from the Quick Access Toolbar, page 28

1.	Right-click the button you want to remove.
2.	Click Remove from Quick Access Toolbar.

3. Working with Data and Data Tables

To enter a data series using AutoFill, page 36

1.	Type the first label or value for your list.
2.	Drag the fill handle to the cell containing the last label or value in the series.

To change how dragging the fill handle extends a series, page 37

1.	Type the first label or value for your list.
2.	Hold down the key, and drag the fill handle to the cell containing the last label or value in the series.

To enter data by using AutoComplete, page 37

1.	Type the beginning of an entry.
2.	Press to accept the AutoComplete value.

To enter data by picking from a list, page 37

1.	Right-click a cell in a column with existing values and click Pick from Drop-down List from the shortcut menu.
2.	Click the item in the list you want to enter.

To copy and paste cells, page 41

1.	Select the cell you want to copy.
2.	On the Home tab, in the Clipboard group, click Copy.
3.	Click the cells into which you want to paste the values.
4.	On the Home tab, in the Clipboard group, click Paste.

To copy and paste a row or column, page 41

1.	Select the row or column you want to copy.
2.	On the Home tab, in the Clipboard group, click Copy.
3.	Click the header of the row or column into which you want to paste the values.
4.	On the Home tab, in the Clipboard group, click Paste.

To find data within a worksheet, page 44

1.	Click the Home tab.
2.	In the Editing group, click Find and Select.
3.	Click Find.
4.	Type the text you want to find.
5.	Click Find Next.
6.	Click Close.

To replace a value with another value within a worksheet, page 44

1.	Click the Home tab.
2.	In the Editing group, click Find and Select.
3.	Click Replace.
4.	Type the text you want to replace.
5.	Type the text you want to take the place of the existing text.
6.	Click Find Next. Follow any of these steps: Click Replace to replace the text. Click Find Next to skip this instance of the text and move to the next time it occurs. Click Replace All to replace every instance of the text.
7.	Click Close.

To edit a cell's contents by hand, page 44

1.	Click the cell you want to edit.
2.	Select the text you want to edit in the Formula Bar.
3.	Type the new text and press Enter.

To check spelling, page 48

1.	On the Review tab, in the Proofing group, click Spelling. If you are asked whether you want to save your work, do so.
2.	Follow any of these steps: Click Ignore Once to ignore the current misspelling. Click Ignore All to ignore all instances of the misspelled word. Click Add to Dictionary to add the current word to the dictionary. Click the correct spelling and then click Change to replace the current misspelling with the correct word. Click the correct spelling and then click Change All to replace all instances of the current misspelling with the correct word. Click Cancel to stop checking spelling.
3.	Click OK to clear the dialog box that appears after the spelling check is complete.

To look up a word in the Thesaurus, page 48

1.	Select the word you want to look up.
2.	On the Review tab, in the Proofing group, click Thesaurus.

To translate a word to another language, page 49

1.	Select the word you want to look up.
2.	On the Review tab, in the Proofing group, click Translate.

To create a data table, page 53

1.	Type your table headers in a single row.
2.	Type your first data row directly below the header row.
3.	Click any cell in the range from which you want to create a table.
4.	On the Home tab, in the Styles group, click Format as Table.
5.	Click the desired table style.
6.	Verify that Excel identified the data range correctly.
7.	If your table has headers, select the My table has headers check box.
8.	Click OK.

To add rows to a data table, page 53

• Follow either of these steps:
  
  
• Click the cell at the bottom right corner of the data table and press to create a new table row.
  
  
• Type data into the cell below the bottom left corner of the data table and press . Excel will make the new row part of the data table.
  
  

To resize a table, page 53

1.	Click any cell in the table.
2.	Drag the resize handle to expand or contract the table.

To add a Total row to a column, page 53

1.	Click any cell in the table.
2.	On the Design tab, in the Table Style Options group, click Total Row.

To change the Total row summary function, page 54

1.	Click any cell in the table's Total row.
2.	Click the down arrow that appears.
3.	Click the desired summary function.

To rename a table, page 54

1.	Click any cell in the table.
2.	On the Design tab, in the Properties group, type a new value in the Table Name box.

4. Performing Calculations on Data

To create a named range, page 61

1.	Select the cells you want to name.
2.	Click the Name Box on the Formula Bar.
3.	Type the name you want for the range.
4.	Press .

To create a named range from a selection, page 61

1.	Select the cells you want to name as a range. Be sure either the first or last cell contains the name for the range.
2.	On the Formulas tab, in the Defined Names group, click Create from Selection.
3.	Select the check box that represents the cell that contains the range's desired name.
4.	Click OK.

To display the Name Manager, page 61

• On the Formulas tab, in the Defined Names group, click Name Manager.
  
  

To edit a named range, page 62

1.	On the Formulas tab, in the Defined Names group, click Name Manager.
2.	Click the named range you want to edit.
3.	Click the Edit button.
4.	Click the Collapse Dialog button.
5.	Select the cells you want in the range.
6.	Click Close.

To create a formula, page 64

1.	Click the cell into which you want to enter a formula.
2.	Type =.
3.	Type the expression representing the calculation you want to perform.
4.	Press .

To create a formula using the Insert Function dialog box, page 64

1.	On the Formulas tab, in the Function Library group, click Insert Function.
2.	Select the function you want to use, and click OK.
3.	Fill in the Function Arguments dialog box, and click OK.

To use a named range in a formula, page 66

1.	Begin typing the formula.
2.	Type the name of the named range as a function's argument.

To refer to a table column or row in a formula, page 66

1.	Click the cell in which you want to create the formula.
2.	Type =, followed by the function to include in the formula and a left parenthesis; for example, =SUM( would be a valid way to start.
3.	Move the mouse pointer over the header of the table column you want to use in the formula. When the mouse pointer changes to a black, downward-pointing arrow, click the column header.
4.	Type a right parenthesis and press .

To create a formula using Formula AutoComplete, page 67

1.	Begin typing the formula.
2.	Click the desired function from the list that appears.

To create a formula that doesn't change when copied between cells, page 68

1.	Begin typing the formula.
2.	Precede all column and row references with a dollar sign (e.g., $C$4).

To create a formula that does change when copied between cells, page 68

1.	Begin typing the formula.
2.	Type all column and row references without a dollar sign (e.g., C4).

To create a conditional formula, page 71

1.	Click the cell in which you want to enter an IF function.
2.	On the Formulas tab, in the Function Library group, click Logical, and then click IF.
3.	Type a conditional statement that evaluates to true or false.
4.	Type the text you want to appear if the condition is true.
5.	Type the text you want to appear if the condition is false.
6.	Click OK.

To display cells that provide values for a formula, page 76

1.	Click the cell you want to track.
2.	On the Formulas tab, in the Formula Auditing group, click the Trace Precedents button.

To display formulas that use a cell's contents, page 76

1.	Click the cell you want to track.
2.	On the Formulas tab, in the Formula Auditing group, click the Trace Dependents button.

To remove tracer arrows, page 77

1.	Click the cell you want to track.
2.	On the Formulas tab, in the Formula Auditing group, click the Remove Arrows button.

To locate errors in a worksheet, page 77

1.	On the Formulas tab, in the Formula Auditing group, click the Error Checking button.
2.	Click the Edit in Formula Bar button.
3.	Edit the formula.
4.	Click the Next button to view the next error.

To step through a formula to locate an error, page 78

1.	Click the cell with the formula you want to evaluate.
2.	On the Formulas tab, in the Formula Auditing group, click Evaluate Formula.
3.	Click Evaluate (one or more times) to move through the formula's elements.
4.	Click Close.

To watch a value in a cell, page 78

1.	On the Formulas tab, in the Formula Auditing group, click Watch Window.
2.	Click Add Watch.
3.	Select the cells you want to watch.
4.	Click Add.
5.	Click Watch Window.

To delete a watch, page 79

1.	On the Formulas tab, in the Formula Auditing group, click Watch Window.
2.	Click the watch you want to delete.
3.	Click Delete Watch.
4.	Click the Close button.

5. Changing Document Appearance

To change a cell's font, font style, font color, or background color, page 87

1.	Select the cells you want to change.
2.	On the Home tab, use the controls in the Font group to format the cells.

To add a border to a cell, page 87

1.	Select the cells around which you want to draw a border.
2.	On the Home tab, in the Font group, click the Border button's down arrow.
3.	Click the type of border you want to apply.

To apply a style to a cell, page 92

1.	Select the cells you want to change.
2.	On the Home tab, in the Styles group, click Cell Styles.
3.	Click a style.

To create a new style, page 92

1.	On the Home tab, in the Styles group, click Cell Styles.
2.	Click New Cell Style.
3.	Type a new style name.
4.	Click Format.
5.	Specify the formatting you want this style to contain.
6.	Click OK twice.

To delete a style, page 93

1.	On the Home tab, in the Styles group, click Cell Styles.
2.	Right-click the style you want to delete.
3.	Click Delete.

To copy a cell's formatting onto another cell, page 94

1.	Click the cell that contains the format you want to apply to another cell.
2.	On the Home tab, in the Clipboard group, click the Format Painter button.
3.	Select the cells to which you want to apply the formatting.

To apply a workbook theme, page 97

1.	On the Page Layout tab, in the Themes group, click Themes.
2.	Click the theme you want to apply.

To change theme fonts, colors, and graphic effects, page 98

• Using the Controls on the Page Layout tab, in the Themes group, follow one of these steps:
  
  
  • Click the Fonts button and select a new font.
    
    
  • Click the Colors button and select a new color set.
    
    
  • Click the Effects button and select a new default effect.
    
    

To save a workbook's format as a new theme, page 98

1.	Format your worksheet using the colors, fonts, and effects you want to include in your theme.
2.	On the Page Layout tab, in the Themes group, click Themes.
3.	Click Save Current Theme.
4.	Type a name for your theme.
5.	Click Save.

To create a new table style, page 98

1.	On the Home tab, in the Styles group, click Format as Table and then click New Table Style.
2.	In the Name field, type a name for the table style.
3.	In the Table Element list, click the element you want to format.
4.	Click Format, and use the controls in the Format dialog box to format the table element.
5.	Click OK.
6.	Repeat as desired to format other elements, and then click OK.

To format a cell value as a phone number, page 102

1.	On the Home tab, click the Number group's dialog box expander.
2.	Click Special.
3.	Click Phone Number.
4.	Click OK.

To format cell data as a currency value, page 103

• On the Home tab, in the Number group, click the Accounting Number Format button.
  
  

To select a foreign currency symbol, page 103

1.	On the Home tab, in the Number group, click the Accounting Number Format button's down arrow.
2.	Click the currency symbol you want to apply.

To add words to a cell's value, page 103

1.	On the Home tab, click the Number group's dialog box expander.
2.	Click Custom.
3.	Click the format to serve as the base for your custom format.
4.	Type the text to appear in the cell, enclosed in quotes (e.g., "cases").
5.	Click OK.

To apply a conditional format to a cell, page 107

1.	Select the cells you want to change.
2.	On the Home tab, in the Styles group, click Conditional Formatting.
3.	Click New Rule.
4.	Click Format Only Cells That Contain.
5.	Click the Comparison Phrase down arrow, and then click the comparison phrase you want.
6.	Type the constant values or formulas you want evaluated.
7.	Click Format.
8.	Specify the formatting you want and click OK twice.

To edit a conditional formatting rule, page 109

1.	Select the cells that contain the rule you want to edit.
2.	On the Home tab, in the Styles group, click Conditional Formatting.
3.	Click Manage Rules.
4.	Click the rule you want to change.
5.	Click Edit Rule.
6.	Use the controls to make your changes.
7.	Click OK twice to save your changes.

To delete a conditional formatting rule, page 109

1.	Select the cells that contain the rule you want to edit.
2.	On the Home tab, in the Styles group, click Conditional Formatting.
3.	Click Manage Rules.
4.	Click the rule you want to delete.
5.	Click Delete Rule.
6.	Click OK.

To display data bars in one or more cells, page 109

1.	Select the cells that contain your data.
2.	On the Home tab, in the Styles group, click Conditional Formatting.
3.	Point to Data Bars.
4.	Click the data bar option you want to apply.

To display a color scale in one or more cells, page 109

1.	Select the cells that contain your data.
2.	On the Home tab, in the Styles group, click Conditional Formatting.
3.	Point to Color Scales.
4.	Click the color scale pattern you want to apply.

To display icon sets in one or more cells, page 109

1.	Select the cells that contain your data.
2.	On the Home tab, in the Styles group, click Conditional Formatting.
3.	Point to Icon Sets.
4.	Click the icon set you want to apply.

To add a picture to a worksheet, page 113

1.	On the Insert tab, in the Illustrations group, click Picture.
2.	Double-click the picture you want to insert.

To change a picture's characteristics, page 113

1.	Click the picture.
2.	Use the controls on the Format tab to edit the picture.

6. Focusing on Specific Data Using Filters

To apply a filter to a worksheet, page 122

1.	Click any cell in the range you want to filter.
2.	On the Data tab, in the Sort & Filter group, click Filter.
3.	Click the filter arrow for the column by which you want to filter your worksheet.
4.	Select the check boxes next to the values by which you want to filter the list.
5.	Click OK.

To clear a filter, page 122

1.	Click any cell in the filtered range.
2.	On the Data tab, in the Sort & Filter group, click Clear.

To display the top or bottom values in a column, page 124

1.	Click the filter arrow at the top of the column by which you want to filter the list.
2.	Click Number Filters.
3.	Click Top 10.
4.	Select whether to display the top or bottom values.
5.	Select how many values to display.
6.	Select whether the value in the middle box represents the number of items to display, or the percentage of items to display.

To create a custom filter, page 124

1.	Click any cell in the list you want to filter.
2.	If necessary, on the Data tab, in the Sort & Filter group, click Filter to display the filter arrows.
3.	Click the filter arrow of the column for which you want to create a custom filter.
4.	Point to Text Filters.
5.	Click Custom Filter.
6.	Click the Comparison Operator down arrow, and then click the comparison you want to use.
7.	Type the value by which you want to compare the values in the selected column.
8.	Click OK.

To generate a random value, page 127

• Type the formula =RAND().
  
  

To generate a random value between two other values, page 127

• Type the formula =RANDBETWEEN(low, high), replacing low and high with the lower and upper bound of values you want to generate.
  
  

To summarize data quickly using AutoCalculate, page 128

1.	Select the cells you want to summarize.
2.	View the summary on the status bar, at the bottom right of the Excel program window.

To summarize filtered data using a SUBTOTAL formula, page 128

• Type the formula =SUBTOTAL(function, ref), replacing function with the desired summary function, and ref with the cell range you want so summarize.
  
  

To find list rows that contain unique values, page 130

1.	Select the cells in which you want to find unique values.
2.	On the Data tab, in the Sort & Filter group, click Advanced.
3.	Select the Unique Records Only check box.
4.	Click OK.

To create a validation rule, page 132

1.	Select the cells you want to validate.
2.	On the Data tab, in the Data Tools group, click the Data Validation button's down arrow.
3.	Click Data Validation.
4.	Click the Allow down arrow, and click what type of data you want to allow.
5.	Click the Data down arrow, and click the condition for which you want to validate.
6.	Type the appropriate values in the boxes.
7.	Click the Input Message tab.
8.	Select the Show Input Message When Cell Is Selected check box.
9.	Type the message you want to appear when the cell is clicked.
10.	Click the Error Alert tab.
11.	Select the Show Error Alert After Invalid Data is Entered check box.
12.	Click the Style down arrow, and click the icon you want to appear next to your message.
13.	Type a title for the error message box.
14.	Type the error message you want.
15.	Click OK.

To identify which cells contain invalid data, page 133

• On the Data tab, in the Data Tools group, click the Data Validation button's down arrow and then click Circle Invalid Data.
  
  

To turn off data validation in a cell, page 134

• On the Data tab, in the Data Tools group, click the Data Validation button's down arrow and then click Clear Invalidation Circles.
  
  

7. Reordering and Summarizing Data

To sort a data list, page 140

1.	Click any cell in the column by which you want to sort your data.
2.	On the Data tab, in the Sort & Filter group, follow either of these steps: Click the Sort Ascending button in the Sort & Filter group on the ribbon. Click the Sort Descending button in the Sort & Filter group on the ribbon.

To sort a data list by values in multiple columns, page 141

1.	Select a cell in the data list or table you want to sort.
2.	On the Data tab, in the Sort & Filter group, click Sort.
3.	Click the Sort By down arrow and then click the first column by which you want to sort.
4.	Click the Sort On down arrow and then click the criteria by which you want to sort.
5.	Click the Order down arrow.
6.	Select the A to Z item or the Z to A item to indicate the order into which the column's values should be sorted.
7.	Click Add Level.
8.	If necessary, repeat steps 37 to set the columns and order for additional sorting rules.
9.	Click OK.

To add a sorting level, page 142

1.	Select a cell in the data list or table you want to sort.
2.	On the Data tab, in the Sort & Filter group, click Sort.
3.	Click Add Level, and define the sort using the tools in the dialog box.

To delete a sorting level, page 142

1.	Select a cell in the sorted data list.
2.	On the Data tab, in the Sort & Filter group, click Sort.
3.	Click the level you want to delete.
4.	Click Delete Level.

To create a custom list for sorting, page 142

1.	Click the Microsoft Office Button.
2.	Click Excel Options.
3.	Click Popular.
4.	Click Edit Custom Lists.
5.	Click New List.
6.	Type the custom list you want. Separate each entry by pressing .
7.	Click Add.
8.	Click OK twice to close the Custom Lists dialog box and the Excel Options dialog box.

To sort worksheet data by a custom list of values, page 143

1.	Click any cell in the list you want to sort.
2.	On the Data tab, in the Sort & Filter group, click Sort.
3.	Click the Sort By down arrow and then click the column you want to sort by.
4.	Click the Sort On down arrow and then click the criteria you want to sort by.
5.	Click the Order down arrow and then click Custom List.
5.	Click a custom list.
7.	Click OK to close the Custom Lists dialog box.
8.	Click OK to sort the data list.

To organize worksheet data into groups, page 147

1.	Click any cell in the range you want to group.
2.	On the Data tab, in the Outline group, click Subtotal.
3.	Click the At Each Change In down arrow, and then click the value on which you want to base the subtotals.
4.	Click the Use Function down arrow, and then click the subtotal function you want to use.
5.	Select which columns should have subtotals calculated.
6.	Click OK.

To show and hide levels of detail in a grouped data list, page 149

• Follow either of these steps:
  
  
  • Click the Show Detail control on a hidden grouping level to display that level's contents.
    
    
  • Click the Hide Detail control to hide rows that are currently displayed.
    
    

To remove grouping levels from a data list, page 150

1.	Click any cell in the subtotaled range.
2.	On the Data tab, in the Outline group, click Subtotal, and then click Remove All.

To look up data in a data list, page 153

1.	Create a sorted data list or data table that has column headers.
2.	Create a VLOOKUP formula of the form =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup).
3.	Type a value in the cell referred to by the lookup_value argument.

8. Combining Data from Multiple Sources

To create a workbook template, page 160

1.	Click the Microsoft Office Button, and click Save As.
2.	Click the Save As Type down arrow, and click Excel Template.
3.	Type the name you want for the template.
4.	Click Save.

To create a new workbook that is based on a template, page 161

1.	Click the Microsoft Office Button, and click New.
2.	Click Installed Templates, and double-click the template you want to use to create your workbook.
3.	Click the Microsoft Office Button, and click Save As.
4.	Type a name for the file.
5.	Click the Save As Type down arrow, and click Excel Workbook.
6.	Click Save.

To create a worksheet template, page 162

1.	Remove all but one worksheet from a workbook, and format the worksheet as you want the template to appear.
2.	Click the Microsoft Office Button, and click Save As.
3.	Click the Save As Type down arrow, and click Excel Template.
4.	Type the name you want for the template.
5.	Click Save.

To add a template-based worksheet to a workbook, page 162

1.	Right-click a sheet tab, and then click Insert.
2.	On the Spreadsheet Solutions tab page, click the template you want to use.
3.	Click OK.

To create a link between two cells, page 167

1.	In the cell you want to be target of the link, type =, but do not press .
2.	On the View tab, in the Window group, click Switch Windows, and then click the workbook that contains the data for your target cell.
3.	Click the cell that contains the data, and press .

To open multiple workbooks simultaneously, page 175

1.	Open the workbooks you want to open simultaneously.
2.	On the View tab, in the Window group, click Save Workspace.
3.	Type a name for the workspace.
4.	Click Save.

9. Analyzing Alternative Data Sets

Define an alternative data set, page 180

1.	On the Data tab, in the Data Tools group, click What-If Analysis and then click Scenario Manager.
2.	Click Add.
3.	In the Scenario Name field, type a name for the scenario.
4.	At the right edge of the Changing cells field, click the Contract Dialog button.
5.	Select the cells to change, and then click the Expand Dialog button.
6.	Click OK.
7.	Type new values for the cells, and then click OK.
8.	Click Close.

To change a worksheet's values using a scenario, page 183

1.	On the Data tab, in the Data Tools group, click What-If Analysis and then click Scenario Manager.
2.	Click the scenario you want to display.
3.	Click Show.

To summarize the values in multiple scenarios, page 184

1.	On the Data tab, in the Data Tools group, click What-If Analysis and then click Scenario Manager.
2.	Click Summary.
3.	Verify that the Scenario summary option button is selected and that the correct cells appear in the Result cells field.
4.	Click OK.

To determine the required inputs for a formula to generate a specific result, page 188

1.	On the Data tab, in the Data Tools group, click What-If Analysis and then click Goal Seek.
2.	In the Set cell field, type the address of the cell that contains the formula you want to generate a target value.
3.	In the To value field, type the target value.
4.	In the By changing cell field, type the cell that contains the value you want to vary.
5.	Click OK.
6.	Click Cancel to close the Goal Seek dialog box without saving your changes.

To analyze data by using Descriptive Statistics, page 197

1.	On the Data tab, in the Analysis group, click Data Analysis.
2.	Click Descriptive Statistics and then click OK.
3.	Click in the Input Range field and then select the cells you want to summarize.
4.	Select the Summary Statistics check box.
5.	Click OK.

10. Creating Dynamic Lists with PivotTables

To create a PivotTable from a data list, page 204

1.	Click any cell in the data table.
2.	On the Insert tab, in the Tables group, click PivotTable.
3.	Verify that the proper table name or cell range appears in the Table/Range field and that the New Worksheet option button is selected.
4.	Click OK.
5.	In the PivotTable Field List task pane, drag the available fields to the desired spots in the PivotTable.

To pivot a PivotTable, page 208

• In the PivotTable Field List task pane, drag a field header to a new position.
  
  

To filter a PivotTable, page 211

1.	On the PivotTable worksheet, click any cell in the PivotTable.
2.	In the PivotTable Field List task pane's Choose fields to add to report section, click the target field header, and then click the down arrow next to the field header and clear the (Select All) check box.
3.	Select the check boxes of the values you do want to show, and then click OK.

To show or hide the PivotTable Field List task pane, page 213

1.	Click any cell in the PivotTable.
2.	On the Options tab, in the Show/Hide group, click the Field List button.

To show or hide levels of detail within a PivotTable, page 214

• In the body of the PivotTable, follow either of these steps:
  
  
  • Click the Show Detail control to display hidden rows.
    
    
  • Click the Hide Detail control to hide rows displayed in the PivotTable.
    
    

To rename a PivotTable, page 218

1.	On the PivotTable worksheet, click any cell in the PivotTable.
2.	On the Options contextual tab, in the PivotTable group, in the PivotTable Name field, type a new name for the PivotTable.

To control how and where subtotals and grand totals appear in your PivotTable, page 218

1.	On the PivotTable worksheet, click any cell in the PivotTable.
2.	On the Design contextual tab, in the Layout group, click Subtotals, and then click the option representing how you want subtotals to appear in your PivotTable.
3.	On the Design contextual tab, in the Layout group, click Grand Totals, and then click the option representing how you want grand totals to appear in your PivotTable.

To change the PivotTable summary function, page 219

• Right-click any data cell in the PivotTable, point to Summarize Data By, and then click the desired summary function.
  
  

To apply a number format to a PivotTable, page 223

1.	On the PivotTable worksheet, right-click any data cell and then click Number Format.
2.	In the Category list, click Number.
3.	Use the controls on the Number tab to create your format.
4.	Click OK.

To apply a conditional format to a PivotTable, page 224

1.	Select the cell ranges you want to format.
2.	On the Home tab, in the Styles group, click Conditional Formatting, point to the type of conditional format you want to use, and then click the specific format you want to apply.

To apply a PivotTable Style to a PivotTable, page 225

1.	Click any cell in the PivotTable.
2.	On the Design tab, in the PivotTable Styles gallery, click the style you want to apply to the PivotTable.

To create a new PivotTable style, page 225

1.	Click any cell in the PivotTable.
2.	On the Design contextual tab, in the PivotTable Styles group, click the More button at the bottom-right corner of the style gallery.
3.	Click New PivotTable Style.
4.	In the Name field, type a name for the style.
5.	In the Table Element list, click the element you want to change, and then click Format.
6.	Use the controls in the Format Cells dialog box to format the element.
7.	If desired, repeat Step 6 for other elements.
8.	Click OK twice.

To import data from an external source, page 231

1.	On the Data tab, in the Get External Data group, click From Text.
2.	Navigate to the folder that contains the source file, and double-click the file.
3.	Verify that the Delimited option button is selected and then click Next.
4.	In the Delimiters section, verify that the correct check box is selected and also verify that the data displayed in the Data preview area reflects the structure you expect.
5.	Click Finish.

11. Creating Charts and Graphics

To create a chart, page 238

1.	Click any cell in the data table.
2.	On the Insert tab, in the Charts group, click the desired chart type, and then click the desired chart subtype.

To change how Excel plots your data, page 239

1.	On the Design tab, in the Data group, click Select Data.
2.	In the Legend Entries (Series) area, click the data series you want to change.
3.	Click Remove.
4.	In the Horizontal (Categories) Axis Labels area, click Edit.
5.	Select the cells you want to plot on this axis and then click OK.

To remove a series from an axis, page 240

1.	On the Design tab, in the Data group, click Select Data.
2.	In the Legend Entries (Series) area, click the data series you want to remove from the chart.
3.	Click Remove.

To add a series to an axis, page 240

1.	On the Design tab, in the Data group, click Select Data.
2.	In the Legend Entries (Series) area, click Add.
3.	In the Series name box, type a name for the series.
4.	Click in the Series values box, and select the cells to provide values for the series.
5.	Click OK.

To move a chart to its own worksheet, page 241

1.	Click the chart.
2.	On the Design tab, in the Location group, click Move Chart.
3.	Select the target sheet for the chart, and click OK.

To apply a Chart Style to a chart, page 244

1.	Click the chart.
2.	On the Design tab, in the Chart Styles gallery, click the style you want to apply.

To apply a different layout to a chart, page 244

1.	Click the chart.
2.	On the Design tab, in the Chart Layouts gallery, click the layout you want to apply.

To change the appearance of a chart's gridlines, page 245

1.	Click the chart.
2.	On the Layout tab, in the Axes group, click Gridlines, and then click the gridline settings you want.

To select a chart element for formatting, page 245

1.	Click the chart.
2.	On the Layout tab, in the Current Selection group, click the Chart Elements down arrow, and then click the element you want to select.

To select a data point in a series, page 245

1.	Click the chart.
2.	Click any point in the data series.
3.	Click the specific data point you want to select.

To format a chart element, page 245

1.	Select the chart element you want to format.
2.	On the Layout tab, in the Current Selection group, click Format Selection.
3.	Use the controls in the Format dialog box to format the chart element.

To save a chart as a chart template, page 246

1.	Click the chart.
2.	On the Design tab, in the Type group, click Save As Template.
3.	Type a name for the template.
4.	Click Save.

To add a trendline to a chart, page 250

1.	Select the chart.
2.	On the Layout contextual tab, in the Analysis group, click Trendline, and then click More Trendline Options.
3.	In the Trend/Regression Type area, select the Linear option button.
4.	In the Forecast area, in the Forward field, type the number of periods you want to project.
5.	Click Close.

To create a PivotChart, page 252

1.	On the data worksheet, click any cell in the data table.
2.	On the Insert tab, in the Tables group, click the PivotTable button down arrow and then click PivotChart.
3.	Verify that the correct data source appears in the Table/Range field and that the New Worksheet option button is selected.
4.	Click OK.

To change the chart type of a chart or PivotChart, page 254

1.	Click the chart.
2.	On the Design contextual tab, in the Type group, click Change Chart Type.
3.	Click the desired chart type and subtype.
4.	Click OK.

To create a SmartArt diagram, page 258

1.	On the Insert tab, in the Illustrations group, click SmartArt.
2.	Click the desired graphic type.
3.	Click the desired subtype and then click OK.

To add text to a diagram shape, page 261

• Click the shape and type the text.
  
  

To add a shape to a diagram, page 261

1.	Click the shape above or to the right of where you want the new shape to appears.
2.	On the Design contextual tab, in the Create Graphic group, click the Add Shape button down arrow and then click the option representing where you want the shape to appear.

To change the format of a diagram shape, page 262

1.	Right-click the shape, and then click Format Shape.
2.	Use the controls in the Format Shape dialog box to change the shape's appearance.

12. Printing

To display a worksheet in Page Layout View, page 268

• On the View tab, in the Workbook Views group, click Page Layout.
  
  

To add a header or footer to a worksheet, page 268

1.	On the View tab, in the Workbook Views group, click Page Layout.
2.	Follow either of these steps: At the top of the worksheet, click the target header section At the bottom of the worksheet, click the target footer section.
3.	In the active header or footer section, type the text that you want to have appear, and press .

To create an AutoHeader, page 269

1.	On the View tab, in the Workbook Views group, click Page Layout.
2.	At the top of the worksheet, click Click to add header.
3.	Click in the target header section.
4.	On the Design contextual tab, in the Header & Footer Elements group, click the auto text you want to add.

To add an image to a header or footer, page 270

1.	On the View tab, in the Workbook Views group, click Page Layout.
2.	Click the desired header or footer section.
3.	On the Design contextual tab, in the Header & Footer Elements group, click Picture.
4.	Double-click the picture you want to add to the header or footer.

To format an image in a header or footer, page 270

1.	Click the image in the footer and then, on the Design contextual tab, click Format Picture.
2.	Use the controls in the Format Picture dialog box to change the picture's appearance.
3.	Click OK.

To change a worksheet's margins, page 273

• On the Page Layout tab, in the Page Setup group, click Margins and then click the desired margins, or click Custom Margins to enter the margins manually.
  
  

To change a worksheet's page orientation, page 274

• On the Page Layout tab, in the Page Setup group, click Orientation and then click the desired orientation.
  
  

To print a worksheet on a specific number of pages, page 275

1.	On the Page Layout tab, in the Scale to Fit group, click the Width control down arrow and then click the desired number of pages.
2.	On the Page Layout tab, in the Scale to Fit group, click the Height control down arrow and then click the desired number of pages.

To preview a worksheet before printing, page 275

• While displaying the worksheet you want to preview, click the Microsoft Office Button, point to Print, and then click Print Preview.
  
  

To add a page break to a worksheet, page 275

1.	Click the row or header below or to the right of where you want the page break to appear.
2.	On the Page Layout tab, in the Page Setup group, click Breaks and then click Insert Page Break.

To remove a page break from a worksheet, page 276

1.	Click the row or header below or to the right of the page break.
2.	On the Page Layout tab, in the Page Setup group, click Breaks and then click Remove Page Break.

To change the order in which worksheets print, page 277

1.	On the Page Layout tab, click the Page Setup group's dialog box launcher.
2.	If necessary, click the Sheet tab.
3.	In the Page order section, select the desired option button.
4.	Click OK.

To print a worksheet, page 281

• Click the Microsoft Office Button, and then click Print.
  
  

To print part of a worksheet, page 283

1.	On the Page Layout tab, in the Page Setup group, click Print Titles.
2.	At the right edge of the Columns to repeat at left field, click the Collapse Dialog button.
3.	Select the column header of the columns you want to repeat.
4.	At the right edge of the Columns to repeat at left field, click the Expand Dialog button.

To center material on the printed page, page 284

1.	On the Page Layout tab, click the Page Setup group's dialog box launcher.
2.	On the Margins page of the dialog box, select the Horizontally and Vertically check boxes.
3.	Click OK.

To print a chart, page 288

1.	Select the chart.
2.	Click the Microsoft Office Button and then click Print.
3.	Verify that the Selected Chart option button is selected and then click OK (or click Cancel if you don't want to print the chart).

13. Automating Repetitive Tasks with Macros

To save a workbook as a macro-enabled workbook, page 292

1.	Click the Microsoft Office Button, and then click Save As.
2.	Click the Save as type down arrow, and then click Excel Macro-Enabled Workbook (*.xlsm).
3.	Click Save.

To enable macros to run in a workbook, page 293

1.	On the Message Bar, click Options.
2.	Select the Enable this content option button.
3.	Click OK.

To view a macro, page 295

1.	On the View tab, in the Macros group, click the Macros button down arrow and then click View Macros.
2.	Click the macro you want to view, and then click Edit.

To step through a macro, page 296

1.	On the View tab, in the Macros group, click the Macros button down arrow and then click View Macros.
2.	Click the macro you want to view, and then click Step Into.
3.	Press to execute the first macro step.

To run a macro, page 296

1.	On the View tab, in the Macros group, click the Macros button down arrow and then click View Macros.
2.	Click the desired macro, and then click Run.

To record a macro, page 299

1.	On the View tab, in the Macros group, click the Macros button's down arrow, and then click Record Macro.
2.	In Record Macro dialog box, in the Macro name box, delete the existing name, and then type a new name for the macro.
3.	Click OK.
4.	Perform the actions you want to record.
5.	On the View tab, in the Macros group, click the Macros button down arrow, and then click Stop Recording.

To edit a macro, page 300

1.	On the View tab, in the Macros group, click the Macros button down arrow and then click View Macros.
2.	Click the macro you want to view, and then click Edit.
3.	Make any desired changes.
4.	Click the Visual Basic Editor Close button.

To run a macro when a Quick Access Toolbar button is clicked, page 303

1.	On the Quick Access Toolbar, click the Customize Quick Access Toolbar button and then click More Commands.
2.	If necessary, click the Choose commands from down arrow and then click the desired category.
3.	In the Commands panel, click the desired command.
4.	Click Add.
5.	Click the Choose commands from box down arrow and then click Macros.
6.	In the Commands panel, click the macro you want to run.
7.	Click Add.
8.	Click OK.

To change the appearance of a Quick Access Toolbar button, page 305

1.	On the Quick Access Toolbar, click the Customize Quick Access Toolbar button and then click More Commands.
2.	In the Customize Quick Access Toolbar command panel, click the command you want to change.
3.	Click Modify.
4.	Click the desired button design.
5.	Click OK twice to close the Modify Button dialog box and the Excel Options dialog box.

To run a macro when a shape is clicked, page 305

1.	Right-click the shape and then click Assign Macro.
2.	Click the desired macro and then click OK.

To run a macro when a workbook is opened, page 308

• Create a macro named Auto_Open.
  
  

14. Working with Other Microsoft Office System Programs

To link to another Microsoft Office system document, page 314

1.	On the Insert tab, in the Text group, click Object.
2.	Click the Create from File tab.
3.	Click Browse.
4.	Click the file to which you want to link, and then click Insert.
5.	Select the Link to file check box and then click OK.

To embed another document in a workbook, page 317

1.	On the Insert tab, in the Text group, click Object.
2.	Click the Create from File tab.
3.	Click Browse.
4.	Click the file to which you want to link, and then click Insert.
5.	Click OK.

To view a linked or embedded document, page 318

• Double-click the document.
  
  

To create a hyperlink, page 320

1.	On the Insert tab, in the Links group, click Hyperlink.
2.	Click the type of hyperlink you want to create.
3.	If necessary, use the controls in the Look in box to locate the file or location to which you want to link.
4.	In the file list, click the hyperlink's target.
5.	In the Text to display box, type the text you want displayed.
6.	Click OK.

To edit a hyperlink, page 323

1.	Right-click the cell that contains the hyperlink, and then click Edit Hyperlink.
2.	Edit the values in the Hyperlink dialog box.
3.	Click OK.

To delete a hyperlink, page 323

• Right-click the cell that contains the hyperlink, and then click Delete Hyperlink.
  
  

To paste a chart into another document, page 325

1.	Right-click the chart and then click Copy.
2.	Open the destination document.
3.	Right-click a blank spot in the visible slide and click Paste.
4.	Click the Paste Options button and then click the desired option.

15. Collaborating with Colleagues

To turn on workbook sharing, page 330

1.	On the Review tab, in the Changes group, click Share Workbook.
2.	Select the Allow changes by more than one use at the same time. This also allows workbook merging. check box.
3.	Click OK.

To add a comment to a cell, page 333

1.	Click the cell where you want the comment to appear.
2.	On the Review tab, in the Comments group, click New Comment.
3.	Type the comment text, and then click outside the body of the comment.

To edit a comment, page 334

1.	Click the cell that contains the comment.
2.	On the Review tab, in the Comments group, click Edit Comment.
3.	Type the new comment text, and then click outside the body of the comment.

To delete a comment, page 334

1.	Click the cell that contains the comment.
2.	On the Review tab, in the Comments group, click Delete Comment.

To track changes made to a workbook, page 336

1.	On the Review tab, in the Changes group, click Track Changes, and then click Highlight Changes.
2.	Select the Track changes while editing. This also shares your workbook check box.
3.	Click OK.

To accept and reject changes, page 337

1.	On the Review tab, in the Changes group, click Track Changes, and then click Accept/Reject Changes.
2.	Click OK.
3.	Click Accept to accept the change, or click Reject to reject the change. You can also click Accept All or Reject All.

To record workbook changes on a History worksheet, page 337

1.	On the Review tab, in the Changes group, click Track Changes, and then click Highlight Changes.
2.	Select the List changes on a new sheet check box, and click OK.

To require a password to open a workbook, page 340

1.	Click the Microsoft Office Button, and then click Save As.
2.	Click the Tools button, and then click General Options.
3.	Type a password in the Password to open box.
4.	Type a different password in the Password to modify box.
5.	Click OK.
6.	In the Reenter password to proceed box, type the first password.
7.	Click OK.
8.	In the Reenter password to modify box, type the second password.
9.	Click OK.

To password protect a worksheet, page 340

1.	On the Review tab, in the Changes group, click Protect Sheet.
2.	In the Password to unprotect sheet box, type a password.
3.	Clear the Select locked cells and Select unlocked cells check boxes, and then click OK.
4.	In the Reenter password to proceed box, type the password you entered before, and then click OK.

To password protect a cell range, page 341

1.	On the Review tab, in the Changes group, click Allow Users to Edit Ranges.
2.	Click New.
3.	In the Title box, type a title for the range.
4.	In the Range password box, type a password, and then click OK.

To sign a workbook using a digital signature, page 345

1.	Click the Microsoft Office Button, click Prepare, and then click Add a Digital Signature.
2.	Click OK to clear the dialog box that appears.
3.	In the Purpose for signing this document box, type a reason.
4.	Verify that your certificate appears in the Signing as area of the dialog box, and then click Sign.
5.	Click OK.

To publish a workbook to the Web, page 347

1.	Click the Microsoft Office Button, and then click Save As.
2.	In the File name box, type a name for the file.
3.	Click the Save as type down arrow, and then click Web Page.
4.	Click Save.
5.	Click Yes to save the workbook as a web file.