Excel Project 9

1
Excel Project 9

• Importing Data, Working with XML, PivotCharts,
  PivotTables, and Trendlines

2
Objectives

• Import data from a text file, Access database,
  and Web page
• Import data from XML and work with XML maps
• Replicate formulas
• Export data from a workbook
• Insert, edit, and delete a comment

3
Objectives

• Explain collaboration techniques
• Track changes and share a workbook
• Route a workbook to other users
• Accept and reject tracked changes made to a
  workbook
• Analyze worksheet data using a PivotTable and a
  PivotChart

4
Objectives

• Analyze worksheet data using a trendline
• Compare and merge shared workbooks
• Add a trendline to a chart
• Format a worksheet background and save a custom
  view of a worksheet

5
Importing Data from a Text File into a Worksheet

• Start Excel, open, and make the necessary
  adjustments to the workbook as described on page
  EX 648
• With the Recycli-Fence Analysis worksheet active
  and the Data Disk in drive A, if necessary,
  select cell A3
• Click Data on the menu bar and then point to
  Import External Data on the Data menu
• Click Import Data on the Import External Data
  submenu
• When Excel displays the Select Data Source dialog
  box, click the Look in box arrow and then click
  3½ Floppy (A) in the Look in list

6
Importing Data from a Text File into a Worksheet

• Double-click the file name, NorthSales2005
• Click the Next button
• When Excel displays the Text Import Wizard - Step
  2 of 3 dialog box, click Comma in the Delimiters
  area
• Click Tab in the Delimiters area to clear the
  check box
• Click the Next button

7
Importing Data from a Text File into a Worksheet

• Click the Finish button
• Click the Properties button
• 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

8
Importing Data from a Text File into a Worksheet

• Click the OK button
• When Excel displays the Import Data dialog box,
  click the OK button
• If the External Data toolbar appears, click its
  Close button

9
Importing Data from an Access Table into a
Worksheet

• Select cell A8
• Click Data on the menu bar and then point to
  Import External Data on the Data menu
• Click New Database Query on the Import External
  Data submenu
• If necessary, when Excel displays the Choose Data
  Source dialog box, click the Databases tab and
  then click MS Access Database in the list
• Click the OK button

10
Importing Data from an Access Table into a
Worksheet

• When Excel displays the Select Database dialog
  box, click the Drives box arrow and then click a
  in the Drives list
• Double-click the file name, southsales2005.mdb
• When Excel displays the Query Wizard - Choose
  Columns dialog box, select the SouthSales2005
  table
• Click the Add Table button
• Click the Next button

11
Importing Data from an Access Table into a
Worksheet

• When Excel displays the Query Wizard - Filter
  Data dialog box, click the Next button
• When Excel displays the Query Wizard - Sort Order
  dialog box, click the Next button
• When Excel displays the Query Wizard - Finish
  dialog box, click the Finish button
• Click the Properties button

12
Importing Data from an Access Table into a
Worksheet

• When Excel displays the External Data Range
  Properties dialog box, click Include field names
  and Adjust column width in the Data formatting
  and layout area to clear the check boxes
• Click the OK button in the External Data Range
  Properties dialog box
• When Excel displays the Import Data dialog box,
  click the OK button
• If the External Data toolbar appears, click its
  Close button

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

• Select cell A13
• Click Data on the menu bar and then point to
  Import External Data
• Click New Web Query
• When Excel displays the New Web Query dialog box,
  type a\eastsales2005.htm in the Address box and
  then click the Go button
• Click the Click to select this table arrow

15
Importing Data from a Web Page into a Worksheet

• Click the Import button
• Click the Properties button
• 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

16
Importing Data from a Web Page into a Worksheet

• When Excel displays the Import Data dialog box,
  click the OK button
• If the External Data toolbar appears, click its
  Close button
• Right-click row heading 13
• Click Delete on the shortcut menu

17
Importing Data from a Web Page into a Worksheet
18
Modifying XML View Options and Adding an XML Map
to a Workbook

• Select cell A18
• Click Data on the menu bar and then point to XML
  on the Data menu
• Click XML Source on the XML submenu
• When Excel displays the XML Source pane, click
  the Options button

19
Modifying XML View Options and Adding an XML Map
to a Workbook

• If Excel displays the List toolbar, click its
  Close button
• Click the Hide Border of Inactive Lists command
  on the Options button menu
• Click the XML Maps button
• Click the Add button

20
Modifying XML View Options and Adding an XML Map
to a Workbook

• If necessary, select 3½ Floppy (A) in the Look
  in box
• Click the file name, salesschema.xsd
• Click the Open button
• Click the OK button

21
Modifying XML View Options and Adding an XML Map
to a Workbook
22
Importing Data from an XML File into a Worksheet
Using an XML Map

• Click the SALES element in the element list
• Drag the SALES element to cell A18
• Click anywhere on the worksheet to deselect the
  range
• Right-click the SFATOOL element in cell C18 and
  then point to Delete on the shortcut menu
• Click the Column command on the shortcut menu

23
Importing Data from an XML File into a Worksheet
Using an XML Map

• Click Data on the menu bar and then point to XML
  on the Data menu
• Click the Import command on the XML submenu
• Click the file name, WestSales2005, and then
  click the Import button
• If the Microsoft Office Excel dialog box appears,
  click the OK button

24
Importing Data from an XML File into a Worksheet
Using an XML Map
25
Replicating Formulas

• Select the range H3I3
• Drag the fill handle down through row 22

26
Exporting a File to a Spreadsheet XML File

• If necessary, select cell J1
• Click File on the menu bar and then click Save As
• When Excel displays the Save As dialog box, click
  the Save as type box arrow and then click XML
  Spreadsheet in the Save as type list
• Click the Save button
• Click the Start button on the Windows taskbar,
  point to the All Programs command on the Start
  menu, click Accessories on the All Programs
  submenu, and then click Notepad on the
  Accessories menu

27
Exporting a File to a Spreadsheet XML File

• When the Notepad window appears, click File on
  the menu bar and then click Open on the File menu
• When Notepad displays the Open dialog box, click
  the Look in box arrow and then click 3½ Floppy
  (A) in the Look in list
• Click the Files of Type box arrow and select All
  Files
• Double-click the file name, Recycli-Fence
  Analysis1.xml
• Click the Close button on the right side of the
  Notepad title bar

28
Exporting a File to a Spreadsheet XML File
29
Sharing a Workbook and Collaborating on a Workbook

• Click Tools on the menu bar
• Click Share Workbook on the Tools menu
• 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 Save As dialog box, click
  the OK button

30
Sharing a Workbook and Collaborating on a Workbook

• If possible, have a classmate open a second copy
  of the workbook
• With a second copy of the workbook open, click
  Tools on the menu bar and then click Share
  Workbook on the Tools menu
• Click the OK button
• Ask the second workbook user to select cell G12,
  enter 104,839 as the new value, select G13, enter
  102,384 as the new value, and then save the
  workbook
• Click the Save button on the Standard toolbar

31
Sharing a Workbook and Collaborating on a Workbook

• Click the OK button
• If necessary, scroll the worksheet so that the
  worksheet is displayed as shown in Figure 9-55 on
  page EX 678
• Point to the blue triangle in cell G13
• Ask the second user of the workbook to close the
  workbook

32
Sharing a Workbook and Collaborating on a Workbook

• Click Tools on the menu bar and then click Share
  Workbook on the Tools menu
• When Excel displays the Share Workbook dialog
  box, click the Allow changes by more than one
  user at the same time check box to clear the
  check box
• Click the OK button
• If Excel displays the Microsoft Office Excel
  dialog box, click the Yes button

33
Sharing a Workbook and Collaborating on a Workbook
34
Inserting a Comment

• Right-click cell E13
• Click Insert Comment on the shortcut menu
• When Excel displays the comment box, enter the
  comment as shown on the following slide
• Click anywhere outside the comment box
• Click the Save button on the Standard toolbar

35
Inserting a Comment
36
Turning on Track Changes

• Click Tools on the menu bar and then point to
  Track Changes
• Click Highlight Changes in the Track Changes
  submenu
• When Excel displays the Highlight Changes dialog
  box, click Track changes while editing

37
Turning on Track Changes

• 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

38
Routing the Workbook

• Click File on the menu bar and then point to Send
  To
• Click Routing Recipient on the Send To submenu
• If one or more Microsoft Office Outlook dialog
  boxes are displayed, click the Yes button in each
  dialog box
• If the Choose Profiles dialog box is displayed,
  choose your user profile and then click the OK
  button
• If the Check Names dialog box is displayed, add
  your return address as a new listing to the
  address book

39
Routing the Workbook

• Click the Address button
• When Excel displays the Address Book dialog box,
  click an address in the address list and then
  click the To button. Repeat the process for three
  more recipients
• Click the OK button
• When Excel again displays the Routing Slip dialog
  box, click the Message text text box

40
Routing the Workbook

• Type Please review the attached worksheet and
  make corrections as necessary. When you are
  finished reviewing, route to the next recipient.
  Thank you.
• Click the Route button
• Click the Save button on the Standard toolbar
• Click the workbook Close button on the menu bar

41
Routing the Workbook
42
Opening a Routed Workbook and Reviewing Tracked
Changes

• With Excel active, click the Open button on the
  Standard toolbar
• If necessary, when Excel displays the Open dialog
  box, click the Look in box arrow and then click
  3½ Floppy (A) in the Look in list
• Double-click the file name, Recycli-Fence
  Analysis2
• Click Tools on the menu bar and then point to
  Track Changes on the Tools menu
• Click Highlight Changes on the Track Changes
  submenu

43
Opening a Routed Workbook and Reviewing Tracked
Changes

• When Excel displays the Highlight Changes dialog
  box, click When to clear the check box
• Click the OK button
• Click Tools on the menu bar and then click
  Options on the Tools menu
• If necessary, when Excel displays the Options
  dialog box, click the View tab and then click
  Comment indicator in the Comments area
• Click the OK button

44
Opening a Routed Workbook and Reviewing Tracked
Changes

• Point to the blue triangle in cell F6
• Click Tools on the menu bar and then point to
  Track Changes
• Click Accept or Reject Changes on the Track
  Changes submenu
• If necessary, when Excel displays the Select
  Changes to Accept or Reject dialog box, click all
  check boxes to clear them
• Click the OK button

45
Opening a Routed Workbook and Reviewing Tracked
Changes

• 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

46
Opening a Routed Workbook and Reviewing Tracked
Changes
47
Creating a PivotTable

• Select cell A3
• Click Data on the menu bar
• Click PivotTable and PivotChart Report on the
  Data menu
• Click the Next button

48
Creating a PivotTable

• Click the Next button
• If necessary, when Excel displays the PivotTable
  and PivotChart Wizard - Step 3 of 3, click New
  worksheet
• Click the Finish button
• When Excel displays the PivotTable drop areas and
  the PivotTable toolbar, drag the toolbar up and
  to the right in order to show all of the
  PivotTable drop areas

49
Creating a PivotTable
50
Adding Data to the PivotTable

• Drag the Type button from the PivotTable Field
  List window to the Drop Row Fields Here area
• Drag the Region button from the PivotTable Field
  List window to the right of the Type button
• Drag the Sales per Rep 2005 button to the Drop
  Data Items Here area

51
Adding Data to the PivotTable

• Drag the Sales per Rep 2004 button to the Drop
  Data Items Here area
• Right-click the Data button at the top of the
  PivotTable
• Click Order on the shortcut menu and then click
  Move to Column on the Order submenu

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

• Click the Chart Wizard button on the PivotTable
  toolbar
• Right-click anywhere on the PivotChart and then
  click Chart Options on the shortcut menu
• When Excel displays the Chart Options dialog box,
  type Recycli-Fence Analysis in the Chart title
  text box
• Click the OK button

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

• Right-click anywhere on the PivotChart and then
  click Chart Type on the shortcut menu
• When Excel displays the Chart Type dialog box,
  click the first chart sub-type, Clustered Column
• Click the OK button
• Double-click the Chart1 sheet tab

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

• Type PivotChart and then press the ENTER key
• Right-click the PivotChart tab and then click Tab
  Color on the shortcut menu
• When Excel displays the Format Tab Color dialog
  box, click Purple (column 7, row 5) in the Tab
  Color area
• Click the OK button

55
Creating a PivotChart, Changing the PivotChart
Type, and Formatting the Chart
56
Comparing Workbooks

• Open the file, Recycli-Fence Travel Expenses,
  from the Data Disk
• Open the file, Recycli-Fence Travel Expenses
  Seth, from the Data Disk
• Click Window on the menu bar
• Click Compare Side by Side with Recycli-Fence
  Travel Expenses on the Window menu
• Use the scroll bar on the top window to scroll
  the Recycli-Fence Travel Expenses Seth worksheet

57
Comparing Workbooks

• Click the Close Side by Side button on the
  Compare Side by Side toolbar
• Click the Close Window button on the menu bar to
  close the Recycli-Fence Travel Expenses Seth
  workbook
• If Excel displays the Microsoft Office Excel
  dialog box, click the No button
• If necessary, click the Maximize button on the
  Recycli-Fence Travel Expense window

58
Comparing Workbooks
59
Merging Workbooks

• Click Tools on the menu bar
• Click Compare and Merge Workbooks on the Tools
  menu
• If necessary, click the Look in box arrow and
  then click 3½ Floppy (A) in the Look in list
• Click Recycli-Fence Travel Expenses Maria, hold
  down the SHIFT key, and then click Recycli-Fence
  Travel Expenses Seth
• Click the OK button

60
Merging Workbooks
61
Adding a Trendline to a Chart

• Select the chart by clicking the shaded area
  within the chart
• Click Chart on the menu bar and then click Add
  Trendline
• When Excel displays the Add Trendline dialog box,
  click the Options tab
• Click the Forward box in the Forecast area and
  then type 2 as the new value
• Click the OK button

62
Adding a Trendline to a Chart
63
Saving a Custom View of a Workbook

• Resize the Recycli-Fence Travel Expenses workbook
  window as shown in Figure 9-107 on page EX 714
• Click View on the menu bar
• Click Custom Views on the View menu
• Click the Add button
• Type Expenses in the Name text box

64
Saving a Custom View of a Workbook

• Click the OK button
• Click the Maximize button on the Recycli- Fence
  Travel Expenses workbook title bar
• Click View on the menu bar and then click Custom
  Views on the View menu
• Click Expenses in the Views list and then click
  the Show button

65
Saving a Custom View of a Workbook
66
Formatting a Worksheet Background

• Click the Maximize button on the Recycli-Fence
  Travel Expenses workbook title bar
• Click Format on the menu bar and then point to
  Sheet on the Format menu
• Click Background on the Sheet submenu
• If necessary, click the Look in box arrow and
  then click select 3½ Floppy (A) in the Look in
  list
• Click the file name, Recycli-Fencegif

67
Formatting a Worksheet Background

• Click the Insert button
• Select the range A3H8 and then click the Font
  Color button arrow on the Formatting toolbar
• Click Blue (column 6, row 2) on the Font Color
  palette
• Click cell I10

68
Formatting a Worksheet Background
69
Summary

• Import data from a text file, Access database,
  and Web page
• Import data from XML and work with XML maps
• Replicate formulas
• Export data from a workbook
• Insert, edit, and delete a comment

70
Summary

• Explain collaboration techniques
• Track changes and share a workbook
• Route a workbook to other users
• Accept and reject tracked changes made to a
  workbook
• Analyze worksheet data using a PivotTable and a
  PivotChart

71
Summary

• Analyze worksheet data using a trendline
• Compare and merge shared workbooks
• Add a trendline to a chart
• Format a worksheet background and save a custom
  view of a worksheet

72
Excel Project 9 Complete