Excel Chapter 9

1
Excel Chapter 9

• Importing Data, Working with Pivot Charts,
  PivotTables, and Trendlines

2
Objectives

• Import data from a text file, an
• Access database, a Web page, and a Word Document
• Transpose data while pasting it
• Convert text to columns
• Replicate formulas
• Explain sharing and collaboration techniques
• Insert, edit, and delete a comment

3
Objectives

• Track changes and share a workbook
• Accept and reject tracked changes made to a
  workbook
• Analyze worksheet data using a PivotTable and a
  PivotChart
• Compare and merge shared workbooks
• Analyze worksheet data using a trendline
• Save a custom view of a worksheet and format a
  worksheet background

4
Planning Ahead

• Analyze the existing workbook and the formats of
  the data to be imported
• Evaluate the various options Excel provides for
  collaboration
• Visualize your workbook in various PivotTable and
  PivotChart layouts
• Identify the trend or trends to analyze with a
  trendline
• Gather workbooks to be merged and then assess any
  differences in the workbooks that are in
  disagreement

5
Starting Excel and Opening a Workbook

• Start Excel
• Open the file FasToast Ovens Analysis from the
  Data Files for Students and then save the
  workbook using the file name, FasToast Ovens
  Analysis1. h
• If the Excel window is not maximized,
  double-click its title bar to maximize it
• If the worksheet window in Excel is not
  maximized, click the Maximize button next to the
  Close button on its title bar to maximize the
  worksheet within Excel

6
Starting Excel and Opening a Workbook
7
Importing data from a Text File into a Worksheet

• With the Repair Revenue Analysis worksheet
  active, if necessary, select cell A3
• Click the Data tab on the Ribbon
• Click the From Text button on the Ribbon to
  display the Import Text File dialog box. If you
  have more than one Excel add-in installed, such
  as Solver, the From Text button may appear on a
  submenu that is displayed when you click the Get
  External Data button. The Get External Data group
  on the Ribbon may be collapsed to a single Get
  External Data button
• When Excel displays the Import Text File dialog
  box, select UDISK 2.0 (E) in the Address bar to
  display the text files on drive E

8
Importing data from a Text File into a Worksheet

• Double-click the file name, FasTo Ovens North
  Dakota Revenue 2008, to display the Text Import
  Wizard - Step 1 of 3 dialog box
• Click the Next button to display the Text Import
  Wizard - Step 2 of 3 dialog box
• When Excel displays the Text ImportWizard - Step
  2 of 3 dialog box, click Comma in the Delimiters
  area to display the data fields correctly in the
  Data preview area
• Click Tab in the Delimiters area to clear the
  check box
• Click the Next button to display the TextImport
  Wizard -Step 3 of 3 dialog box

9
Importing data from a Text File into a Worksheet

• Click the Finish button to display the Import
  Data dialog box
• Click the Properties button
• When Excel displays the External DataRange
  Properties dialog box, click Adjust column
  width in the Data formatting and layout area
  to clear the check box
• Click the OK button
• When Excel displays the Import Data dialog box,
  click the OK button to import the data from the
  text file into the worksheet beginning at cell A3

10
Importing data from a Text File into a Worksheet
11
Importing Data from an Access Table into a
Worksheet

• Select cell A8
• With the Data tab active, click the From Access
  button on the Ribbon to display the Select Data
  Source dialog box
• When Excel displays the Select Data Source dialog
  box, select UDISK 2.0 (E) in the Address bar to
  display the Access database files on drive E
• Double-click the file name, FasToast Ovens South
  Dakota Revenue 2008 in the Name list to display
  the Import Data dialog box
• Click the OK button to import the data in the
  database to a table in the range A8G13

12
Importing Data from an Access Table into a
Worksheet

• Right-click cell A8 and then point to the Table
  on the shortcut menu
• Click Convert to Range
• When Excel displays the Microsoft Office Excel
  dialog box, click the OK button to convert the
  table to a range
• Right-click the row heading for row 8 to display
  the shortcut menu
• Click Delete on the shortcut menu to delete row 8

13
Importing Data from an Access Table into a
Worksheet

• Drag through the column headings of column A
  through G in the column heading area to select
  the columns
• Double-click the column separator in the column
  headings area between columns A and B to change
  the widths of each column to best fit
• If necessary, click the Home tab on the Ribbon
  and then select the range A7G7
• Click the Format Painter button on the Ribbon and
  then drag though the range A8G12 to copy the
  formats of the selected range to the range A8G12
• Select cell A13

14
Importing Data from an Access Table into a
Worksheet
15
Importing Data from a Web Page into a Worksheet

• With cell A13 selected, click the Data tab on the
  Ribbon
• Click the From Web button on the Ribbon to
  display the New Web Query dialog box
• When Excel displays the New Web Query dialog box,
  type e\FasToastOvensNebr askaRevenue2008.htm in
  the Address bar and then click the Go button to
  display the Web page in the preview area
• Click the Click to select this table arrow to
  select the HTML table containing the Nebraska
  repair revenue report
• Click the Import button to display the Import
  Data dialog box and display a marquee around cell
  A13

16
Importing Data from a Web Page into a Worksheet

• Click the Properties button to display the
  External Data Range Properties dialog box
• When Excel displays the External Data Range
  Properties dialog box, click Adjust column
  width in the Data formatting and layout area
  to clear the check box
• Click the OK button
• When Excel displays the Import Data dialog box,
  click the OK button to import the data from the
  Web page into the worksheet beginning at cell A13
• Drag through the row 13 and 14 row headings and
  then right-click to display the shortcut menu
• Click Delete on the shortcut menu to delete rows
  13 and 14, which contained the column headings
  from the Web Page

17
Importing Data from a Web Page into a Worksheet
18
Copying and Transposing Data from a Word Document
to a Worksheet

• Click the Home tab on the Ribbon and then select
  cell A24
• Start Word and then open the Word document named,
  FasToast Ovens Kansas Revenue 2008, from the Data
  Files for Students
• In Word, if necessary, click the Home tab on the
  Ribbon
• Drag through all of the cells in the second
  through last columns in the table in the Word
  document and then click the Copy button on the
  Ribbon to copy the contents of the table to the
  Office Clipboard
• Quit Word and, if necessary, click the FasToast
  Ovens Analysis1 workbook taskbar button to make
  it active
• Click the Paste button arrow on the Ribbon to
  display the Paste menu

19
Copying and Transposing Data from a Word Document
to a Worksheet

• Click Paste Special on the Paste menu to display
  the Paste Special dialog box
• When the Paste Special dialog box appears, click
  Text in the As list
• Click the OK button in the Paste Special dialog
  box to paste the contents of the Office Clipboard
  to the range A24E29
• Select the range A24E29 and then click the Copy
  button on the Ribbon
• Select cell A18
• Click the Paste button arrow on the Ribbon and
  then point to Transpose on the Paste menu
• Click Transpose on the Paste menu to transpose
  and paste the copied cells to a range beginning
  with cell A18

20
Copying and Transposing Data from a Word Document
to a Worksheet
21
Converting Text to Columns

• If necessary, click the Home tab on the Ribbon
• Select the range A24E29 and press the DELETE key
  to delete the range
• Select the range B18F22 and then click the Cut
  button on the Ribbon to copy the range to the
  Office Clipboard
• Select cell C18 and then click the Paste button
  on the Ribbon
• Select the range C17G17 and then click the
  Format Painter button on the Ribbon
• Select the range C18G22 to copy the formats in
  range C17G17 down to the corresponding columns
  of range C18G22

22
Converting Text to Columns

• Select the range A18A22 and then click the Data
  tab on the Ribbon
• Click the Text to Columns button on the Ribbon to
  display the Convert Text to Columns Wizard - Step
  1 of 3 dialog box
• Click the Next button to display the Convert Text
  to Columns Wizard - Step 2 of 3 dialog box
• When Excel displays the Convert Text to Columns
  Wizard - Step 2 of 3 dialog box, click Comma in
  the Delimiters area to display the data fields
  correctly in the Data preview area
• Click Tab in the Delimiters area to clear the
  check box
• Click the Finish button to close the Convert Text
  to Columns Wizard - Step 2 of 3 dialog box and
  separate the data in column A to two columns

23
Converting Text to Columns
24
Replicating Formulas

• Select the range H3I3
• Drag the fill handle down through row 22 to copy
  the two formulas to the range H4I22 and display
  the new values for the Revenue per Repair Person
  2007 and Revenue per Repair Person 2008 columns
• Click the Save button on the Quick Access Toolbar
  to save the workbook using the file name,
  FasToast Ovens Analysis1.

25
Replicating Formulas
26
Sharing a Workbook and Collaborate on a Workbook

• Click the Review tab on the Ribbon
• Click the Share Workbook button on the Ribbon to
  display the Share Workbook dialog box
• When Excel displays the Share Workbook dialog
  box, click Allow changes by more than one user
  at the same time
• Click the OK button
• When Excel displays the Microsoft Office Excel
  dialog box, click the OK button to place the
  workbook in share mode

27
Sharing a Workbook and Collaborate on a Workbook

• If possible, have a classmate open a second copy
  of the workbook
• With a second copy of the workbook open, click
  the Share Workbook button on the Review tab on
  the Ribbon to display the Share Workbook dialog
  box, which lists all users who currently have the
  workbook open
• Click the OK button
• Ask the second workbook user to select G13, enter
  183,540 as the new value, and then save the
  workbook
• Click the Save button on the Quick Access Toolbar
  to display the Microsoft Office Excel dialog box
  indicating that the workbook has been updated
  with changes saved by another user
• Click the OK button

28
Sharing a Workbook and Collaborate on a Workbook

• Point to the blue triangle in cell G13 to display
  the comment indicating the other users changes
• Ask the second user of the workbook to close the
  workbook
• Click the Review tab on the Ribbon and then click
  the Share Workbook button on the Ribbon to
  display the Share Workbook dialog box
• When Excel displays the Share Workbook dialog
  box, click Allow changes by more than one user
  at the same time to clear the check box
• Click the OK button to stop sharing the workbook
• If Excel displays the Microsoft Office Excel
  dialog box, click the Yes button

29
Sharing a Workbook and Collaborate on a Workbook
30
Inserting a Comment

• Right-click cell E13 to display the shortcut menu
• Click Insert Comment on the shortcut menu to open
  a comment box next to the selected cell and
  display a comment indicator in the cell
• When Excel displays the comment box, enter the
  comment
• Click anywhere outside the comment box to close
  the comment box and display a red comment
  indicator in cell E13
• Click the Save button on the Quick Access Toolbar

31
Inserting a Comment
32
Turning on Track Changes

• If necessary, click the Review tab on the Ribbon
  and then click the Track Changes button on the
  Ribbon to display the Track Changes menu
• Click Highlight Changes on the Track Changes menu
  to display the Highlight Changes dialog box
• When Excel displays the Highlight Changes dialog
  box, click Track changes while editing
• If necessary, click all of the check boxes in the
  Highlight which changes area to clear them
• Click the OK button
• When Excel displays the Microsoft Office Excel
  dialog box, click the OK button to save the
  workbook
• Close the workbook

33
Turning on Track Changes
34
Opening a Workbook and Reviewing Tracked Changes

• With Excel active, open the file, FasToast Ovens
  Analysis2, from the Data Files for Students
• Click the Review Tab on the Ribbon and then click
  the Track Changes button on the Ribbon to display
  the Track Changes menu
• Click Highlight Changes on the Track Changes menu
• When Excel displays the Highlight Changes dialog
  box, click When to clear the check box
• Click the OK button
• Click the Show All Comments button on the Ribbon
  to display all comments
• Point to the blue triangle in cell G5 to display
  the comment box with information about the change
  made to the cell G5

35
Opening a Workbook and Reviewing Tracked Changes

• Click the Track Changes button on the Ribbon to
  display the Track Changes menu
• Click Accept/Reject Changes on the Track Changes
  menu
• If necessary, when Excel displays the Select
  Changes to Accept or Reject dialog box, click all
  check boxes to clear them, indicating that all
  changes in the change history file should be
  reviewed
• Click the OK button to display the first tracked
  change
• Click the Accept button
• As Excel displays each change in the Accept or
  Reject Changes dialog box, click the Accept
  button.
• Right-click cell A18 and then click Delete
  Comment on the shortcut menu
• Right-click cell E13 and then click Delete
  Comment on the shortcut menu

36
Opening a Workbook and Reviewing Tracked Changes
37
Saving the Workbook with a New File Name

• Click the Office Button then click Save As on the
  Office Button menu
• When Excel displays the Save As dialog box, type
  FasToast Ovens Analysis3 in the File name text
  box
• Click the Save button in the Save As dialog box
  to save the workbook with the new file name

38
Turning Off Track Changes

• Click the Track Changes button on the Ribbon, and
  then click Highlight Changes on the Track Changes
  menu
• When Excel displays the Highlight Changes dialog
  box, click Track Changes while editing to clear
  the check box
• Click the OK button
• When Excel displays the Microsoft Office Excel
  dialog box asking if the workbook should be made
  exclusive, click the Yes button

39
Creating a PivotTable

• Select cell A3 and then click the Insert tab on
  the Ribbon
• Click the PivotTable button arrow on the Ribbon
  to display the PivotTable menu
• Click PivotTable to display the Create PivotTable
  dialog box
• Click the OK button to create the PivotTable on a
  new worksheet and display the PivotTable Field
  List pane

40
Creating a PivotTable
41
Adding Data to the PivotTable

• Drag the Shop Type field from the Choose fields
  to add to report list to the Row Labels area to
  add the Shop Type field to the PivotTable
• Drag the State field from the Choose fields to
  add to report list below the Shop Type button in
  the Row Labels area to add the State field to the
  PivotTable
• Drag the Revenue per Repair Person 2007 field to
  the Values area to add the field to column B of
  the PivotTable
• Drag the Revenue per Repair Person 2008 field to
  the Values area to add the field to column C of
  the PivotTable

42
Adding Data to the PivotTable
43
Changing the View of a PivotTable

• Drag the Shop Type button in the Row Labels area
  below the State button to group revenue by shop
  type, rather than by state
• Click the Sum of Revenue per Repair Person 2007
  button arrow in the Values area to display the
  shortcut menu
• Click Remove Field on the shortcut menu to remove
  Sum of Revenue per Repair Person 2007 from the
  PivotTable
• Click the Undo button on the Quick Access Toolbar
  to undo the removal of Sum of Revenue per Repair
  Person 2007 column from the PivotTable
• Click the Row Labels AutoFilter button in cell A4
  to display the Row Labels AutoFilter menu

44
Changing the View of a PivotTable

• When Excel displays the Row Labels AutoFilter
  menu, click the check boxes for Kansas and North
  Dakota to clear them
• Click the OK button to display only the Nebraska
  and South Dakota data in the PivotTable
• Click the Undo button on the Quick Access Toolbar
  to display the data for all states

45
Changing the View of a PivotTable
46
Filtering a PivotTable Using a Page Field

• Drag the State button in the Row Labels area to
  the Report Filter area in the PivotTable Field
  List pane to create a new page field in the
  PivotTable
• Click the State AutoFilter button in cell B1,
  click South Dakota on the AutoFilter menu, and
  then click the OK button to display totals for
  South Dakota

47
Filtering a PivotTable Using a Page Field
48
Formatting a PivotTable

• Double-click the Sheet1 tab
• Type PivotTable and then press the ENTER key.
• Select cell A4, right-click the PivotTable tab,
  and then point to Tab Color on the shortcut menu.
• Click Olive Green, Accent 3 (column 7, row 1) in
  the Theme Color area
• If necessary, select a cell in the PivotTable and
  then click the Design tab on the Ribbon

49
Formatting a PivotTable

• Click the PivotTable Styles More button to
  display the PivotTable Styles gallery
• Point to the Pivot Style Medium 11 PivotTable
  style in the PivotTable Styles gallery to view a
  preview of the style in the PivotTable
• Click the Pivot Style Medium 11 PivotTable style
  to apply the style to the PivotTable
• Right-click anywhere in cell B4 to display the
  shortcut menu
• Click Number Format on the shortcut menu to
  display the Format Cells dialog box

50
Formatting a PivotTable

• When Excel displays the Format Cells dialog box,
  if necessary, click Currency in the Category
  list, type 0 in the Decimal places box, and then
  click the OK button
• Right-click anywhere in cell C4 to display the
  shortcut menu
• Click Number Format on the shortcut menu to
  display the Format Cells dialog box
• When Excel displays the Format Cells dialog box,
  click Currency in the Category list, type 0 in
  the Decimal places box, and then click the OK
  button to format the numeric values in the
  PivotTable with the Currency style
• Select cell A9 to deselect the worksheet
• Click the Save button on the Quick Access toolbar
  to save the workbook

51
Formatting a PivotTable
52
Switching Summary Functions in a PivotTable

• Select cell B4 and then drag the State button in
  the Report Filter area above the Shop Type button
  in the Row Labels area
• Right-click cell B4 to display the shortcut menu
• Point to Summarize Data By on the shortcut menu
• Click Average on the Summarize Data By submenu to
  change the numbers in column B from sums to
  averages
• Right-click cell C4 to display the shortcut menu

53
Switching Summary Functions in a PivotTable

• Point to Summarize Data By on the shortcut menu
• Click Average on the Summarize Data By submenu to
  change the numbers in column C from sums to
  averages
• Click cell A19 to deselect the PivotTable and
  close the PivotTable Field List

54
Switching Summary Functions in a PivotTable
55
Creating a PivotChart, Changing the PivotChart
Type, and Formatting the Chart

• Select cell A4
• Click the Options tab on the Ribbon and then
  click the PivotChart button on the Ribbon to
  display the Insert Chart dialog box
• Click Clustered Cylinder (column 1, row 2) in the
  Column chart type gallery
• Click the OK button to add the chart to the
  PivotTable worksheet
• With the chart selected, click the Move Chart
  button on the Ribbon to display the Move Chart
  dialog box
• When Excel displays the Move chart dialog box,
  click New sheet to select it and then type
  PivotChart in the New sheet text box

56
Creating a PivotChart, Changing the PivotChart
Type, and Formatting the Chart

• Click the OK button
• Right-click the PivotChart tab and then point to
  Tab Color on the shortcut menu
• Click Purple, Accent 4 (column 8, row1) to change
  the color of the PivotChart sheet tab
• Click the Layout tab on the Ribbon and then click
  the Chart Title button on the Ribbon to display
  the Chart Title menu
• Point to Above Chart on the Chart Title menu
• Click Above Chart on the Chart Title menu
• When the title is placed above the chart, select
  the title text. Type FasToast Ovens Analysis and
  then click anywhere on the chart to add the title
  to the chart

57
Creating a PivotChart, Changing the PivotChart
Type, and Formatting the Chart
58
Changing the View of a PivotChart

• Drag the Shop Type button in the Row Labels above
  the State button to group the data by state
  within shop type
• Click the PivotTable sheet tab

59
Changing the View of a PivotC hart
60
Closing the Workbook

• Click the workbook Close button
• When Excel displays the Microsoft Office Excel
  dialog box, click the Yes button

61
Comparing Workbooks

• Open the file, FasToast Ovens Travel Expenses,
  from the Data Files for Students
• Open the file, FasToast Ovens Travel Expenses
  Maura, from the Data Files for Students.
• Click the View tab on the Ribbon Click the View
  Side by Side button on the Ribbon to display the
  workbooks side by side. Depending on how previous
  Excel windows were arranged on your computer, the
  workbooks may appear next to each other
  left-to-right
• Use the scroll bar in the top window to scroll
  the FasToast Ovens Travel Expenses Maura
  worksheet

62
Comparing Workbooks

• Click the View Side by Side button on the Ribbon
  again
• Click the workbook Close Window button to close
  the FasToast Ovens Travel Expenses Maura workbook
• If Excel displays the Microsoft Office Excel
  dialog box, click the No button
• If necessary, click the Maximize button in the
  FasToast Ovens Travel Expenses window

63
Comparing Workbooks
64
Merging Workbooks

• Click the Customize Quick Access Toolbar button
  arrow next to the Quick Access Toolbar and then
  click More Commands on the Customize Quick Access
  Toolbar menu
• When the Excel Options dialog box appears, select
  All Commands in the Choose commands from list
• Scroll to the Compare and Merge Workbooks
  command in the list on the left and then select
  it
• Click the Add button to add the Compare and Merge
  Workbooks button to the list on the right side of
  the dialog box
• Click the OK button to add the Compare and Merge
  Workbooks button to the Quick Access Toolbar

65
Merging Workbooks

• Click the Compare and Merge Workbooks button on
  the Quick Access Toolbar to display the Select
  Files to Merge Into Current Workbook dialog box
• Select UDISK 2.0 (E) in the Address bar
• Click FasToast Ovens Travel Expenses Stanley,
  hold down the SHIFT key, and then click FasToast
  Ovens Travel Expenses Maura to select both files
• Click the OK button to merge the workbooks

66
Merging Workbooks
67
Turning Off Workbook Sharing and Saving the
Workbook

• Click the Review tab on the Ribbon and then click
  the Share Workbook button on the Ribbon
• When Excel displays the Share Workbook dialog
  box, click Allow changes by more than one user
  at the same time to clear the check box
• Click the OK button
• If Excel displays the Microsoft Office Excel
  dialog box, click the Yes button
• Click the Save button on the Quick Access Toolbar

68
Adding a Trendline to a Chart

• Select the chart by clicking the shaded area
  within the chart
• Click the Design tab on the Ribbon
• Click the Change Chart Type button on the Ribbon
  to display the Change Chart Type dialog box
• Click the Line with Markers chart type in the
  Change Chart Type dialog box

69
Adding a Trendline to a Chart

• Click the OK button to change the Travel Expenses
  chart to a line with markers chart
• With the chart selected, click the Layout tab on
  the Ribbon
• Click the Trendline button on the Ribbon to
  display the Trendline menu
• Click Linear Forecast Trendline to add a
  trendline to the chart with a 2-period forecast

70
Adding a Trendline to a Chart
71
Saving a Custom View of a Workbook

• Resize the FasToast Ovens Travel Expenses
  workbook window as shown in Figure 991
• Click the View tab on the Ribbon
• Click the Custom Views button on the Ribbon to
  display the Custom Views dialog box
• Click the Add button to display the Add View
  dialog box
• Type Expenses in the Name text box

72
Saving a Custom View of a Workbook

• Click the OK button
• Click the Maximize button on the FasToast Ovens
  Travel Expenses workbook title bar
• Click the Custom Views button on the Ribbon to
  display the Custom Views dialog box
• Click Expenses in the Views list and then click
  the Show button to resize the workbook to the
  size saved in the Expenses view

73
Saving a Custom View of a Workbook
74
Formatting a Worksheet Background

• C lick the Maximize button on the FasToast Ovens
  Travel Expenses workbook title bar
• C lick the Page Layout tab on the Ribbon
• C lick the Background button on the Ribbon to
  display the Sheet Background dialog box
• If necessary, select UDISK 2.0 (E) in the
  Address bar
• Click the file name, FasToast Travel

75
Formatting a Worksheet Background

• C lick the Background button on the Ribbon to
  display the Sheet Background dialog box
• If necessary, select UDISK 2.0 (E) in the
  Address bar
• C lick the file name, FasToast Travel

76
Formatting a Worksheet Background
77
Quitting Excel

• Remove the Compare and Merge Workbooks button
  from the Quick Access Toolbar
• Click the Close button on the right side of the
  title bar
• If Excel displays the Microsoft Office Excel
  dialog box, click the No button

78
Summary

• Import data from a text file, an
• Access database, a Web page, and a Word Document
• Transpose data while pasting it
• C onvert text to columns
• Replicate formulas
• Explain sharing and collaboration techniques
• Insert, edit, and delete a comment

79
Summary

• Track changes and share a workbook
• Accept and reject tracked changes made to a
  workbook
• Analyze worksheet data using a PivotTable and a
  PivotChart
• Compare and merge shared workbooks
• Analyze worksheet data using a trendline
• Save a custom view of a worksheet and format a
  worksheet background

80
Excel Chapter 9 Complete