Exercise 5: Week 5

CREATING EXCEL CHARTS

LEARN THESE KEY SKILLS

You can look at a grid of numbers and figures and calculations until you go nutsy-cuckoo. You can flip it, turn it, calculate it, massage it, and so on and still miss the obvious. Sometimes you need to "see" the data, and the best way to see other meanings in data is to chart them. A chart can show you trends relationships of one set of data compared to another (like one product versus another or one quarter versus another), individual volume compared to total sales, and so on.

Excel is more than just a calculation program; it also offers sophisticated charting tools. You can use these tools to create charts of all types, as covered in this chapter.

Decisions, Decisions

Before you start wildly charting every little thing in your worksheet, you should understand a few concepts about charts, Most important you should have a good idea of the different chart types. Doing so will help you select the one most suitable to your message. For example, if yon want to show how sales increased from 1992 to this year, a pie chart isn't going to help. You do end up with pie on your face, so to speak. If you want to show how your total sales break down by product, a line chart isn't the "right" chart. This section explains the different chart types as well as other decisions to think about before you start charting away. Think of this section as picking the "right" outfit for different occasions.

Selecting the "Right" Chart Type

When you create a chart, Excel selects a column chart by default. But you don’t have to use this chart type. Excel has several chart types to choose from, and each chart has several subtypes or styles. You should try to match the message you want your data to convey to the best chart type. Which chart type is best? It depends. The table below explains each chart type so that you can have some ideas of what to try when you create your chart.

Chart TypeDescription

COLUMN CHARTThe default chart type. Use this chart when you want to compare items but emphasize change over time. The values are charted vertically. Subtypes include stacked, 3D, and 100% stacked charts.

BAR CHART Similar to the column chart, but the values are plotted horizontally rather than vertically. The horizontal plotting puts more emphasis on comparison, rather than time. Subtypes of this chart type include a stacked bar chart (values are stacked on each other), 3D, and 100% stacked (the percentage of each value is stacked).

LINE CHARTUse this chart type when you want to show trends or emphasize change over time.

PIE CHARTUse this chart type when you want to show the relationship of the individual values to the whole. You can chart only one data series in a pie chart.

XY SCATTER Use this chart type to show the relationship of values in several chart data series. The chart shows clusters or uneven intervals in the data, a feature that is useful in charting scientific data.

AREA CHART Use this chart type when you want to show change in volume or magnitude over time. This chart type is similar to a line chart, but an area chart emphasizes the amount of change. The line chart emphasizes the rate of change.

DOUGHNUT CHARTSimilar to a pie chart, this chart shows the relationship of the parts to the whole, but in a doughnut chart, you can chart more than one data series.

RADAR CHARTUse this chart when you want to show changes relative to a center point. This plotting shows which data series covers the most area.

SURFACESimilar to a topographical map, this chart type is useful for findingrelationships that may otherwise be difficult to see, such as the best combination between two sets of data.

BUBBLEA type of XY scatter chart. The difference is that the size of the data marker indicates the value.

STOCKOften called a high-low-close chart, it is used for charting stock prices or other scientific data.

CYLINDERAnother style of a column or bar chart, but cylinders are used to plot the data series.

CONE Another style of a column or bar chart but cones are used for the data series.

PYRAMIDAnother style of a column or bar chart that uses a pyramid.

Deciding Where to Place the Chart

You may not know exactly what chart type you want until you start tinkering around, but the preceding table should give you a good idea of which chart types might be applicable. The next thing you should think about is where you want to place your chart. You can include a chart as a graphic object on the worksheet. Use this option when you want to show your data from the worksheet and the chart on the same page. You can also include a chart as a separate sheet in the workbook. Use this when you want to create a separate page for your chart.

Deciding What to Chart

You start a chart by selecting the data that you want to chart, This may insert simple enough, but keep a few points in mind:

Excel will use the column and row headings as the axis titles and legend. (You can select which is which.)

If your chart includes a total row, you most likely will not include this in the charted data. For example, if you are charting the sales of products over four quarters, you won't want to include the totals. Instead, just include the individual data series.

In a pie chart, you can chart only one series - that means you can select only one set of data to chart.

Charting Your Course

If you are starting to think that charting may be more trouble that it's worth, don't. Excel makes creating a chart simple with the Chart Wizard. This wizard leads you step by step through the process and shows you visually how your choices affect the chart. You can create a chart in four simple steps. (This is much easier than previous versions without the Chart Wizard, where you may have been tempted to simply color a chart rather than figure out how to set up a chart.)

Follow these steps to create a chart:

  1. Select the range that you want to chart. (Use Data from Exercise5.exl)

  2. Select “Insert” “chart” or click the chart icon. Excel displays the first step of the Chart Wizard here you select the chart type (see above for types). Notice that you can select the chart type and the style (or subtype).
  3. In the Chart type column, select the type of chart you want to create. In the Chart sub-type list, Excel displays the available styles of this chart type.
  4. Select the chart subtype you want and click Chart Wizard. Here you can select how the series order.

TIP: If you want to get help on the different chart types, click the Help button in the dialog box. You can also click the Press and hold to view sample button to see how your data will be charted using the selected chart type. If you change your mind, click the Back button to go the Chart Wizard steps and make changes.

  1. On the Data Range tab, confirm the correct range is selected in the Data range text box. If not, drag across the correct range in the worksheet. Also, select whether the rows or columns are the series. For example, when the rows are the series, each quarter is charted as a group. You could do the reverse - use the columns (here the products) as the series. Compare the two to see the difference.

  2. If you selected the data correctly, you shouldn't have to make any changes to the series, so you can skip this step. Excel guesses pretty well what to use as the axis label, the series names, and the values. If you want to confirm or change any of these, click the Series tab and make any changes. Click the Next button. When you click the Next button, you see Step 3 of the Chart Wizard. Again, the defaults will work for most charts. You can always start with the defaults and then make changes, as covered later in this chapter. Or you can click any of the tabs and make changes.
  3. Make any changes to any of the chart options tabs and then click the Next button. You are prompted to select a location for the chart - on the worksheet or as a new sheet.
  4. Select where to place the chart and click the Finish button. If you selected the new sheet option, Excel creates the chart on a new, separate sheet in the workbook. If you selected to include the chart as an object in the worksheet, the chart is placed on the worksheet. You may have to adjust the size and placement as covered later.

What If I…

Your first concern with the chart should be that the data are represented accurately and that the chart as a whole is OK. Then you can worry about the individual elements of the chart. First, if you placed the chart on the worksheet, is the chart the right size? In the right place? If not, you can move it around or resize it. Second, is the chart the right type? If not, you can select another chart type. Third, are the data right? You can make changes to the data and poof! - the chart is updated. Finally, if you don't like what you got and want to start over, you can delete the chart.

Moving, Resizingand Deleting the Chart

If you included the chart on the worksheet, Excel plunks down the chart right in the worksheet. Maybe right on top of your data. If that happens, you move it out of the way. Also, the chart may be too small. You can change the size. And if you messed up in a big way, you can just delete the chart and start over. (You can't resize or move a chart that is on a separate sheet. You can delete the chart by deleting the sheet.)

To make any changes to the chart, start by selecting it. To do so, click the chart object once. You should see black selection handles around the outside of the chart. Now you can make any of the following changes:

To move the chart, place the pointer on a chart border, but not on one of the selection handles. Drag the chart to the new location.

To resize the chart, place the pointer on a selection handle and drag. Use the handles at the top and bottom to make the chart taller Use the handles on the sides to make the chart wider. Use the handles in the corners to change both the height and width.

To delete the chart, press the Delete key.

Changing the Chart Type

Charting takes some trial and error. The more you work with chartsthe easier it gets. When you see how your data turn out in a chart, you may think, "that's not it." You may want to try another chart type. Rather than recreate the chart, you can select a different chart type by following these steps:

If the chart is on the worksheet, click the chart once to select the entire chart object. If the chart is on a separate sheet, click that sheet tab to display the chart.

Select “ Chart” “Chart Type” You see the Chart Type dialog box, which is similar to Step 1 of the Chart Wizard.

Select the chart type and subtype and click OK.

Changing Charted Data

The connection between the chart and the data it represents is "live" - that is if you make a change to the worksheet data, the chart is updated. If you delete data in the worksheet, the matching data series will be deleted in the chart. Pretty cool! You might want to make a change even if one isn't necessary to test this out.

Follow these steps:


  1. Hey, go ahead change the data in Quarter 2- for Soccer from 53,150 to 75,000.

Just as you can change the worksheet data to update the chart, you can also change the chart and update the worksheet data. To make this type of change, follow these steps:

If the chart is on the worksheet, click the chart once to select the entire chart object. If the chart is on a separate sheet, click that sheet tab to display the chart.

Click the data point you want to change. When a single data point is selected, black selection handles appear along the borders of the area.

Also, the Name box displays the selected data point, and the formula bar displays the entire series references.

Drag the data point up or down. The corresponding worksheet data are updated to reflect the change.

Adding Chart Data

If you insert a row or column into the worksheet, those data are not added to the chart automatically. If you want to include the data on the chart, you can do so by following these steps:

Follow these steps:


  1. In the worksheet, add the row “Hockey” with the following figures for each quarter’

Qrtr 1- 21,150

Qrtr 2- 30,450

Qrtr 3- 31,900

Qrtr 4-22,100

  1. Select this new data you want to add to the chart.
  2. Drag the selection onto the chart. The series are updated to include the new data.

If the worksheet is on a separate page, you can use the Chart Add Data command to add the data to the worksheet. Select the command and then when prompted, select the range that contains the data you want to add.

Changing the Chart Location

Change your mind about where you want the worksheet? Then you can move it from the worksheet to a separate sheet or vice versa. Just follow these steps:

Select the chart.

Select “Chart” “Location”. You see the Chart Location dialog box, which is similar to Step 4 of the chart Wizard.

Select As new sheet to move the chart to a new sheet “Select As” object in and then select which worksheet you want from the drop-down list to move the chart to a worksheet.

Click the OK button

Chart Makeovers

Many different elements make up a chart - the data points, the collection of data points or series, the axes, the legend, and more. You can change each individual element of the chart. To start, you have to figure out what the element is called. Then you can make a change. This section describes generally how to make a change. As with a lot of Excel features, you can tinker with many, many different options to control how the chart looks. The best way is to experiment. You can also consult the Office Assistant for complete information on each command and option.

Making a Change to a Chart Element

To make a change or simply get an idea of what you can change, follow these steps:

Double-click the chart element you want to change. For example, double-click the data series you want to change. You see a dialog box with tabs for each set of options you can change.

Select the tab you want and make any changes. For example, if you did not like the color used for this particular series, you could select another color on the Patterns tab.

Click the OK button when you are finished making changes.

Follow these steps:


  1. Select a new pattern and add to the chart

Changing Chart Options

You can also change the chart options that you selected when you created the chart. Here you can add titles, select how the gridlines are displayed, and what appears as data labels. Some of these options overlap with options you can change by double-clicking the chart item.

Follow these steps:

  1. Select the chart.
  2. Select “Chart” “Chart Options”.

  3. Select the tab you want. Use the Titles tab to add a chart title “Revenues by Quarter for Five Sports”
  4. Add a x-axis title (Sports)
  5. Add a y-axis title (Revenue)
  6. Use the Axes tab to select whether the x- and y-axes are displayed.
  7. Select gridlines to be displayed for the x- and y-axes on the Gridlines tab.
  8. Select whether a legend is included and if so its placement in the right hand side of the chart.
  9. Use the Data Labels tab to select how data labels are displayed. Include the data table as part of the chart, use the Data Table tab.

PRINT OFF AT END OF CLASS AND HAND IN