Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog

The Excel Chart Wizard

The sample data has been selected as a first step to making our chart. From here we will run the Chart Wizard.

The Chart Wizard can be started by using the Chart Wizard button on the Standard command bar (left) or from the Chart... button on the Insert menu (shown below). The data can be selected prior to running the chart wizard, filled in while running the chart wizard, or added to the chart later.

The Chart Wizard runs through a series of steps that help you set up your chart.

Step 1 - Select a Chart Type

The Standard Types tab of Step 1 of the Chart Wizard shows a list of chart types on the left side, and several chart sub-types on the right. The Custom Types tab has a number of built-in custom types of charts, including several combination charts (not an exhaustive list: see Roll Your Own Combination Charts elsewhere on this site). You can add your favorite custom chart types to the User-Defined list.

Column charts are among the most common chart types you will use (see dialog box below). The two commonly used sub-types are the Clustered Column type (top left option) and the Stacked Column type (top middle); the 100% Stacked Column type (top right) normalizes each stack so it reaches the top of the chart. I avoid the 3D styles, because I find it difficult to line the column tops up with the tick marks on the axes. Bar charts are horizontally oriented versions of column charts.

Line charts are also commonly used in Excel. The default Line-With-Markers is probably most common (selected in the dialog box below); other options include Stacked Line and 100% Stacked Line, and all of these are available with or without markers. Again, I avoid the 3D Line type.

The Category (X) values in a line chart are treated as equally-spaced non-numeric categories, unless a time-scale category axis is selected in Step 3-Chart Options, and points are plotted in order from left to right.

For a discussion of X axis types, see X Axis: Category vs. Value; for a comparison of Line charts and XY Scatter charts, see my article, Scatter Chart or Line Chart?, in the TechTrax web magazine.

The XY (Scatter) Chart is the type I use most often. In addition to the default Markers-Only style, you can select smoothed or straight line connectors, with or without markers, as shown below.In a Scatter chart, both the X and Y values are treated as continuously variable numeric values. A logarithmic X axis can only be produced in an XY chart.

For a discussion of X axis types, see X Axis: Category vs. Value; for a comparison of Line charts and XY Scatter charts, see my article, Scatter Chart or Line Chart?, in the TechTrax web magazine.

Excel offers a number of other chart types. Area charts are similar to line charts, with the areas under the lines filled in with some color pattern. You can make several versions of Pie charts, and Doughnut charts are similar, but allow multiple series, plotted concentrically. Bubble charts allow an additional value to be plotted for each point, where the diameter of the circle representing the point is sized according to the third variable. Stock charts let you plot high, low, and closing stock prices, with some options allowing opening prices and additional series, representing volume or a market index. Cylinder, Cone, and Pyramid charts are 3D Column charts which use other shapes instead of rectangular columns. You are encouraged to experiment with these chart types outside of this tutorial.

Step 2 - Source Data

The Data Range tab gives you a preview of the chart style you selected in Step 1, indicating where the chart's source data range is, and allowing you to plot by row or by column. The first dialog box below shows a column chart, plotted by rows. The second shows an area chart plotted by columns.

If you click in the Data range box, or click on the Range selection button , the dialog box shrinks to the size of just the data range box, obscuring less of the worksheet, so you are able to adjust this range with your mouse.

When the Source Data dialog box is activated, the source data range is highlighted by a "marching ants" border, shown in unanimated form below.

Clicking on the Series tab gives you the option to add or remove some of the data series in the selected source data range. You can change the Name, Values (Y axis), and Category labels (X axis) of each series, by editing the contents of the respective box, or by clicking on the Range selection button , then dragging with the mouse.

Step 3 - Chart Options

This step allows you to provide a title for the chart and for the chart axes, to select which and what kind of axes to put in your chart, and whether to show other options, such as gridlines, a legend, and data labels.

Step 4 - Chart Location

Here you tell Excel where to place the chart. You can put the chart onto its own sheet with a name that you enter in the box (where it says "Chart1"). Or you can embed the chart as a chart object in one of the existing worksheets (or chart sheets), which you select from the drop down list.

The New Chart

Here are a few examples of finished charts. Of course, these are in the default Excel formats, and you'll want to adjust the fill colors, marker shapes, background colors, font sizes, and other formats.