Exploring Office 2003 - Grauer and Barber

1. Exploring Office 2003 - Grauer and Barber 1 Exploring Microsoft Excel 2003

2. Exploring Office 2003 - Grauer and Barber 2 Objectives Describe how a chart can be used to deliver a message. List several types of charts and describe the purpose of each Distinguish between an embedded chart versus a chart in its own sheet Use the Chart Wizard, and F11, to create and modify a chart

3. Exploring Office 2003 - Grauer and Barber 3 Objectives (continued) Use the Drawing toolbar to enhance a chart by adding lines and objects Distinguish between data series in rows versus columns Differentiate between a stacked-column chart versus a side-by-side column chart Create a Word document that is linked to a worksheet and an associated chart

4. Exploring Office 2003 - Grauer and Barber 4 Case Study: Tax Cuts and Dinner The opening case study is inspired by the recent tax cut debate in Washington. Students will enter the percentage of the bill paid both before and after the rebate and create charts plotting the amounts The solution found on the solution disk shows three charts, each embedded in a chart sheet. You can give the students flexibility on the types of charts to create or you can specify the charts. Encourage them to add text bars, arrows and lines, and other elements to highlight the information on a chart. If they have free rein to create the charts, you may want to have them include a short cover sheet describing the charts and the message they are trying to convey. Notice that the compound chart and the side-by-side column chart use the data table to display the data beneath the chart. It may or may not be your style to interject politics into classroom discussion. If it is, this is an excellent example of how each side of a political debate can manipulate the facts to suit its agenda. Both political parties are guilty of this.The solution found on the solution disk shows three charts, each embedded in a chart sheet. You can give the students flexibility on the types of charts to create or you can specify the charts. Encourage them to add text bars, arrows and lines, and other elements to highlight the information on a chart. If they have free rein to create the charts, you may want to have them include a short cover sheet describing the charts and the message they are trying to convey. Notice that the compound chart and the side-by-side column chart use the data table to display the data beneath the chart. It may or may not be your style to interject politics into classroom discussion. If it is, this is an excellent example of how each side of a political debate can manipulate the facts to suit its agenda. Both political parties are guilty of this.

5. Exploring Office 2003 - Grauer and Barber 5 What is a Chart? A graphic representation of data in a worksheet Chart elements Category labels – descriptive text entries Data points – numeric values Data series: a grouping of data points Graphic representations of data allow decision makers to quickly spot trends and patterns without having to sort through rows and columns of data. Charts are frequently used in presentations. Many organizations have performance measurables. These items are often charted and displayed in each group’s work area.Graphic representations of data allow decision makers to quickly spot trends and patterns without having to sort through rows and columns of data. Charts are frequently used in presentations. Many organizations have performance measurables. These items are often charted and displayed in each group’s work area.

6. Exploring Office 2003 - Grauer and Barber 6 Chart Types Keep it simple Use the appropriate chart type Pie and Exploded pie charts display proportional relationships Column charts display numbers rather than percentages Bar charts display numbers horizontally The most important step in creating a chart is to decide on the intended message. The best-looking chart that conveys no meaning, or the wrong meaning, is useless. The intended message will often dictate the type of chart you create. Pie charts are effective when you want to display proportion; i.e. if you have four regions and want to know how much each contributes to total revenues. The IRS used to put a pie chart in the front of the 1040 booklet showing how much of each dollar of tax revenue went to different areas in the budget. Column charts and bar charts display the actual numbers rather than the percentages. These charts also allow you to plot more than one data series. Some people prefer bar charts, which display the numbers horizontally. They feel it allows them to compare proportionality among the data points more readily than column charts.The most important step in creating a chart is to decide on the intended message. The best-looking chart that conveys no meaning, or the wrong meaning, is useless. The intended message will often dictate the type of chart you create. Pie charts are effective when you want to display proportion; i.e. if you have four regions and want to know how much each contributes to total revenues. The IRS used to put a pie chart in the front of the 1040 booklet showing how much of each dollar of tax revenue went to different areas in the budget. Column charts and bar charts display the actual numbers rather than the percentages. These charts also allow you to plot more than one data series. Some people prefer bar charts, which display the numbers horizontally. They feel it allows them to compare proportionality among the data points more readily than column charts.

7. Exploring Office 2003 - Grauer and Barber 7 Pie Charts Notice the labels for each slice of the pie. They show both the category label and the percentage. This option eliminates the need for a legend.Notice the labels for each slice of the pie. They show both the category label and the percentage. This option eliminates the need for a legend.

8. Exploring Office 2003 - Grauer and Barber 8 Column Charts The pie chart shown earlier was created in a chart sheet. This chart is embedded in the worksheet. Notice that the chart is neatly lined up under the data and that the text on both axes is legible. Sometimes students will just create the chart and not make it presentable. An illegible chart is much worse than no chart at all. Another common mistake is to not include the row or column headings in the data selected for the chart. This results in helpful category labels such as “Series 1” and “Series 2”. Use the mouse to select a non-contiguous cell range for the chart.The pie chart shown earlier was created in a chart sheet. This chart is embedded in the worksheet. Notice that the chart is neatly lined up under the data and that the text on both axes is legible. Sometimes students will just create the chart and not make it presentable. An illegible chart is much worse than no chart at all. Another common mistake is to not include the row or column headings in the data selected for the chart. This results in helpful category labels such as “Series 1” and “Series 2”. Use the mouse to select a non-contiguous cell range for the chart.

9. Exploring Office 2003 - Grauer and Barber 9 Creating A Chart Two ways to create Embed chart in worksheet Sizing handles allow you to size, move, copy, or delete an embedded chart Create in separate chart sheet Charts are linked to underlying data A change in the data instantly updates the chart(s) created on that data Embedded worksheets allow you to show the chart and its underlying data on the same printed sheet or to display the data and more than one chart on the same sheet. Formatting embedded worksheets tends to be a little more difficult. Charts in separate chart sheets are easily created with the F11 key! Chart sheets are easier to format and tend to print better.Embedded worksheets allow you to show the chart and its underlying data on the same printed sheet or to display the data and more than one chart on the same sheet. Formatting embedded worksheets tends to be a little more difficult. Charts in separate chart sheets are easily created with the F11 key! Chart sheets are easier to format and tend to print better.

10. Exploring Office 2003 - Grauer and Barber 10 Using the Chart Wizard Select the cells that contain the data Click the Chart Wizard button on the standard toolbar Select the chart type Check the data series Complete the chart options Choose the location Don’t forget to include row and/or column headings in the data. These will be used as category labels. Don’t use chart wizard if you are creating a chart a “stand alone” chart, F11 is much faster. It provides a basic column chart (the default chart) that can be customized as you wish. The Chart Wizard walks you step-by-step through creating the chart. If you make a mistake or change your mind on something, just click the Back button.Don’t forget to include row and/or column headings in the data. These will be used as category labels. Don’t use chart wizard if you are creating a chart a “stand alone” chart, F11 is much faster. It provides a basic column chart (the default chart) that can be customized as you wish. The Chart Wizard walks you step-by-step through creating the chart. If you make a mistake or change your mind on something, just click the Back button.

11. Exploring Office 2003 - Grauer and Barber 11 Choose the Chart Type Encourage students to make use of the button that views a sample chart. Many times it alerts students that they selected too much, or not enough, data.Encourage students to make use of the button that views a sample chart. Many times it alerts students that they selected too much, or not enough, data.

12. Exploring Office 2003 - Grauer and Barber 12 Check the Data Series Encourage students to spend time on this tab. Does this look like the message they are trying to convey? If not, experiment with the data series and/or data type. The dropdown at the bottom shows the default Category axis labels. If students forget to select the row with the column headings, they can enter those cells during this step. Show them how to use the Collapse button to select cells.Encourage students to spend time on this tab. Does this look like the message they are trying to convey? If not, experiment with the data series and/or data type. The dropdown at the bottom shows the default Category axis labels. If students forget to select the row with the column headings, they can enter those cells during this step. Show them how to use the Collapse button to select cells.

13. Exploring Office 2003 - Grauer and Barber 13 Complete the Chart Options A descriptive title is a very important feature for a useful chart. The easiest way to add the chart title is by using the Wizard; however, a title can be manually added after the chart has been created. Explore the tabs on this dialog in class. Pay particular attention to the Legend and Data Labels tabs. If you plan to create the chart on a separate sheet, the Data Table tab places the data underneath the appropriate data series on the chart. I would not use a data table on a chart that is going to be embedded.A descriptive title is a very important feature for a useful chart. The easiest way to add the chart title is by using the Wizard; however, a title can be manually added after the chart has been created. Explore the tabs on this dialog in class. Pay particular attention to the Legend and Data Labels tabs. If you plan to create the chart on a separate sheet, the Data Table tab places the data underneath the appropriate data series on the chart. I would not use a data table on a chart that is going to be embedded.

14. Exploring Office 2003 - Grauer and Barber 14 Choose the Location If you’re going to create the chart in a chart sheet, give the sheet a descriptive name so that you don’t end up with sheets named Chart1, Chart2, etc. If you forget, rename the sheet the same way you rename any worksheet. Better yet, if you intend to create the chart in a chart sheet, don’t use chart wizard because F11 is much faster! Charts can actually be embedded in any worksheet. The default will be the worksheet that was active when the chart wizard was invoked.If you’re going to create the chart in a chart sheet, give the sheet a descriptive name so that you don’t end up with sheets named Chart1, Chart2, etc. If you forget, rename the sheet the same way you rename any worksheet. Better yet, if you intend to create the chart in a chart sheet, don’t use chart wizard because F11 is much faster! Charts can actually be embedded in any worksheet. The default will be the worksheet that was active when the chart wizard was invoked.

15. Exploring Office 2003 - Grauer and Barber 15 Modifying a Chart Add labels, change the data type, or format the chart with the Chart toolbar Add text boxes, arrows and other objects for emphasis with the Drawing toolbar Don’t get too carried away with adding objects to the chart. One or two text boxes and/or arrows can create a good effect, but too much causes the chart to become cluttered and hard to read. Keep it simple!Don’t get too carried away with adding objects to the chart. One or two text boxes and/or arrows can create a good effect, but too much causes the chart to become cluttered and hard to read. Keep it simple!

16. Exploring Office 2003 - Grauer and Barber 16 Enhancing a Chart

17. Exploring Office 2003 - Grauer and Barber 17 Moving and Sizing the Chart When the sizing handle is selected, the chart can be resized. Resizing an embedded chart resizes all the elements in the chart, so there is a lot of experimentation involved.When the sizing handle is selected, the chart can be resized. Resizing an embedded chart resizes all the elements in the chart, so there is a lot of experimentation involved.

18. Exploring Office 2003 - Grauer and Barber 18 Hands-on Exercise 1 Title of Exercise: The Chart Wizard Objective: To create and modify a chart by using the Chart Wizard; to embed a chart within a worksheet; to enhance a chart to include arrows and text. Input file: Software Sales Output file: Software Sales Solution

19. Exploring Office 2003 - Grauer and Barber 19 Multiple Data Series Select multiple data series when you want to see individual data points rather than totals Determine whether data series are in rows or columns Data points plotted are the same either way, but grouping will be different.

20. Exploring Office 2003 - Grauer and Barber 20 Rows Versus Columns If data series are in rows First row is used for category labels Remaining rows are used for data series First column is used for the legend text If data series are in columns First column is used for category labels Remaining columns are used for data series First row is used for legend text

21. Exploring Office 2003 - Grauer and Barber 21 Data Series in Rows Data series are specified in rows, so the data points are grouped by city. The legend text is taken from the first column.Data series are specified in rows, so the data points are grouped by city. The legend text is taken from the first column.

22. Exploring Office 2003 - Grauer and Barber 22 Data Series in Columns The twelve data points are the same, but the grouping is different. Now it is easier to compare by product as opposed to by area. Notice the title of this chart is different than the previous one. We go back to our first point about creating charts. Decide what message you want to convey. Are you comparing by product or by city?The twelve data points are the same, but the grouping is different. Now it is easier to compare by product as opposed to by area. Notice the title of this chart is different than the previous one. We go back to our first point about creating charts. Decide what message you want to convey. Are you comparing by product or by city?

23. Exploring Office 2003 - Grauer and Barber 23 Stacked Column Charts Depicts totals by category instead of each individual data point Each data point is plotted as part of a whole Useful when you want to compare totals by category A stacked column chart simply plots each data point as part of a whole. The second data point in the series is graphed where the first data point leaves off and so forth. A variation on this is the 100% stacked column chart, which combines the proportionality of a pie chart with the ability to plot multiple data series, which you cannot do with a pie chart.A stacked column chart simply plots each data point as part of a whole. The second data point in the series is graphed where the first data point leaves off and so forth. A variation on this is the 100% stacked column chart, which combines the proportionality of a pie chart with the ability to plot multiple data series, which you cannot do with a pie chart.

24. Exploring Office 2003 - Grauer and Barber 24 Example of Stacked Column Chart Each data point is plotted on top of the other data points in that series. This approach shows each data point as well as the total information. Data labels associate the value of each data point.Each data point is plotted on top of the other data points in that series. This approach shows each data point as well as the total information. Data labels associate the value of each data point.

25. Exploring Office 2003 - Grauer and Barber 25 Hands-on Exercise 2 Title of Exercise: Multiple Data Series Objective: To plot multiple data series in the same chart; to differentiate between data series in rows and columns Input file: Software Sales Solution (from first exercise) Output file: Software Sales Solution (additional modifications)

26. Exploring Office 2003 - Grauer and Barber 26 Object Linking and Embedding Create a document in one application that contains objects from another application Embedded object is stored in the document an Excel chart becomes part of the Word document Linked object is stored in its own file any change in this file is automatically reflected in the main document There are advantages to linking and to embedding. If you want to create a memo to a worksheet that frequently changes and you want the values in the worksheet to remain static, embed the worksheet and/or chart. The advantage of a link is that as the underlying worksheet changes, the memo is automatically updated. Linking is a good choice for creating a template. With either, the worksheet can be edited in the application that created it by simply double-clicking the object on the document. The difference between the two is where the document is saved. With an embedded object, it becomes part of the master document. Links sometimes become broken if the file is moved or damaged. In a local network area environment, the link can become broken if the mapping to the network drive is changed, so take care when using links.There are advantages to linking and to embedding. If you want to create a memo to a worksheet that frequently changes and you want the values in the worksheet to remain static, embed the worksheet and/or chart. The advantage of a link is that as the underlying worksheet changes, the memo is automatically updated. Linking is a good choice for creating a template. With either, the worksheet can be edited in the application that created it by simply double-clicking the object on the document. The difference between the two is where the document is saved. With an embedded object, it becomes part of the master document. Links sometimes become broken if the file is moved or damaged. In a local network area environment, the link can become broken if the mapping to the network drive is changed, so take care when using links.

27. Exploring Office 2003 - Grauer and Barber 27 Linking A Worksheet With a linked document, changes to the source document are instantly updated. If they are not updated in the memo, right click on the linked object (the worksheet in this case) and select the Update Link command.With a linked document, changes to the source document are instantly updated. If they are not updated in the memo, right click on the linked object (the worksheet in this case) and select the Update Link command.

28. Exploring Office 2003 - Grauer and Barber 28 Multitasking Multitasking – the ability to have more than one application open at the same time Use the taskbar to switch between open applications

29. Exploring Office 2003 - Grauer and Barber 29 Updating Links Links do become broken. Either you move, rename, or delete a file or, in a LAN environment, the server is replaced or simply remapped to another drive on your computer. You can change the source by clicking the Change Source button. If you have ever worked with linked tables in Microsoft Access, this is similar to using the Linked Table Manager in Access. You can also change the update method so that links are refreshed only when you perform a manual update or never, if you prefer.Links do become broken. Either you move, rename, or delete a file or, in a LAN environment, the server is replaced or simply remapped to another drive on your computer. You can change the source by clicking the Change Source button. If you have ever worked with linked tables in Microsoft Access, this is similar to using the Linked Table Manager in Access. You can also change the update method so that links are refreshed only when you perform a manual update or never, if you prefer.

30. Exploring Office 2003 - Grauer and Barber 30 Hands-on Exercise 3 Title of Exercise: Object Linking and Embedding Objective: To create a compound document consisting of a memo, worksheet, and chart Input file: Software Sales Solution (from second exercise) Output files: Software Sales Solution (additional modifications),Software Memo Solution (Word document)

31. Exploring Office 2003 - Grauer and Barber 31 Summary Charts graphically represent data in a worksheet Select chart type based on the message you are trying to convey Create with the Chart Wizard Can be embedded onto a worksheet or created on a separate chart sheet

32. Exploring Office 2003 - Grauer and Barber 32 Summary (continued) Multiple data series may be specified in either rows or columns Same data points, but different grouping gives different comparison Object Linking and Embedding (OLE) creates a compound document Contains objects from multiple applications Embedding – the object is stored within the compound document Linking – the object is stored as a separate file

33. Exploring Office 2003 - Grauer and Barber 33 End-of-Chapter Exercises Multiple Choice Practice Exercises Exercise 1 – Theme Park Admissions Exercise 2 – Rows versus Columns Exercise 3 – Flexibility in Charting Exercise 4 – Page Break Preview Exercise 5 – Your Next Car Exercise 6 – Irrational Exuberance Exercise 7 – Worksheet References Exercise 8 – Additional Practice

34. Exploring Office 2003 - Grauer and Barber 34 End-of-Chapter Exercises (continued) Mini Cases The Convention Planner Designer Clothing PowerPoint Presentations Exploded Pie Charts

35. Exploring Office 2003 - Grauer and Barber 35 Questions?