Microsoft Office Excel 2003 Training

1. Microsoft® Office Excel 2003 Training Using XML in Excel [Note to trainer: For detailed help in customizing this template, see the very last slide. Also, look for additional lesson text in the notes pane of some slides.] Before you begin: This course requires a basic understanding of XML (including schemas and well-formed data) as well as Excel lists. You can download these templates: for more information about XML, “Training presentation—XML: What’s it all about?” and, for more information about lists, “Training presentation: Excel 2003—How to use lists”.[Note to trainer: For detailed help in customizing this template, see the very last slide. Also, look for additional lesson text in the notes pane of some slides.] Before you begin: This course requires a basic understanding of XML (including schemas and well-formed data) as well as Excel lists. You can download these templates: for more information about XML, “Training presentation—XML: What’s it all about?” and, for more information about lists, “Training presentation: Excel 2003—How to use lists”.

2. Using XML in Excel Course contents Overview: XML in Excel 2003 Lesson 1: The Excel approach to XML Lesson 2: Opening XML files as XML lists (Continued on next slide.)

3. Using XML in Excel Course contents, cont’d. Lesson 3: Creating XML maps with the XML Source task pane Lesson 4: Understand the XML Source task pane

4. Using XML in Excel Two new tools in Excel 2003 — XML lists and the XML Source task pane — make it easier than ever to use XML. Overview: XML in Excel 2003 Extensible Markup Language (XML) is now a widely accepted standard for sharing data among computer users and computer systems. New tools in Excel 2003 make it easier to work with XML. Learn how to use these tools to create XML lists and XML maps. XML lists have the same look and feel as regular Excel lists, but they take their data from XML files. XML maps are sets of links between the cells in a worksheet and the elements in an XML schema. You can import data into the map, modify it, and then save the changed data or export it for others to use.Extensible Markup Language (XML) is now a widely accepted standard for sharing data among computer users and computer systems. New tools in Excel 2003 make it easier to work with XML. Learn how to use these tools to create XML lists and XML maps. XML lists have the same look and feel as regular Excel lists, but they take their data from XML files. XML maps are sets of links between the cells in a worksheet and the elements in an XML schema. You can import data into the map, modify it, and then save the changed data or export it for others to use.

5. Using XML in Excel Course goals Create an XML list. Create an XML map. Import and export XML data to and from mapped data cells. Use the icons, controls, and options in the XML Source task pane.

6. Lesson 1 The Excel approach to XML

7. Using XML in Excel The Excel approach to XML An XML map is a set of links that you create between an XML schema and the cells in a worksheet. If you don't have an XML schema, all you need is an XML data file. Excel will infer a schema from the structure of the tags in your data file. Before you start using XML in Excel 2003, you need to understand the concept of XML maps, and how to go about creating one. Before you start using XML in Excel 2003, you need to understand the concept of XML maps, and how to go about creating one.

8. Using XML in Excel Getting started using XML with Excel To work with XML in Excel, you use an XML schema to create what Excel calls an XML map. XML maps link the cells in a worksheet to the items (also called elements) in a schema. You then enter or import data into the mapped cells, work with that data, and then either save or export the data to an XML file.

9. Using XML in Excel Getting started using XML with Excel If you don't have a schema, don't worry. All you need is an XML data file. Excel infers a schema from the structure of the tags in the file and allows you to create a map. You'll see that process in detail later in this course. The rest of this course introduces you to XML maps, and teaches you how to create and use them. [Note to trainer: This course doesn't assume that you already have an XML schema or a data file. If you can connect to the practice, which requires Excel 2003, it provides a workbook and a sample XML data file for you to practice with.]The rest of this course introduces you to XML maps, and teaches you how to create and use them. [Note to trainer: This course doesn't assume that you already have an XML schema or a data file. If you can connect to the practice, which requires Excel 2003, it provides a workbook and a sample XML data file for you to practice with.]

10. Using XML in Excel XML maps, schemas, and data files This illustration shows how a map links the items in a schema to cells in a worksheet. You can create a map by using any of these three sources: The previous sections mentioned that you can't work with XML in Excel unless you first create an XML map. By design, a schema doesn't contain any data. So when you create a map, the cells in the map remain empty until you enter or import data into them. [Note to trainer: Steps—given in either numbered or bulleted lists—are always shown in yellow text.]The previous sections mentioned that you can't work with XML in Excel unless you first create an XML map. By design, a schema doesn't contain any data. So when you create a map, the cells in the map remain empty until you enter or import data into them. [Note to trainer: Steps—given in either numbered or bulleted lists—are always shown in yellow text.]

11. Using XML in Excel Ways to create XML maps Excel 2003 gives you two ways to get started with an XML map: The second lesson in this course shows you how to open data files as XML lists. The second lesson in this course shows you how to open data files as XML lists.

12. Using XML in Excel Ways to create XML maps Create a map by using the XML Source task pane. This is a much more powerful and flexible option because you can create maps from schemas or data files. (When you create an XML list, you can only use a data file.) You can also select just the items from the schema or data file that you want to see. This option is how you take advantage of the power that XML can provide. The third lesson in this course shows you how to use the XML Source task pane. This option is how you take advantage of the power that XML can provide. The third lesson in this course shows you how to use the XML Source task pane.

13. Using XML in Excel Entering, saving, and exporting XML data Excel automatically loads all the data from the source file into your worksheet for you. However, when you create a map by using the XML Source task pane, you either enter data manually or import it from an existing file. You don't need to worry about importing or otherwise entering data when you create an XML list. Whether you enter or import data, Excel behaves normally — you can type numbers or text, press ENTER to move to an adjacent cell, and so on.You don't need to worry about importing or otherwise entering data when you create an XML list. Whether you enter or import data, Excel behaves normally — you can type numbers or text, press ENTER to move to an adjacent cell, and so on.

14. Using XML in Excel Entering, saving, and exporting XML data Keep in mind that the underlying schema specifies the type of data you can enter into a mapped cell. For example, if a schema specifies numeric values for a given cell and you enter text, Excel displays an alert.

15. Using XML in Excel Entering, saving, and exporting XML data When you're ready to save your data, Excel gives you several choices. You'll use these options the most: Excel saves the data, any formatting, and any additional objects (such as images and graphs) to the workbook file. [Note to trainer: Steps—given in either numbered or bulleted lists—are always shown in yellow text.]Excel saves the data, any formatting, and any additional objects (such as images and graphs) to the workbook file. [Note to trainer: Steps—given in either numbered or bulleted lists—are always shown in yellow text.]

16. Using XML in Excel Entering, saving, and exporting XML data When you save files this way, Excel writes the data to a well-formed XML file that other XML users or systems can process. However, Excel removes any formatting and additional objects. When you save files this way, Excel writes the data to a well-formed XML file that other XML users or systems can process. However, Excel removes any formatting and additional objects.

17. Using XML in Excel Suggestions for practice List the ways you can create an XML map. List the ways you can save XML data in Excel.

18. Using XML in Excel Test 1, question 1 You create an XML map when you need to: (Pick one answer.)

19. Using XML in Excel Test 1, question 1: Answer Link cells in a worksheet with items in a schema and make use of XML data.

20. Using XML in Excel Test 1, question 2 You must create a map before you can save a spreadsheet as an XML data file. (Pick one answer.)

21. Using XML in Excel Test 1, question 2: Answer True.

22. Lesson 2 Opening XML files as XML lists

23. Using XML in Excel Opening XML files as XML lists XML lists are new to Excel 2003. Opening an XML data file as an XML list is a quick way to browse the data in that file. XML lists also make it easy for you to sort and filter data and to insert common calculations, such as totals and averages. If you're already familiar with the new Excel list feature, you should feel right at home with an XML list.If you're already familiar with the new Excel list feature, you should feel right at home with an XML list.

24. Using XML in Excel Opening files as XML lists What is an XML list? An XML list has the same look, feel, and features as an Excel list. You can sort, filter, and add common calculations like sums and averages to your data. The only real difference between XML lists and Excel lists is the data source. XML lists use XML data files, while Excel lists take their data from a range of cells in a worksheet. [Note to trainer: The figure at left depicts part of the list that you'll create in the practice session for this lesson. The practice session requires Excel 2003.]XML lists use XML data files, while Excel lists take their data from a range of cells in a worksheet. [Note to trainer: The figure at left depicts part of the list that you'll create in the practice session for this lesson. The practice session requires Excel 2003.]

25. Using XML in Excel Opening files as XML lists When do you use an XML list? You open an XML data file as an XML list when you want to view or work with the data in that file quickly, or when the file contains a small, straightforward data structure. For instance, you might open a file this way just to see if you're looking at the correct data, or when you're in a hurry to find some numbers or calculate a total. Keep in mind, however, that opening an XML data file as an XML list loads all the data from the file into your worksheet. That can give you a large amount of data, and it can expose sensitive data that you don't want others to see.For instance, you might open a file this way just to see if you're looking at the correct data, or when you're in a hurry to find some numbers or calculate a total. Keep in mind, however, that opening an XML data file as an XML list loads all the data from the file into your worksheet. That can give you a large amount of data, and it can expose sensitive data that you don't want others to see.

26. Using XML in Excel Important points about XML lists 1st bulleted item: Excel uses every element in the schema, and you have no control over the map or the amount of data that Excel loads into your worksheet. 2nd bulleted item: You can only save the workbook as a .xls file.1st bulleted item: Excel uses every element in the schema, and you have no control over the map or the amount of data that Excel loads into your worksheet. 2nd bulleted item: You can only save the workbook as a .xls file.

27. Using XML in Excel Suggestions for practice Name the only real difference between XML lists and Excel lists. Describe when you would use an XML list. Describe at least two important points about XML lists. [Note to trainer: With Excel 2003 installed on your computer, you can click the link in the slide to go to an online practice. In the practice, you can work through each of these tasks in Excel, with instructions to guide you. Important: The practices for this course require that you have Microsoft Office Excel 2003, or that you have Microsoft Office Professional Edition 2003. You cannot complete the practices by using the version of Excel in Microsoft Office Standard Edition 2003. If you don’t have Excel 2003, you won’t be able to access the practice instructions.][Note to trainer: With Excel 2003 installed on your computer, you can click the link in the slide to go to an online practice. In the practice, you can work through each of these tasks in Excel, with instructions to guide you. Important: The practices for this course require that you have Microsoft Office Excel 2003, or that you have Microsoft Office Professional Edition 2003. You cannot complete the practices by using the version of Excel in Microsoft Office Standard Edition 2003. If you don’t have Excel 2003, you won’t be able to access the practice instructions.]

28. Using XML in Excel Test 2, question 1 Excel creates an XML map for you when you open a data file as an XML list. (Pick one answer.)

29. Using XML in Excel Test 2, question 1: Answer True.

30. Using XML in Excel Test 2, question 2 You open an XML data file as an XML list when you need to: (Pick one answer.)

31. Using XML in Excel Test 2, question 2: Answer Quickly browse, sort, or filter data.

32. Lesson 3 Creating XML maps with the XML Source task pane

33. Using XML in Excel Using the XML Source task pane Using the XML Source task pane to create a map is the most powerful and flexible way to use XML in Excel 2003. The options in this task pane enable you to be selective about which data you view. Also, any changes you make can be saved to a separate XML data file that other systems and users can process. Also, any changes you make can be saved to a separate XML data file that other systems and users can process.

34. Using XML in Excel Why create XML maps with the task pane? Creating an XML map with the XML Source task pane takes a bit of time and effort, but the results are much more powerful. You can:

35. Using XML in Excel Why create XML maps with the task pane? 1st bulleted item: When you export data or save as XML, you write the data to a separate XML file. In contrast, the XML list option that you explored in the previous lesson writes any changes to the workbook. 2nd bulleted item: [The practice session, which requires Excel 2003, shows you how to refresh data by importing it.] Depending on your situation, your IT department may have already set up a system to automate that process (to refresh data by importing it).1st bulleted item: When you export data or save as XML, you write the data to a separate XML file. In contrast, the XML list option that you explored in the previous lesson writes any changes to the workbook. 2nd bulleted item: [The practice session, which requires Excel 2003, shows you how to refresh data by importing it.] Depending on your situation, your IT department may have already set up a system to automate that process (to refresh data by importing it).

36. Using XML in Excel An example of a map at work Here's a common example of how you might use a map. Say that one of your customers sends purchase orders in the form of XML files created in Microsoft Office Word 2003. The figure at left illustrates the idea.The figure at left illustrates the idea.

37. Using XML in Excel An example of a map at work Your shipping department appreciates the use of XML, but it uses Excel to track all orders and shipments. So, you create a map to make the data usable in Excel. The figure at left illustrates the idea. The figure at left illustrates the idea.

38. Using XML in Excel An example of a map at work In this particular case, the items in the file created with Word are mapped to specific cells in a worksheet. The employees in the shipping department then put the worksheets to use as needed to retrieve ordered items and track shipments. Also, now that you have a map, you can reuse it. For example, as new purchase orders arrive, your system could automatically create a new workbook and attach the map to that workbook. The exact mechanics are up to you or your IT department. The key point to remember is that you can create a map once and then use it as needed.Also, now that you have a map, you can reuse it. For example, as new purchase orders arrive, your system could automatically create a new workbook and attach the map to that workbook. The exact mechanics are up to you or your IT department. The key point to remember is that you can create a map once and then use it as needed.

39. Using XML in Excel Suggestions for practice List a few results you can achieve by creating an XML map with the XML Source task pane. Describe a scenario in which you might use an XML map. [Note to trainer: With Excel 2003 installed on your computer, you can click the link in the slide to go to an online practice. In the practice, you can work through each of these tasks in Excel, with instructions to guide you. Important: The practices for this course require that you have Microsoft Office Excel 2003, or that you have Microsoft Office Professional Edition 2003. You cannot complete the practices by using the version of Excel in Microsoft Office Standard Edition 2003. If you don’t have Excel 2003, you won’t be able to access the practice instructions.][Note to trainer: With Excel 2003 installed on your computer, you can click the link in the slide to go to an online practice. In the practice, you can work through each of these tasks in Excel, with instructions to guide you. Important: The practices for this course require that you have Microsoft Office Excel 2003, or that you have Microsoft Office Professional Edition 2003. You cannot complete the practices by using the version of Excel in Microsoft Office Standard Edition 2003. If you don’t have Excel 2003, you won’t be able to access the practice instructions.]

40. Using XML in Excel Test 3, question 1 You export data when you need to: (Pick one answer.)

41. Using XML in Excel Test 3, question 1: Answer Make the data available to other users or systems.

42. Using XML in Excel Test 3, question 2 When you first create a map, the mapped cells remain empty because: (Pick one answer.)

43. Using XML in Excel Test 3, question 2: Answer Schemas don't contain data.

44. Lesson 4 Understand the XML Source task pane

45. Using XML in Excel Understand the XML Source task pane Now that you've had a chance to use the XML Source task pane, take a deeper look at the controls it provides. You’ll see what the icons in the element list mean, and how to use the various XML Source options.

46. Using XML in Excel The controls in the XML Source task pane You can open the task pane in several ways: After you click Open on the File menu, in the Files of type list, select XML Files (*.xml), locate and select the desired XML file, and then click Open. Click Use the XML Source task pane, and then click OK. When you open the task pane by clicking the Use the XML Source task pane command, Excel automatically creates a new workbook for you. To show or hide the task pane, press CTRL+F1.After you click Open on the File menu, in the Files of type list, select XML Files (*.xml), locate and select the desired XML file, and then click Open. Click Use the XML Source task pane, and then click OK. When you open the task pane by clicking the Use the XML Source task pane command, Excel automatically creates a new workbook for you. To show or hide the task pane, press CTRL+F1.

47. Using XML in Excel The controls in the XML Source task pane List of XML maps that you or others have added to the workbook. Use the list to select a given map. Element list. A hierarchical list of the elements in an XML schema. You can use any of the maps that have been added to a workbook as long as you don't try to map a cell twice. The list uses the same kind of tree structure that you see in Windows Explorer, but it uses different icons. See the next section for more information about the icons. You can use any of the maps that have been added to a workbook as long as you don't try to map a cell twice. The list uses the same kind of tree structure that you see in Windows Explorer, but it uses different icons. See the next section for more information about the icons.

48. Using XML in Excel The controls in the XML Source task pane Set options for using the XML Source task pane. Open the XML Maps dialog box. Before you export data from mapped cells, this option validates your data against the schema used to create the map. For more information about the options, see the third segment in this lesson. Use the dialog box to add, delete, and rename XML maps, just like you did in the practice session for the previous lesson (you would only have been able to do the practice session if you have Excel 2003). In other words, this option helps ensure that you or another user don't accidentally enter invalid data into a mapped cell, and then try to export that invalid data.For more information about the options, see the third segment in this lesson. Use the dialog box to add, delete, and rename XML maps, just like you did in the practice session for the previous lesson (you would only have been able to do the practice session if you have Excel 2003). In other words, this option helps ensure that you or another user don't accidentally enter invalid data into a mapped cell, and then try to export that invalid data.

49. Using XML in Excel What do all those icons mean? There are a number of icons in the XML Source task pane. What icons you see in the task pane depends on the schemas that you use to create maps. The following table lists and describes all of the possible icons in the XML Source task pane. You will probably map child elements most of the time. You will probably map child elements most of the time.

50. Using XML in Excel Icons in the elements list

51. Using XML in Excel Understand the task pane options This option works only when you create a map from a data file. This option works only when you create a map from a data file.

52. Using XML in Excel Understand the task pane options If you were able to do the exercise in Excel, you saw this behavior when you created a map in the first segment of the previous exercise (step 5, to be precise). If you clear this option, Excel uses the names of the repeating elements as headings. If you were able to do the exercise in Excel, you saw this behavior when you created a map in the first segment of the previous exercise (step 5, to be precise). If you clear this option, Excel uses the names of the repeating elements as headings.

53. Using XML in Excel Understand the task pane options

54. Using XML in Excel Suggestions for practice Name both ways that you can open the XML Source task pane. Describe how you can view the options for the XML Source task pane. List a few of the options for the task pane, and what they do.

55. Using XML in Excel Test 4, question 1 This icon represents a required child element: (Pick one answer.)

56. Using XML in Excel Test 4, question 1: Answer False.

57. Using XML in Excel Test 4, question 2 You can add any number of XML maps to a worksheet. (Pick one answer.)

58. Using XML in Excel Test 4, question 2: Answer True.

59. Using XML in Excel Quick Reference Card For a summary of the tasks covered in this course, view the Quick Reference Card.

60. USING THIS TEMPLATE See the notes pane or view the full notes page (View menu) for detailed help on this template. Using This Template This Microsoft PowerPoint® template has training content about using Excel 2003 to create XML lists and XML maps. It's geared for you to present to a group and customize as necessary. This template's content is adapted from the Microsoft Office Online Training course “So that’s how: Using XML in Excel 2003.” Features of the template Title slide: On the very first slide, there are empty brackets over which you should type the name of your company. Or you can delete the text box altogether if you don't want this text. Animations: Custom animation effects are applied throughout. They'll play in previous versions back to Microsoft PowerPoint 2000. They include the entrance effects called Peek and Stretch. To alter them, go to the Slide Show menu, click Custom Animation, and work with the options that appear. Slide transitions: The Wipe Down transition is applied throughout the show. If you want a different one, go to the Slide Show menu, click Slide Transition, and work with the options that appear. Hyperlinks to online course: The template contains links to the online version of this training course. The links take you to the hands-on practice session for each lesson and to the Quick Reference Card that is published for this course. Please take note: You must have Excel 2003 installed to view the hands-on practice sessions. Headers and footers: The template contains a footer that has the course title. You can change or remove the footers in the Header and Footer dialog box (which opens from the View menu). Using This Template This Microsoft PowerPoint® template has training content about using Excel 2003 to create XML lists and XML maps. It's geared for you to present to a group and customize as necessary. This template's content is adapted from the Microsoft Office Online Training course “So that’s how: Using XML in Excel 2003.” Features of the template Title slide: On the very first slide, there are empty brackets over which you should type the name of your company. Or you can delete the text box altogether if you don't want this text. Animations: Custom animation effects are applied throughout. They'll play in previous versions back to Microsoft PowerPoint 2000. They include the entrance effects called Peek and Stretch. To alter them, go to the Slide Show menu, click Custom Animation, and work with the options that appear. Slide transitions: The Wipe Down transition is applied throughout the show. If you want a different one, go to the Slide Show menu, click Slide Transition, and work with the options that appear. Hyperlinks to online course: The template contains links to the online version of this training course. The links take you to the hands-on practice session for each lesson and to the Quick Reference Card that is published for this course. Please take note: You must have Excel 2003 installed to view the hands-on practice sessions. Headers and footers: The template contains a footer that has the course title. You can change or remove the footers in the Header and Footer dialog box (which opens from the View menu).