Present your data in a column chart

Using Microsoft Office Excel 2007, you can create a column chart and give it a brand new, appealing look. As one of the most common chart types, a column chart is useful to compare data points (data points: Individual values plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and various other shapes called data markers. Data markers of the same color constitute a data series.) in one or more data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.).

Formatted stacked column chart

After you create a column chart, you can change the spacing between the columns. You can also create a floating column chart by hiding a data series in the chart.

In this article


Learn more about plotting data in a column chart

Data that is arranged in columns or rows on a worksheet can be plotted in a column chart. Column charts are useful for showing data changes over a period of time or for illustrating comparisons among items.

In column charts, categories are typically organized along the horizontal axis and values along the vertical axis.

Clustered Column Chart in 3-D

Consider using a column chart when:

  • You have one or more data series that you want to plot.
  • Your data contains positive, negative, and zero (0) values.
  • You want to compare the data for numerous categories side by side.

When you create a column chart, you can choose one of the following column chart subtypes:

  • Clustered column and clustered column in 3-D    Clustered column charts compare values across categories. A clustered column chart displays values in 2-D vertical rectangles. A clustered column in 3-D chart displays the data by using a 3-D perspective only. A third value axis (depth axis) is not used.

Clustered column chart and clustered column chart in 3-D

You can use a clustered column chart type when you have categories that represent:

  • Ranges of values (for example, item counts).
  • Specific scale arrangements (for example, a Likert scale with entries, such as strongly agree, agree, neutral, disagree, strongly disagree).
  • Names that are not in any specific order (for example, item names, geographic names, or the names of people).

 Note   To present data in a 3-D format that uses three axes (a horizontal axis, a vertical axis, and a depth axis) that you can modify, use a 3-D column chart subtype instead.

  • Stacked column and stacked column in 3-D    Stacked column charts show the relationship of individual items to the whole, comparing the contribution of each value to a total across categories. A stacked column chart displays values in 2-D vertical stacked rectangles. A 3-D stacked column chart displays the data by using a 3-D perspective only. A third value axis (depth axis) is not used.

Stacked column chart and stacked column chart in 3-D

You can use a stacked column chart when you have multiple data series and when you want to emphasize the total.

  • 100% stacked column and 100% stacked column in 3-D    100% stacked column charts and 100% stacked column in 3-D charts compare the percentage that each value contributes to a total across categories. A 100% stacked column chart displays values in 2-D vertical 100% stacked rectangles. A 3-D 100% stacked column chart displays the data by using a 3-D perspective only. A third value axis (depth axis) is not used.

100% stacked column chart and 100% column chart in 3-D

You can use a 100% stacked column chart when you have three or more data series and you want to emphasize the contributions to the whole, especially if the total is the same for each category.

3-D column chart

You can use a 3-D column chart when you want to compare data across the categories and across the series equally, because this chart type shows categories along both the horizontal axis and the depth axis, while the vertical axis displays the values.

  • Cylinder, cone, and pyramid    Cylinder, cone, and pyramid charts are available in the same clustered, stacked, 100% stacked, and 3-D chart types that are provided for rectangular column charts, and they show and compare data exactly the same way. The only difference is that these chart types display cylinder, cone, and pyramid shapes instead of rectangles.

Cylinder, cone, and pyramide chart

Top of Page Top of Page

Create an elaborate column chart

Formatted stacked column chart

So, how did we create this column chart? The following procedure will help you create a column chart with similar results. For this chart, we used the example worksheet data. You can copy this data to your worksheet, or you can use your own data.

  1. Copy the example worksheet data into a blank worksheet, or open the worksheet that contains the data that you want to plot into a column chart.

ShowHow to copy the example worksheet data

  • Create a blank workbook or worksheet.
  • Select the example in the Help topic.

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  • Press CTRL+C.
  • In the worksheet, select cell A1, and press CTRL+V.

 
1
2
3
4
5
6
A B C D E
Sales Territory Qtr 1 Qtr 2 Qtr 3 Qtr 4
Northwest 3,767,341 3,298,694 2,448,772 1,814,281
Northeast 2,857,163 3,607,148 1,857,156 1,983,931
Central 3,677,108 3,205,014 2,390,120 1,762,757
Southwest 4,351,296 3,366,575 2,828,342 1,851,616
Southeast 2,851,419 3,925,071 1,853,422 2,158,789

 

  1. Select the data that you want to plot in the column chart.
  2. On the Insert tab, in the Charts group, click Column.

Excel Ribbon Image

  1. Under 3-D Column, select Stacked Column in 3-D.
  2. Click the chart area (chart area: The entire chart and all its elements.) of the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

  1. On the Design tab, in the Chart Layouts group, select the layout that you want to use.

Excel Ribbon Image

 Tip   For our column chart, we used Layout 3.

  1. On the Design tab, in the Chart Styles group, click the chart style that you want to use.

Excel Ribbon Image

 Tip   For our column chart, we used Style 34.

  1. In the chart, click the title, and then type the title text that you want.

 Tip   For our column chart, we used the title Regional Sales in North America.

  1. In the chart, click the vertical (value) axis, or select it from a list of chart elements (Format tab, Current Selection group, Chart Elements box).
  2. On the Format tab, in the Current Selection group, click Format Selection.
  3. Under Axis Options, in the Display units box, click Millions, and then click Close.
  4. In the chart, click a data series, or select it from a list of chart elements (Format tab, Current Selection group, Chart Elements box).
  5. On the Format tab, in the Shape Styles group, click Shape Effects, and then click Bevel.

Excel Ribbon Image

  1. Click 3-D Options, and then under Bevel, click the Top and Bottom bevel options that you want to use.

 Tip   For our column chart, we used Soft Round.

  1. In the Width and Height boxes for Top and Bottom bevel options, type the point size that you want.

 Tip   For our column chart, we used 12 pt for top width, 4 pt for top height, 6 pt for bottom width, and 6 pt for bottom height.

  1. Under Surface, click Material, and then click the material option that you want to use.

 Tip   For our column chart, we used Plastic.

  1. In the chart, click another data series, and then repeat steps 13 through 16.
  2. Keep the last data series selected and the dialog box open after you have formatted all data series, and then click Series Options in the Format Data Series dialog box.
  3. Under Gap Width, drag the slider to the gap width that you want.

 Tip   For our column chart, we dragged the slider to No Gap (0%).

  1. Keep the dialog box open, and then click the chart area of the chart.
  2. In the dialog box, click Border Styles, and then select the Rounded corners check box.
  3. If you want to use theme colors that are different from the default theme that is applied to your workbook, do the following:
  1. On the Page Layout tab, in the Themes group, click Themes.

Themes group on Excel Ribbon

  1. Under Built-in, click the theme that you want to use.

 Tip   For our column chart, we used the Median theme.

Top of Page Top of Page

Change the spacing between the columns

You can enlarge or reduce the space between the data markers (data marker: A bar, area, dot, slice, or other symbol in a chart that represents a single data point or value that originates from a worksheet cell. Related data markers in a chart constitute a data series.) or columns in a column chart. In a 2-D column chart, the columns can even overlap each other.

  1. In a chart, click the data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) that you want to change, or do the following to select the data series from a list of chart elements:
    1. Click the chart.

This displays the Chart Tools, adding the Design, Layout, and Format tabs.

  1. On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the data series.

Excel Ribbon Image

  1.  Tip   You need to select only a single data series to change the spacing of all data series of the same chart type.

  2. On the Format tab, in the Current Selection group, click Format Selection.
  3. Under Series Options, do one of the following:
  • To change the overlap of columns in a 2-D column chart, drag the slider to the percentage of series overlap that you want, or enter a percentage between -100 and 100 in the Series Overlap box.

 Tip   The higher the value, the greater the overlap within the category.

  • To change the spacing between categories of data markers in a 2-D or 3-D column chart, drag the slider to the percentage of gap width that you want, or enter a value between 0 (zero) and 500 in the Gap Width box.

 Tip   The higher the value, the greater the spacing between the columns.

  • To change the spacing between the data series in a 3-D column chart, enter a value between 0 (zero) and 500 in the Gap Depth box.

 Tip   The higher the value, the greater the distance between the columns.

Top of Page Top of Page

Simulate a floating column chart

Floating charts are useful when you want the columns in the column chart to depict minimum and maximum values. Excel does not provide a floating column chart type. However, when you create a stacked column chart that contains two (or more) data series, you can make the first data series invisible to give the second data series a floating column chart effect.

Floating column chart

  1. On the worksheet, select the data that you want to plot into a floating column chart.
  2. On the Insert tab, in the Charts group, click Column.

Excel Ribbon Image

  1. Under 2-D Column, click Stacked Column.

 Tip    For a 3-D effect, under 3-D Column, click Stacked Column in 3-D.

  1. In the chart, click the first data series, or select it from a list of chart elements (Format tab, Current Selection group, Chart Elements box).
  2. On the Format tab, in the Current Selection group, click Format Selection.

Excel Ribbon Image

  1. Click Fill, and then click No fill.

Top of Page Top of Page

Save a chart as a template

If you want to create another chart like the one that you just created, you can save the chart as a template that you can use as the basis for other similar charts.

  • Click the chart that you want to save as a template.
  • On the Design tab, in the Type group, click Save as Template.

Type group on Excel Ribbon

  • In the File name box, type a name for the template.

 Tip   Unless you specify a different folder, the template file (.crtx) will be saved in the Charts folder, and the template becomes available under Templates in both the Insert Chart dialog box (Insert tab, Charts group, Dialog Box Launcher Button image ) and the Change Chart Type dialog box (Design tab, Type group, Change Chart Type).

For more information about how to apply a chart template, see Reuse a favorite chart by using a chart template.

 Note   A chart template contains chart formatting and stores the colors that are in use when you save the chart as a template. When you use a chart template to create a chart in another workbook, the new chart uses the colors of the chart template — not the colors of the document theme that is currently applied to the workbook. To use the document theme colors instead of the chart template colors, right-click the chart area (chart area: The entire chart and all its elements.), and then click Reset to Match Style on the shortcut menu.

Top of Page Top of Page

 
 
Applies to:
Excel 2007, Outlook 2007