Creating Chart Course Materials



Creating Charts

Table of Contents

Using Excel Charts 1

Creating a Default Chart 1

Using the Chart Wizard 1

Chart Toolbar 4

Resizing an Embedded Chart 4

Moving an Embedded Chart 4

Formatting Chart Elements 5

Formatting Colors 5

Using Fill Effects 6

Formatting Axes and Tick-Mark Labels 8

Customizing Chart Options 9

Titles 9

Axes 9

Legend 9

Data Labels 10

Formatting Data Series Labels 11

Formatting a Single Data Point or Data Label 11

Changing the Series Order 12

Changing Column Spacing on Two-Dimensional Charts 12

Adding Error Bars 13

Adding Trendlines to Two-Dimensional Charts 14

Deleting a Trendline 14

Changing the Chart Type 15

Problems with Changing Two-Dimensional Column Charts to Three-Dimensional Column Charts 15

Chart Types 16

Changing the Default Chart Type 18

Creating a Custom Chart Type 19

Applying a User-Defined Chart Type 20

Combining Chart Types 20

Changing 3-D Chart Viewing Angles 20

Adding or Removing Data from a Chart 21

Adding Data 21

Clicking and Dragging 21

Using Copy and Paste 22

Removing Data from a Chart 22

Modifying Chart Source Data 23

Changing the Chart Location 23

Enhancing Your Chart with Text, Arrows, and Drawings 24

Turning on the Drawing Toolbar 24

Adding Arrows, Boxes, Circles, etc. 24

Formatting Shapes 25

Creating a Chart in PowerPoint 26

Formatting the Power Point Chart 27

Activating a PowerPoint Chart 27

Formatting Chart Elements 27

Changing Chart Types in PowerPoint 28

Using Excel Data in your PowerPoint Chart 29

Creating a Chart in Word 29

Resizing the Chart in Word 30

Moving the Chart in Word 30

Adding an Excel Chart to Word 30

Course Objectives

At the end of this course, participants will be able to:

▪ Create a default Excel chart with a shortcut key

▪ Use the Excel chart wizard

▪ Format chart elements

▪ Change the chart type

▪ Create a custom chart type

▪ Change the chart location

▪ Combine chart types

▪ Add or remove chart data

▪ Change the plot order

▪ Add error bars

▪ Add trendlines

▪ Create a chart to PowerPoint

▪ Create a chart in Word

Using Excel Charts

Microsoft Excel 2003 allows you to create sophisticated charts from your worksheet data. There are a wide variety of chart types and sub-types available. You can also combine chart types. For example, you could use a line chart over a column chart to display both stock prices and volume on the same chart.

Creating a Default Chart

You can quickly create a default chart using Excel’s default column chart. Simply select a cell within the data you want to chart, then press the F11 key. (To chart only some cells within a range, select the range of cells you want to chart.) The default chart type is created on a new worksheet. You can then format the chart as necessary. Note: The default chart in Excel is a two-dimensional column chart; however you can change the default chart type. See page 18 for more information.

Using the Chart Wizard

You can use Excel’s Chart Wizard to create a chart. The Chart Wizard walks you through steps to select a chart type, enter the range of cells, enter titles, set chart options, and choose the chart location. Follow these steps to use the Chart Wizard.

1. From the menu, select Insert, Chart, or click the Chart Wizard button [pic] on the Standard Toolbar. The Chart Wizard dialog box displays.

[pic]

2. Select the desired Chart type and Chart sub-type.

3. Click Next. Step 2 of the Chart Wizard displays.

[pic]

4. If you have not yet selected a range of cells, click in the Data range: field and select the desired cells. Hint: To collapse the Chart Wizard dialog box to easily select the range of cells you want to include, click the collapse button [pic].

Hint: To select non-contiguous cells, press and hold Ctrl while selecting the cells.

5. In the Series in: area, select whether to display the data by rows or columns.

6. Activate the Series tab. If desired, you can change the X-axis categories or series names. The range displayed in the Name and Values: fields, represents the series selected.

[pic]

7. Click Next. Step 3 of the Chart Wizard displays. Activate each tab and enter the appropriate options. These options will be discussed in further detail later in this class, beginning on page 9.

[pic]

8. Click Next. Step 4 of the Chart Wizard displays.

[pic]

9. Select where you want the new chart to display. (The chart can be moved later, if desired. See page 23.)

10. Click Finish. The new chart is created per your specifications.

Chart Toolbar

The Chart toolbar offers quick options for formatting and modifying charts. Usually the Chart toolbar displays when the chart is active (selection handles displayed around the chart.) You can activate a chart by single-clicking on the chart. If the Chart toolbar does not display, select View, Toolbars, then select Chart.

[pic]

Buttons of the Chart toolbar are described below.

|Click: |To: |

|[pic] in [pic] |Select an item on the chart |

|[pic] |Open the Format dialog box for the selected item |

|[pic] on [pic] |Change to a different chart type |

|[pic] |Display or remove a legend |

|[pic] |Display or remove the data table beneath the chart |

|[pic] or [pic] |Change the chart to display information by row or by column (Not available with all |

| |chart types) |

|[pic] or [pic] |Angle selected text downward or upward (You must click to select text before these |

| |buttons are available.) |

Resizing an Embedded Chart

You can resize an embedded chart. Click once on the chart to activate it. Selection handles display around the chart. Click and drag one of the handles to make the chart the desired size.

Moving an Embedded Chart

If you want to move an embedded chart on the worksheet, simply click and drag it to the desired location.

Formatting Chart Elements

You can format most chart elements including the background, the chart area, the gridlines, the columns or bars, fonts, and legend. A “Format” dialog box displays appropriate for the selected item. Use any of the following methods to format.

• Double-click the object or area you want to format.

• Right-click, then select Format [selected item].

• Use the Chart toolbar buttons.

• Select the item and then use the Format menu.

Note: Remember, you can “undo” any formatting you do that you do not like by clicking the undo button [pic] on the Standard toolbar.

Formatting Colors

You can format the color of chart items.

1. Double-click the chart item for which you want to change the color. A Format [chart item] dialog box similar to the following displays. The dialog box will be somewhat different for different chart features.

[pic]

2. If necessary, activate the Patterns tab.

3. Select the appropriate options.

Hint: For custom color options, click Fill Effects, then select the desired texture or pattern.

4. Click OK.

Using Fill Effects

Excel provides a variety of other formatting options for your chart objects.

1. Double-click the chart item for which you want to change the color. A Format [chart item] dialog box similar to the following displays. The dialog box will be somewhat different for different chart features.

2. If necessary, activate the Patterns tab.

3. Select the appropriate options.

4. Click Fill Effects.

5. In the Fill area, click the drop-down list and select Fill Effects.

[pic]

6. Activate each tab to view available options.

▪ The Gradient tab provides the option to select colors and shading. You can modify the shading styles and angles to create many different effects.

▪ The Texture tab provides several textures, such as marble, granite, or wood.

▪ The Pattern tab provides multiple pattern options as well as color selection options for the selected patterns. Patterns are useful on chart elements for charts that may be printed in black and white. It is easier to distinguish patterns than shades of gray.

▪ The Picture tab allows you to select a picture or graphic (from a file). Click Browse to locate the picture or graphic. You can select to stack the graphic or stretch it for the desired effect.

[pic]

7. Select the appropriate option(s).

8. Click OK.

Note: You can also select a chart item and select Insert, Picture, Clip-Art to format the chart element with a graphic.

Formatting Axes and Tick-Mark Labels

You can format the chart axes as well as the tick marks that cross an axis.

1. Select the axis you want to format by clicking it.

2. From the Format menu, choose Selected Axis.

[pic]

3. Activate each tab and select the appropriate options. Options include:

• Adding minor tick marks

• Displaying different line styles and colors for gridlines

• Modifying the scale of gridlines

• Modifying the font of the gridline labels

• Modifying the number format

• Modifying the alignment of the gridline labels

4. When finished, click OK.

Customizing Chart Options

The following options are available when using the Chart Wizard. After the chart is created, you can customize chart options such as gridlines, data labels, location of the legend, etc. by using the Chart menu. Select Chart, Chart Options. Activate each tab, make the appropriate changes, and click OK.

[pic]

Titles

Activate the Titles tab to add a chart title or titles for the various axes.

Axes

Activate the Axes tab to add or remove axes as desired.

Legend

Activate the Legend tab to add or remove the legend or to display it in an alternate location.

Data Labels

You can display data labels for the data series on your charts.

1. Select Chart, Chart Options.

2. Activate the Data Labels tab.

Note: To display labels for just one series, double-click the column, bar, or other data series on your chart. The Format Data Series dialog box displays.

[pic]

3. In the Data labels area, select the appropriate option, then click OK.

Formatting Data Series Labels

You might want to change the alignment, font, color, or number format for the data series labels. You can only format one series at a time.

1. Apply data labels as described above.

2. Double-click a data label. The Format Data Label dialog box displays.

[pic]

3. Activate each tab and make the appropriate changes.

4. To display the labels in a different location or with a different alignment, activate the Alignment tab and select the appropriate options.

Note: You can click and drag the red ( in the Orientation area to display a vertical data label.

5. Click OK.

Note: It is sometimes necessary to drag labels to a location if the label does not display in the exact location of your choice using the above options.

Formatting a Single Data Point or Data Label

You might at times want one data point or data label formatted differently from the rest.

1. Single-click on an item of data series or data labels. All the data series or data labels display with selection handles around them.

2. Single-click again on the one item you want to format differently from the rest. That one item now displays with selection handles around it.

3. Right-click the selected item, then select Format [item] to display the Format [item] dialog box.

4. Make the appropriate changes, then click OK.

Changing the Series Order

You may, at times, want to rearrange the order in which the series display in your chart.

1. Select any data series.

2. Select Format, Selected Data series. The Format Data Series dialog box displays.

3. Activate the Series Order tab.

[pic]

4. In the Series order: field, select the series you want to move.

5. Click the Move Up or Move Down buttons to move the series to the desired location.

6. Click OK.

Changing Column Spacing on Two-Dimensional Charts

To change spacing between columns of Two-dimensional charts, double-click one of the columns, then activate the Options tab. Adjust the Gap Width, then click OK.

Adding Error Bars

If you chart statistical or experimental data, you can use the Excel error bar feature to show the confidence level for your data. You can only use error bars on two-dimensional bar, column, area, or line charts.

1. Select the data series to which you want to add error bars.

2. Select Format, Selected Data Series.

3. Activate the Y Error Bars tab.

[pic]

4. In the Error amount area, select the desired options.

5. Click OK.

Adding Trendlines to Two-Dimensional Charts

You can add trendlines to your two-dimensional charts. This is commonly used with an X-Y scatter chart.

1. Select the series to which you want to add a trendline.

2. Select Chart, Add Trendline. The Add Trendline dialog box displays.

[pic]

3. Select the desired options.

4. If desired, activate the Options tab to select additional options.

5. Click OK.

Deleting a Trendline

To delete a trendline, select it and press the Delete key.

Changing the Chart Type

You can change the chart type if you feel that another chart type would more effectively illustrate your data.

1. Single-click the chart to activate it. Selection handles display around the chart.

2. Click the [pic] next to the Chart type button [pic] on the Chart toolbar and select the desired option.

or

For more options, select Chart, Chart Type. The Chart Type dialog box displays.

[pic]

Note: To view an example of your data with the selected chart type, click and hold Press and Hold to View Sample.

3. Select the appropriate chart type, then click OK.

Problems with Changing Two-Dimensional Column Charts to Three-Dimensional Column Charts

When you try to change a two-dimensional column chart to a three-dimensional column chart, you may get an unsatisfactory chart with jagged lines. To solve this, change to a three-dimensional bar chart and then change to a three dimensional column chart.

Chart Types

Bar and column charts are the most common, and are often the easiest to interpret. However, for some types of data, other chart types are more effective. Some of the chart types are described below.

Column or Bar Chart: Compares values across categories.

[pic]

Line Chart: Displays trends over time.

[pic]

100% Stacked Column Chart: Compares the percentage each value contributes of a total across categories.

[pic]

Pie Chart: Displays the contribution of each value to a total.

Tip: To create a pie in pie chart, enter the data you want to display in the smaller chart in the last rows. To adjust how much data displays in the smaller chart, double-click the “Other” pie piece, click the Options tab, and make the necessary changes. If you want to rename the “Other” piece, click on the text box that displays “Other” and enter a new name. In this example, we might rename Other to “Midwest.”

[pic]

Changing the Default Chart Type

You can change the default chart type. This is the chart type created when pressing the F11 key to create a chart.

1. Select Chart, Chart Type. The Chart Type dialog box displays.

[pic]

2. Select the appropriate chart type,

3. Click Set as default chart. (This chart type will be the chart created when pressing the F11 key as described above.)

4. Click OK.

Creating a Custom Chart Type

You can create a custom chart type with all the formatting features you want to use for future charts. This is similar to creating a template.

1. Select Chart, Chart Type. The Chart Type dialog box displays.

2. Activate the Custom Types tab.

3. In the Select from area, select User-defined.

[pic]

4. Click the Add button. The Add Custom Chart Type dialog box displays.

[pic]

5. In the Name: field, type a name for your custom chart type.

6. If desired, enter a description in the Description: field.

7. Click OK. Your custom chart displays in the Chart Type area.

8. Click OK to close the Chart Type dialog box.

Applying a User-Defined Chart Type

You can easily apply your custom chart type.

1. Select Chart, Chart Type. The Chart Type dialog box displays.

2. Activate the Custom Types tab.

3. In the Select from area, select User-defined.

4. In the Chart Type area, select the chart type you want to apply.

5. Click OK.

Note: You can make your custom type chart your default chart, if desired. See page 18.

Combining Chart Types

There may be times when you want to plot one series as columns while plotting another as a line or area chart. This can only be done with a two-dimensional chart.

1. Select the series you want to display as a different chart type.

2. Select Chart, Chart Type.

3. Select the desired option and click OK.

Changing 3-D Chart Viewing Angles

You can change the angles on three-dimensional charts. Select Chart, 3-D View. The following screen displays. Select the desired angles, then click OK.

[pic]

To return to the default angles, click the Default button.

Adding or Removing Data from a Chart

You can easily add or remove data from a chart in a variety of ways.

Adding Data

Excel provides several methods to add new data to a chart.

Clicking and Dragging

When you have an imbedded chart selected, Excel draws rectangles around the chart’s worksheet data. Use any of the following methods to add data by clicking and dragging.

▪ You can click and drag a fill handle to include the new data in your chart.

[pic]

Using Copy and Paste

You can use copy and paste to add data.

▪ Select the data you want to add. Select, Edit, Copy (or click the Copy button [pic] on the Standard toolbar). Activate the chart to which you want to add the data, and click Edit, Paste (or click the Paste button [pic] on the Standard toolbar).

▪ If you don’t get the desired result, click “undo.” Activate the chart and select Edit, Paste Special. Select the desired option.

[pic]

Removing Data from a Chart

Use any of the following methods to remove data from a chart.

▪ To get the best results, click on the chart to select the series or data point you want to delete and press the Delete key. (If you delete from the data, you get a result that displays a legend item with no data.)

▪ You can also click and drag to include or remove data from your chart.

[pic]

Modifying Chart Source Data

You can also modify what data is included in your chart from the menu.

1. Select Chart, Source Data (or right-click and select Source Data). The Source Data dialog box displays.

[pic]

2. Enter the appropriate cell range in the Data range: field. (You can select the appropriate cell range.)

3. Click OK.

Changing the Chart Location

When you create a chart you can either embed it on the worksheet or create it on a separate sheet. Follow these steps to move your chart to a different location.

1. Right-click on the chart, and select Location. The Chart Location dialog box displays.

[pic]

2. Select the appropriate option.

3. Click OK.

Enhancing Your Chart with Text, Arrows, and Drawings

You may, at times, want to add comments or call attention to items on your Excel Chart. You can do this using the Drawing Toolbar.

Turning on the Drawing Toolbar

To turn on the Drawing Toolbar, select View, Toolbars, Drawing. By default the Drawing toolbar displays at the bottom of the screen. (You can also right-click a toolbar; then select Drawing.)

Adding Arrows, Boxes, Circles, etc.

Follow these steps to add an arrow, box, circle, or other shape to your chart.

1. From the Drawing Toolbar, click AutoShapes.

[pic]

2. Select the type of shape you want to draw.

3. Move your mouse to the area on your chart when you want to draw the shape. Your mouse displays as a “cross-hair.” (+)

4. Click and drag to create the desired size and shape.

5. Release the mouse button. The shape displays on the chart or worksheet.

Formatting Shapes

You can format any drawn shape. Right-click the shape and select Format AutoShape. The Format AutoShape dialog box displays. Activate the tabs to make the desired formatting changes.

To re-size a shape, single-click it to display the selection handles on the shape. Click and drag to the desired size.

To add text to a shape, click on the shape and begin typing.

Some shapes can also be modified. To modify a shape, click and drag the yellow diamond, if available.

[pic]

To move a shape, single-click it to select it.

• If the shape contains text, click and drag the border of the shape to move it to another location.

• If the shape does not contain text, click and drag the shape to move it to another location.

[pic]

Creating a Chart in PowerPoint

You can also create charts in PowerPoint. Microsoft Graph Chart is applied over the PowerPoint application. This gives a more satisfactory display than copying an Excel Chart and pasting in PowerPoint. See page 29 or more information.

1. Open or create the PowerPoint presentation into which you want to insert a chart.

2. From the PowerPoint menu, select Insert, New Slide, or click the New Slide button on the Formatting toolbar.

3. From the Task Pane on the right, scroll to select a Content slide layout. A screen similar to the following screen displays.

[pic]

4. Click the chart placeholder. A chart with default data displays.

[pic]

Note: The PowerPoint chart is “active” when slashed lines display around the border. When you click outside of the border, the chart becomes inactive. To activate it again, double-click the chart.

5. Delete the default data and enter the data you want displayed in your chart.

Formatting the Power Point Chart

Most formatting features work the same way in PowerPoint as in Excel. However, you must activate the chart by double-clicking on it.

Activating a PowerPoint Chart

You must first activate the chart to make any modifications. Double-click on the chart. A “slashed” border displays around the chart and charting options (menu and toolbar) are available. When finished, click outside the chart.

Formatting Chart Elements

You can format most chart elements. The easiest way to format a chart element is to double-click it. A “Format” dialog box displays appropriate for the selected item. You can also right-click, then select Format [selected item] or use the Chart menu or Chart toolbars as you do for Excel Charts.

Note: First double-click the chart to make it “active.” Then double-click the item you want to format. Format options are similar to those in Excel Charts.

Changing Chart Types in PowerPoint

You can change from the default column chart to other chart types.

1. Activate the Chart by double-clicking it.

2. From the menu, select Chart, Chart Type. The Chart Type dialog box displays.

[pic]

3. In the Chart type: area, select the appropriate type.

4. In the Chart sub-type area, select the appropriate option.

5. Click OK.

Using Excel Data in your PowerPoint Chart

Often users want to display an Excel Chart in PowerPoint. The method that gives the best presentation in PowerPoint is to copy the data into the PowerPoint Chart datasheet. You can then use the PowerPoint charting options to format it appropriately. Nearly all chart-formatting options are the same from PowerPoint. The charting software used in PowerPoint and Word is Microsoft Graph Chart.

1. Open the Excel file containing the data you want to use.

2. Copy the data you want to use for your chart.

3. With the chart “active,” paste the data from Excel into the datasheet in PowerPoint.

Note: If the datasheet doesn’t display when the chart is active, select View, Datasheet.

[pic]

Creating a Chart in Word

Microsoft Word uses the same charting software as PowerPoint. Follow these steps to insert a chart in Word.

1. Place your insertion point where you want the chart to display.

2. Select Insert, Object, Microsoft Graph Chart. The chart is inserted and the same default chart and data display as for a default PowerPoint chart.

3. Edit the data in the data sheet as described earlier.

4. To edit or format your chart, you must double-click it to activate the chart.

Resizing the Chart in Word

You may want to resize the chart in Word. To do this, “single” click on the chart to display selection handles around the chart (similar to clip art). Click and drag the selection handles until the chart is the desired size.

Moving the Chart in Word

• To center the chart, click once to select it, and then use the Center button on the Formatting toolbar.

• For other options, right-click the chart. Select Format Object. Activate the tabs to make changes to size, or layout (which will allow you to drag the chart to another place or align text around the chart differently.

• Use the Picture toolbar to modify the size and location. The Picture toolbar should display when you click the chart. If not, select View, Toolbars, Picture. Rest your mouse over the various buttons to view their purpose.

[pic]

Use the Text Wrapping button [pic] and select “In Front of Text” to allow you to click and drag the chart to another location. Other options are also available.

Adding an Excel Chart to Word

You can also copy and paste an Excel Chart in Word.

1. Select the chart you want to copy and select Edit, Copy.

2. Open the Word document into which you want to paste the chart.

• To paste the Excel chart as a picture, simply select Paste.

• To paste a chart that you can edit, select Edit, Paste Special, Microsoft Excel Chart Object when pasting the chart. To make changes to the Excel Chart, simply double-click it. All Excel menus and tools become available. When finished, click outside the chart to de-activate it. Move or resize as described above.

-----------------------

Data for smaller chart

Click here and select the cells you want to chart.

Paste Excel Data here.

Click and drag handle to remove data.

Chart Tools –

To view all tools, click to show buttons on two rows.

Click and drag to adjust shape.

Click and drag to the desired size

Click and drag handle to include additional data.

Chart Toolbar

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download