Charts produce graphics from data in a table and are useful for displaying data patterns or results. To create a chart, we must first have a table with data and then use the Excel
Chart Wizard, a step-by-step process that helps us define the ideal chart for our
purposes. Let’s now work with Figure 5.1, a table of car sales for a local dealer in the year 2003, to demonstrate the uses of the Chart Wizard. The table records the numbers of various models sold as well as the total revenue generated for each model.
Figure 5.1 Car sales for 2003 at a local dealer.
Suppose we want to compare which Car Model sold the most and which Car Model generated the most revenue. We will create two different charts to do this: a bar graph and a pie chart. We begin by creating a bar graph of the number sold for various models. First, we need to highlight the data that we want to graph. In our example, we highlight the cells B4:B10 and E4:E10 (by holding down the CTRL key). We have highlighted the column titles so that we can label data automatically when creating the chart. To begin using the Chart Wizard, we either choose Insert > Chart from the menu or click the icon at the top of the window. The following screen then appears (see Figure 5.2).
Figure 5.2 The Chart Wizard allows a user to create a chart from data by following a
series of simple steps and option selections.
Step 1 features a selection of Chart types. Clicking on each Chart type displays all of the
Chart sub-types, which are the various display options associated with that particular Chart type. First we want to create a vertical bar graph from our data. To do so, we
select Column from the list of Chart types and click on the top left Chart sub-type. We will only discuss the Standard Chart Types in this chapter; however, the selection process for the Custom Chart Types parallels what we will be demonstrating.
After we click Next, A new window with two tabs, Data Range and Series, appear. A Data Range is the range of cells that we initially highlight to plot. In this tab, we are asked to verify or change our selected Data Range (see Figure 5.3(a)). The following
Data Range appears in the window:
=’New Example’!$B$4:$B$10, ’New Example’!$E$4:$E$10
This step allows us to verify that we have highlighted the correct data range. Therefore, we do not modify our Data Range. After checking the data range, we can then select an option to define our Series. Series are various sets of data.
Data Range: Cells selected to create a chart.
Range: Any group of cells that form a row, column, or area. Series: Various sets of data.
We can view the ranges of each individual series of data by selecting the Series tab at the top of the window (see Figure 5.3(b)). For each Series listed in the window, we can see the corresponding range of data in the Values section. Series1 corresponds to the first column of the data we selected while the other series apply to their corresponding columns.
(b)
Figure 5.3 Step 2 of the Chart Wizard allows a user to verify or change the Data
Range and determine how to define the Series. (a) Data Range selection and the row versus column option for Series. (b) Range selections and names for each Series.
We can also assign each series a Name. Note that if we include column titles while highlighting our initial data, then Excel automatically uses those titles to create the series names.
Pressing Next again brings us to the third step of the Chart Wizard that defines the final chart options (see Figure 5.4). In the first tab, Titles, we create labels for our axes and the chart itself (see Figure 5.4(a)). In the Axes tab, we can decide whether we want to show the values of the x- and y-axes (see Figure 5.4(b)). In the Gridlines tab, we can determine how many gridlines should be shown in the x- and y-directions of the graph (see Figure 5.4(c)). In the Legend tab, we can choose the location of our legend or choose not to have it displayed at all (see Figure 5.4(d)). In the Data Labels and Data
Table tabs, we can determine which, if any, labels will appear with the data points and
whether we want the original data table to appear below the graph (see Figures 5.4(e) and 5.4(f)).
(a)
(c)
(e)
(f)
Figure 5.4 Step 3 of the Chart Wizard defines the Chart Options. Each tab at the top
of the window presents options for each category.
We again press Next to arrive at the final step of the Chart Wizard, which allows us to select the location of the chart (see Figure 5.5). If we choose As new sheet, Excel maximizes the chart to fill an entire new worksheet. We can then label the name of this worksheet. Here it is automatically given the name “Chart1.” Choosing the chart as an object in a sheet leaves it as a separate smaller window in the current worksheet or in another sheet of our selection. In this figure, we have chosen to include it automatically as an object in the current sheet.
Figure 5.5 Step 4 of the Chart Wizard is to select the location of the chart.
The resulting bar graph is in Figure 5.6. We can now determine that the Aco3500 had the highest sales.
Figure 5.6 The resulting bar graph of Car Models Sold.
We can now follow the Chart Wizard steps again to create a pie chart of the Revenue Generated by each car model. The final chart should look like the chart shown in Figure 5.7. Here, we can see that the Cam3200 generated the most revenue.
Figure 5.7 The pie chart of Revenue Generated per car model.