1 - 4 Lesson Objectives: • Create charts using MS Excel • Format chart components Charts: MS Excel has the capability to create a variety of graphs. In Excel, a graph is referred to as a chart. Because they are the most commonly used graphs in engineering, we will be concerned mainly with XY (Scatter) (line graphs) and Column charts. Charts are created by opening the Chart Wizard and following the steps indicated. The Chart Wizard will only open if the spreadsheet contains data for graphing. Figure 3-1 shows the Chart Wizard button. Charts can be saved as an object in the current spreadsheet or in a new sheet. A chart is a made-up of many parts. Figure 3-2 identifies all of the chart entities which can be formatted. MS Excel Charts Figure 3-1: Chart Wizard Toolbar Button Figure 3-2: Parts of a Chart Chart Title05101520250246810X-Axis TitleY-Axis TitleSeries1Plot AreaValue (X) Axis GridlinesLegendValue (Y) Axis GridlinesData SeriesChart AreaValue (Y) Axis Value (X) Axis Title2 - 4 Notice that all the parts of the chart are identified in Figure 3-2. All of the entities making up the chart can be formatted. To format a particular entity, press the right mouse button while the cursor is over the entity. The menu shown below appears. Make a selection. In the example below, the first menu entry formats the plot area. Most of the choices are obvious once you inspect the dialog boxes for formatting chart entities. Example 1: Using Excel, create a graph of the function y = 2x + 3 for values of x from 0 to 10. The data points are given in the table below. Data Points x-values y-values 0 3 1 5 2 7 3 9 4 11 5 13 6 15 7 17 8 19 9 21 10 23 The following steps were used to create the chart shown above. 1. Create a table of x-values in Excel. Key-in the 0 and 1, then drag by picking the black box in the lower right corner of the highlighted area. 2. Write a cell formula for the first y-value and copy it. 3. Check a few y-values to verify their correctness. 4. Pick the Chart Wizard toolbar button. 5. The chart type dialog box, similar to Figure 3-4 appears. 6. Pick XY (Scatter) chart type. 7. Pick the sub-type which connects data points with smooth lines without data point markers. 8. Pick next to go to the next dialog box for specifying the source range, This dialog box is shown in Figure 3-5. Format Selected EntityChange Chart Type Switch Chart Location- separate sheet - same sheet as dataChange Titles, Axes, Gridlines, Legend and Data Labels Change & Add Chart Data Values Figure 3-3: Chart Formatting Menu Chart of y = 2x + 30510152025012345678910x-valuesy-values3 - 4 9. Left mouse click in the “Data range” box and highlight the data to be graphed from the spreadsheet. The data from this example was located on Sheet 1 and in cells C6 through D11. Notice that the data table was created in column form. The first column contained the x-values and the second column contained the y-values. 10. Picking “Next” opens the “Chart Options” dialog box. Figure 3-6 shows the Chart Options dialog box. Notice it also has a preview area to show how changes will affect the chart. This is a great tool for discovering what all the options do. In this dialog box, the following steps were taken: a. Using this box, the titles are added. b. X-value major gridlines are added. The y-value major gridlines are shown by default. c. The legend is turned off. It usally is not needed when only one function is graphed. 11. Pick “Next” to go to the last dialog box. Indicate whether the chart is placed as an object in the current sheet, or is created on a separate sheet. On a separate sheet, the chart can be printed on a page by itself. 12. Pick “Finish” to place the chart. Figure 3-4: Chart Type Dialog Box Pick a chart type Pick “Next” to go to the next step in the charting process Chart preview Indicate location of data in this box. The data can be highlighted with the mouse. Data in rows or columns? The Series tab allows the user to graph multiple functions on one chart. Pick “Next” to go to the next step in the charting process Figure 3-5: Source Data Dialog Box Chart type options are shown in this area. The XY (Scatter) chart can be created with: Data points only Smooth curve w/ data points Smooth curve, no data points Straight lines w/ data points Straight lines, no data points4 - 4 Once the chart in this example was placed, the format of several parts was changed. The Plot area was picked, and the background color was changed to “none.” The x-axis was formatted so that the maximum value became 10 instead of 12 and the major unit was changed from 2 to 1. Changing the major unit displayed unit markes incremented by one (0, 1, 2, 3, … )instead of by two (0, 2, 4, … ). Another possible option would have been to keep the major units at 2 and set the minor units to one and displayed the minor axis marks. The results would have been as shown below: Many possibilities exist for formatting chart axes. Most of the steps used in this example apply to all the different Excel charts. Be sure to spend whatever time is necessary to learn the different options so you will quickly be able to create any type of chart. Chart preview Specify titles Turn X and Y axes on or off Turn major and minor gridlines on or off Change Legend location or turn it offDisplay data values or not Figure 3-6: Chart Options Dialog Box Chart of y = 2x +