Microsoft Excel Charts
Charts
Charts are visually appealing and make it easy for users to see comparisons, patterns, and trends in data. And, a chart is linked to the worksheet data it's created from and is updated automatically when you change the worksheet data. Similarly, if you adjust the value indicators in the chart, the worksheet data will change. Some of the terms used while creating charts are: The data that will be displayed in the chart is called the data source. Excel organizes the data source into data series which are ranges of numbers that are plotted on the chart. Y Axis Value Axis
Budget
1000 800 600 400 200 0
R EN T O FO D G AS E EL C TR IC PH O N E C L EL
Chart Title
JANUARY FEBRUARY MARCH APRIL
Legend
X Axis Category Axis
•
Note: If you are new to creating charts in Excel, it is best to first select the cells in the worksheet that you want to chart. If the cells are non-adjacent, hold down the CTRL key while you select the cells. Selecting the cells to be charted can be done later, but if you intend to look at sample charts in the Chart Wizard, the samples will be more graphic with cells selected.
Excel - Charts
Page 1 of 8
June 15, 2005
Chart Wizard
To create a chart, click Chart Wizard on the toolbar. • Note: You can quit the Chart Wizard at any time by clicking the ‘Finish’ button. Excel will complete the remaining steps of the Chart Wizard using the default configuration for the chart you selected.
The Chart Wizard has 4 steps.
Step 1: Chart Type
Purpose: Select the type of chart you want to create from a list of standard chart types and associated chart sub-types or choose to customize and create your own chart type. There are 2 tabs to permit you to select a standard chart or a custom chart. This handout will focus on standard types of charts. Select the type of chart you want. There are 14 standard types of charts. And each of the 14 types has sub-types! Click on the chart type you want and then click on the sub-type. A brief description of the chart will be displayed. Press and hold this button to see a sample of the selected chart. NOTE: Every chart type is not appropriate for all data! And, make sure the data on your worksheet is arranged properly for the type of chart you want to use. The following table highlights some of the traits of each chart type: Chart Type
• • • • • • • • • •
Qualities
most commonly used compares values based on height of the columns categories are horizontal, values (data) are vertical shows changes over time, or comparisons between items sub-types include: clustered, stacked, 100% stacked, 3-D compares values based on width of the bars categories are vertical, values (data) are horizontal shows comparisons between items less emphasis on time flow than a column chart sub-types include: clustered, stacked, 100% stacked
Excel - Charts
Page 2 of 8
June 15, 2005
Chart Type
• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • •
Qualities
compares values using different lines for different categories shows trends in data over a period of time, at equal intervals emphasizes the rate of change over time, rather than the magnitude of change sub-types include: stacked, 100% stacked, 3-D compares values based on the size of the pie slice always contains just one data series shows the relationship (proportion) of parts to the whole useful for emphasizing a significant element sub-types: exploded, pie of pie, bar of pie shows uneven intervals (clusters) of data good for showing time good for finding patterns/trends shows relationship among numeric values in several groups of data commonly used for scientific data sub-types include: scatter with data points connected by lines similar to line chart but, the areas under the lines are filled in with color emphasizes extent of change over time good for showing trend over time or categories sub-types include: stacked, 100% stacked similar to a pie chart in that shows relationship (proportion) of parts to the whole but, it can contain more than one data series sub-types include: exploded doughnut compares the aggregate values of a number of data series each category has its own axis radiating from the center point lines connect all the values in the same series sub-type include: filled radar useful for finding optimum combinations between two sets of data. colors and patterns indicate areas that are at the same value use for 2 changing variables can be difficult to read and interpret sub-types include: 3-D, contour similar to scatter chart but, compares 3 variables the size of the bubble or data marker indicates the value of the third variable place x values, y values and bubble sizes in adjacent rows or columns plots stock price data can also be used for scientific data must organize your data in the correct order to create stock charts see descriptions in ‘step 1: chart type’ dialog box for required data and order sub-types include: high-low-close, open-high-low-close, volume-high-low-close, volume-open-high-low-close
• basically, column and bar charts using the specified shape to create dramatic effect • sub-types include: column, stacked column, 100% stacked column, bar, stacked bar, 100% stacked bar, 3-D column
Excel - Charts
Page 3 of 8
June 15, 2005
•
After you select chart type, click the ‘Next’ button to go to Step 2.
Step 2: Chart Source Data
Purpose: Specify the worksheet cells that have the data that will be charted and the cells that have the labels that will be displayed on the chart. Watch the preview window as you make changes! Step 2 has 2 tabs: 1. Data Range tab = select the range of cells you are charting and the range of cells with labels Type the cells that contain the data and labels that you want in the chart in the ‘Data range:” box:
OR Click the Collapse Dialog button and select the cells that contain the data and labels that you want directly in the chart. Excel assumes the data is organized in rows or columns, but you can override this by clicking the appropriate box. 2. Series tab = differentiates the values you’re charting (series) and the labels Excel assumes this information from the Data Range tab. But you can override the information by adjusting the appropriate box.
• •
You can return to previous steps by clicking on the ‘Back’ button. When you have finished Step 2, click the ‘Next’ button to go to Step 3.
Excel - Charts
Page 4 of 8
June 15, 2005
Step 3: Chart Options
Purpose: Change the appearance of the chart by selecting options that define titles, axes, gridlines, legend, data labels, and data tables. Watch the preview window as you make changes! Step 3 can have up to 6 tabs depending on the chart style selected: 1. Titles tab Chart Title: type the title for the chart Category (X) axis: type the label for the horizontal axis Value (Y) axis: type the label for the vertical axis 2. Axes tab Category (X) axis: prints the categories along the horizontal axis Value (Y) axis: prints the values along the vertical axis
3. Gridlines tab Click to select which gridlines to display on the chart. Default is ‘Value (Y) Axis Major Gridlines.’
Excel - Charts
Page 5 of 8
June 15, 2005
4. Legend tab Determines whether the legend is displayed on the chart and, if displayed, where. Default is that the legend is displayed on the right.
5. Data Labels tab Determines what information is displayed at the data points. The Separator is used when you are displaying more than one piece of information.
6. Data Table tab Determines if the data should be displayed at the bottom of the chart and, if displayed, should the legend key (color and format) also be displayed.
• •
You can return to previous steps by clicking on the ‘Back’ button. When you have finished Step 3, click the ‘Next’ button to go to Step 4.
Excel - Charts
Page 6 of 8
June 15, 2005
Step 4: Chart Location
Purpose: Specify where the chart will be placed: embedded in the worksheet that has the data or on a separate worksheet called a chart sheet. Either way, the chart is linked to the source data on the worksheet, which means the chart is updated whenever you update the worksheet data! Embedded charts - An embedded chart is considered a graphic object and is saved as part of the worksheet on which it is created. Use embedded charts when you want to display or print one or more charts with your worksheet data. Chart sheets - A chart sheet is a separate sheet within your workbook that has its own sheet name. Use a chart sheet when you want to view or edit large or complex charts separately from the worksheet data or when you want to preserve screen space as you work on the worksheet. • • You can return to previous steps by clicking on the ‘Back’ button. When you have finished Step 4, click the ‘Finish’ button to end the Chart Wizard.
Updating Charts
• Right click on the chart. The option menu will include: Chart Type – this will display a dialog box like Step 1 of the Chart Wizard Source Data - this will display a dialog box like Step 2 of the Chart Wizard Chart Options - this will display a dialog box like Step 3 of the Chart Wizard Location - this will display a dialog box like Step 4 of the Chart Wizard • You can right click on any object (or double click) in the chart and the first option will be ‘Format xxx’ where xxx is the object you right clicked on. Depending on the object, you will have formatting options displayed in a dialog box. You can select a single data marker by clicking once to select the data series and clicking again to select the data marker. If you adjust the size of the data marker, the change will be reflected in the appropriate cell in your worksheet You can move and size an embedded chart. Click and drag on the border to move the chart; click and drag the sizing handles to resize the chart.
•
•
Excel - Charts
Page 7 of 8
June 15, 2005
•
Click VIEW > TOOLBARS > CHART on the menubar to display the following Chart toolbar: Legend Data Table
Chart Objects Format selected Chart Object
some Chart Types
data series in Columns data series in Rows
The Chart Type button on the toolbar will display some of the more popular chart types. If you want a chart type that is not displayed, right click on the chart and click ‘Chart Type.’
Excel - Charts
Page 8 of 8
June 15, 2005