Creating Charts with the Chart Wizard

One of the terrific features in Excel is the Chart Wizard. The easiest way to create a chart in Excel is to use the Chart Wizard. The Chart Wizard leads you step by step through the task of creating a chart. Excel plots the data and creates the chart where you specify on the worksheet.

Creating charts with the Chart Wizard is a snap because you get help every step of the way. You are guided through four dialog boxes from which you create your chart: Chart Type, Chart Source, Chart Options, and Chart Location. You can preview the sample chart in all the steps and make changes to the chart at any time.

A basic concept you should remember when creating a chart in Excel is that you must select data before you can create a chart. You begin with Excel's default (or automatic) chart and then modify it to your liking. With so many chart types and options, you have carte blanche for creating a chart that best suits your needs.

All charts start out basically the same. You have to create a basic chart with Excel's automatic settings before you can create more customized charts. If desired, you can modify the basic chart, using various tools. The first task is to select the data you want to chart. The second task is to bring up the basic chart.

Selecting the Chart Type and Subtype

You can choose a chart type from the Chart Type list and then choose a chart subtype from the Chart Subtype gallery in a Chart Wizard dialog box. A description of the chart type appears in the lower-right side of the Chart Wizard dialog box when you click a chart subtype.

Choosing the Data Range and Series

To control the orientation of your chart, you choose the data range and then plot a series in rows or columns. Sometimes when Excel produces a chart from a highlighted range, the chart is backward. The data series appears where categories should be and vice versa. How does Excel know which orientation to use? Well, Excel makes a guess based on your selected data. If you have more columns than rows, then the columns become the categories on the x-axis. If you have more rows than columns , then the rows become categories along the x-axis.

You can always change Excel's orientation for a chart if Excel guesses wrong. Here's how you can change the orientation. Choose to plot your data in rows if you want the rows to be translated into data series and columns into categories. The rows option is best used when the selected data range contains more columns than rows.

In some instances, you might create a chart by plotting your data in columns, which turns your columns into data series and rows into categories. This situation would occur when you have more rows than columns.

The chart's appearance depends on your choice, so make sure you choose a setup that fits your needs best.

Setting Chart Options

All kinds of chart options are available for your chart, including titles, axes, gridlines, legend, data labels, and data table. These are the tabs in the Chart Wizard - Step 3 of 4 - Chart Options dialog box. Here's where you can add descriptive text to the chart if you like. For example, you can add labels to the Category (X) axis along the bottom of the chart and Value (Y) axis labels along the left side of the chart.

Choosing a Location for the Chart

In the final Chart Wizard dialog box, you can specify where you want to place the chart. You have two choices: As New Sheet and As Object In. The As New Sheet option lets you insert the chart on a separate chart sheet. A chart sheet is a separate element from the worksheet and is stored in the current workbook.

The As Object In option enables you to insert the chart as an object in the worksheet that contains the data you're charting. A chart object on a worksheet is useful for showing the actual data and its graphic representation side by side.

The next few steps help you create a default chart (clustered column chart) using the Chart Wizard.

Select cells A3:C7 on the Summary sheet of your My Budget workbook to identify the range you want to chart.
Click the Chart Wizard button on the Standard toolbar. The Chart Wizard - Step 1 of 4 - Chart Type dialog box opens, displaying the chart types. The Clustered Column chart is the default chart type. You want to use this chart type.
Click the Next button to accept the Clustered Column chart type, and the Chart Wizard - Step 2 of 4 - Chart Source Data dialog box should appear with a sample chart, as shown in Figure 47.12.

Figure 47.12. The Chart Wizard - Step 2 of 4 Chart Source Data dialog box, plotting data in columns or rows.
Leave the Columns option selected. Each column or data series represents the values for each budget category by month. January and February are the Category (X) axis labels. The budget category names appear in the legend for the data series.
Click the Next button. Excel displays the Chart Wizard - Step 3 of 4 - Chart Options dialog box. The dialog box contains a sample chart and options for adding titles, changing the legend, and formatting other elements in the chart.
On the Titles tab, click the Chart Title text box and type My Budget .
Click the Next button. You should see the Chart Wizard - Step 4 of 4 - Chart Location dialog box. You can place the chart on a separate chart sheet or as an object in an existing worksheet. A chart sheet is a separate element from the worksheet and is stored in the current workbook. Keep the As Object In option and Summary sheet selected.
Click the Finish button. The chart appears near the top of the worksheet. The chart has a plot area with data series columns, and a legend on the right. Selection handles surround the border of the chart. You also should see the Chart toolbar. Sometimes the Chart toolbar does not always automatically appear when the chart is displayed. Figure 47.13 shows the clustered column chart and the Chart toolbar.

Figure 47.13. The clustered column chart and Chart toolbar.

A chart is handled as an object in an Excel worksheet, and so you can move and resize the chart, just as you would any object in Excel.

To move a chart on a worksheet, click anywhere in the chart to select it and then hold down the left mouse button. When the mouse pointer changes to a four-headed arrow, drag the chart to a new place.

To change the size of a chart, select the chart and then drag one of its handles (the black squares that border the chart). Drag a corner handle to change the height and width or drag a side handle to change only the width.

When you save the worksheet, Excel saves the chart along with it. Unless you remove it, this chart appears on the worksheet. You can remove a chart by clicking it and then pressing the Delete key.

Creating Charts with the Chart Wizard