Chart types

Column charts

Column charts are typically used for comparing categories of data or for displaying changes in data over time. Typically, column charts are used with small data sets. Consider using line charts for larger data sets.

In most cases, you can use either the rows or the columns of data for the categories. The data which is not used for the categories is used for comparison and is displayed in the chart legend. An exception is, for example, pie charts which, with the exception of the Tiled subtype, have only one data series.

The categories are displayed on the X axis and the measure on the Y axis.

Column charts are also available as sparklines.

Column chart subtypes

Stacked, and Stacked Percentage are the two main subtypes of Column charts. Three-dimensional versions are also available as subtypes.

Bar charts

Bar charts are the same as column charts except that the categories are displayed on the Y axis and the measure on the X axis. So, the data is displayed horizontally in bars instead of vertically in columns.

Bar charts can be useful if the names of the categories are long and if the measure represents units of duration.

Bar charts have the same subtypes as Column charts except that there is no 3 axis bar subtype.

Bar charts are also available as sparklines.

Line charts

Line charts show the changing values or trends of categories of data over a continuous period. Typically, the categories on the X axis of a line chart represent units of time such as months or years. Each line in a line chart is represented in the chart legend by one of eleven distinctive icons. If more than eleven elements are compared the icons are reused, reducing the clarity of the chart.

Line charts are also available as sparklines.

Line chart subtypes

Stacked and Stacked Percentage are the two main subtypes of Line charts. They show how the size of an element's contribution to the chart total changes over time.

Further subtypes are versions of the charts which display markers at each data point.

A 3D subtype is also available. The axes and lines of the chart are displayed in three dimensions, displaying the lines as ribbons.

Profile charts

Profile charts are the same as line charts but the lines are vertical. That is, the categories are displayed on the Y axis and the measure is displayed on the X axis.

Profile chart subtypes

Profile charts have the same two main subtypes as Line charts. That is, Stacked, and Stacked Percentage. Both Stacked and Stacked Percentage charts can be displayed with markers at each data point.

There are no three-dimensional subtypes for Profile charts.

Pie charts

With the exception of Tiled pie charts, Pie charts have only one data series. That is, the data on which they are based is held in one column.

Each item in the data series is displayed as a segment of the pie chart and represents a proportion of the total value of the data series.

We recommend that you do not use Pie charts in these cases:

• There are more than 8 segments
• There are negative values
• There are zero values

Pie chart subtypes

Separated pie

The segments of the pie chart are separated.

Pie of pie and Bar of pie

These subtypes create a separate pie or bar chart adjacent to the main pie chart. You might use this, for example, to highlight, and add detail to, small segments of chart data. By default, the separate chart is based on the last two values in the data series but you can specify which values are used. For example, specify that the last three values are used, or values below a specified value or percentage. In the main pie chart, the segments on which the separate chart is based are combined into one.

You can also specify the distance between the charts and the size of the second chart.

Tiled pie

Tiled pie charts are based on two or more columns of data. The default selection, 'Data in: Columns' in the Chart Wizard generates a separate small pie chart for each row of data. Each pie chart has as many segments as there are columns of data.

If you select Data in: Rows in the Chart Wizard, a separate pie chart is generated for each column of data. Each chart has as many segments as there are rows of data.

If the columns or rows of data do not have headings, the individual pie charts are automatically, sequentially, numbered.

Three dimensional pie charts

Pie charts, Separated pie charts and Tiled pie charts have three dimensional subtypes. Pie of pie and Bar of pie charts are available only in two dimensions.

XY scatter charts

Scatter charts are typically used with large data sets that are based on pairs of values, rather than on categories and values.

XY scatter charts require three columns or rows of data. One column supplies the X-axis values, such as dates. Each remaining column supplies one of each pair of values. Environmental data can provide some uses of scatter charts. For example, a data set can record thickness of lake ice and mean winter temperatures over the years. This table shows sample data for a scatter chart to plot the thickness of ice against the mean winter temperature:

To plot, for example, mean winter temperatures over the years, you should use a line chart.

Scatter chart subtypes

You can use lines to join data points of scatter charts. The profile of the lines can be smooth or angular. If you use lines, you can show them with or without markers at each data point.

Combo charts

Area charts

Area charts illustrate magnitude: for example, the size of an organization's profits over time. Each data series is displayed, by default, as a solid area of color. This can result in smaller data series being completely obscured by larger data series. Often, the solution is to use a stacked or 3D subtype. Or, you can assign different degrees of transparency to each data series so that other data series are visible behind.

Area chart subtypes

The two main subtypes for Area charts are Stacked and Stacked percentage charts. The areas of a stacked area chart show how the size of an element's contribution to the chart's total changes over time. A stacked percentage chart shows the size of an element's contribution as a percentage of the total. Both Stacked and Stacked percentage subtypes have three-dimensional subtypes. Although having the appearance of three dimensions, these subtypes have only two axes.

A 3D subtype is also available which displays the data against three axes.

Doughnut charts

Doughnut charts display concentric rings. Each column or row of data forms a separate ring. Each ring is segmented according to the number of rows or columns in the data. Each segment represents a proportion of the total value of the row.

For example, this data would generate a Doughnut chart with three rings:

The inner ring would be divided into three equal segments of different colors.

The middle ring would be unsegmented.

The outer ring would be have three unequal segments representing the values in Row 3 Column 1, Row 3 Column 2 and Row 3 Column 3.

You might consider using a Tiled Pie chart as an alternative to a Doughnut chart.

You can specify the size of the hole at the center of the Doughnut chart. If required, you can rotate the inner ring by a specified degree.

Radar charts

Radar charts can be useful for making comparisons between different items, using a standard set of criteria. For example, you might want to assess different candidates' suitability for a particular job against an ideal profile, or to compare similar products from different vendors.

Radar charts have an axis for each row of data. For example, if there were a row for each month of a year, there would be 12 axes meeting at a central point, like the spokes of a wheel.

You can plot multiple rows in a chart, or create a separate radar chart for each row of data. The data points are joined, forming a distinctive shape for each row. The different shapes can give a quick visual indication of similar and dissimilar rows - particularly if each row is displayed in a separate chart.

For example, this data would plot four shapes on a chart with six axes. Each axis represents a key criterion measured from 5 (strong match) to 1 (strong mismatch). The ideal candidate is reliable, punctual, logical and a team player, but not particularly creative or easily bored:

If you were to create a radar chart for each row, it would be apparent from the shapes that candidates 2 and 4 most closely match the ideal.

Radar chart subtypes

Radar charts can be displayed with or without markers on the data points.

The data points on a Radar chart are joined by lines. In a filled Radar chart, the area bordered by the lines can be filled with a color.

Two further subtypes are Radar chart with data labels and Radar chart with markers and data labels. Because of the space occupied by the markers and data labels, these subtypes are most suitable for Radar charts with few axes and few columns.

Portfolio

You might know this as a Bubble chart.

Portfolio charts display circles of varying sizes within their X and Y axes. The sizes of the circles represent the magnitude of the values displayed. So, both the size of the circles and their relative positions within the X and Y axes describe the data which they represent. You can specify whether the size represents the area of the circles or their diameters.

You can use portfolio charts if each data series is expressed by three numerical values (for example, actual sales, budgeted sales, and the variance between them).

If your data is in columns A, B and C, the data in column A forms the X axis. The data in column B forms the Y axis and the data in column C determines the radii of the circles. Each row forms a data series.

If you add data to columns D and E, column D forms the Y axis of another data set. Column E determines the radius of the circles in that series. Similarly, if you add data in columns F and G, these become the Y axis and radius of a third data set.

Portfolio chart subtypes

One subtype renders the circles with a three-dimensional effect.

The remaining subtypes divide the background of the chart, or Plot Area, into different arrangements of quadrants of different colors. By choosing the appropriate subtype and by re-sizing the quadrants, you can configure a chart so that a specific data series appear on a quadrant of a particular color. For example, display values below a specific level on a red background to indicate a poor result and other values on a yellow or green background to indicate adequate or good results.

By default, the quadrant subtypes display data labels. You can specify whether the circles are labeled with the size of the bubble or of the value which they represent.

To render the data series of a quadrant subtype as bubbles rather than as circles, use the gradients and other options in the Format Data Series dialog box.

Quadrant charts

Quadrant charts are similar to the quadrant subtypes of portfolio charts.

Stock charts

Stock charts are also known as Candlestick charts. Each data series in a Stock chart is a 'high-low line' (candle wick) which represents several values. In a default Stock chart the values represented are High, Low, and Close. That is, each data series represents the highest price, lowest price and the closing price of a stock. On each line, a marker indicates the closing price. On the Options tab of the Format Data Series dialog, you can turn off the high-low lines and display only the closing price markers.

To create a stock chart or any of its subtypes, you select only the data - not the column and row headings. For this reason, it is essential that your data is in the correct order. That is, for the default chart, three columns or rows which represent, from left to right and top to bottom, High, Low, and Close.

Stock chart subtypes

Each Stock chart subtype has a different purpose and requires different data. The correct order of the columns or rows is essential.

Open, High, Low, Close

This Stock chart requires four data series which represent the opening price, highest price, lowest price and closing price of stocks. A block (candle body) on each data series (candle wick) represents the difference between the opening and closing prices. If the stock closes at a lower price than its opening price, the block is colored purple. If the stock closes at a higher price than its opening price, the block is colored grey.

Volume, High, Low, Close

This Stock chart requires four data series which represent the volume of stocks traded, their highest prices, lowest prices and closing prices. In addition to the lines which represent the highest, lowest and closing values, columns represent the volume of stocks traded. A marker on each line represents the closing price.

Volume, Open, High, Low, Close

This Stock chart requires five data series which represent the volume of stocks traded, their opening prices, highest prices, lowest prices and closing prices. In addition to the lines (which represent the opening, highest, lowest and closing values), columns represent the volume of stocks traded. Blocks (candle bodies) represent the difference between the opening and closing values. If the stock closes at a lower price than its opening price, the block is colored purple. If the stock closes at a higher price than its opening price, the block is colored grey.

Surface charts

A Surface chart is similar to, for example, a topographic map that represent heights at different latitudes and longitudes. In a topographic map, altitude, latitude and longitude can be considered variables Z, X and Y. Altitude (Z) changes with the values of variables X and Y. Surface charts can be used where the value of one variable is affected by the values of a pair of variables. Examples of use include testing of materials - for example, to test the mechanical strength of a material at different temperatures for different lengths of time.

A Surface chart has the appearance of a sheet of mesh which is twisted and bent to represent the variations in values. The cells of the mesh are filled with different colors which represent ranges of values.

The data for a Surface chart should be arranged with the X and Y variables as row and column headings.

Surface chart subtypes

Cylinder, Cone and Pyramid charts

Cylinder, Cone and Pyramid can be considered as further subtypes of both Column and Bar charts. They serve the same purpose and can use the same data as Column and Bar charts. Instead of bars or columns, these charts display cylinders, cones or pyramids. All three chart types are two dimensional with a 3D effect and all have Stacked and Stacked Percentage subtypes. Each type and subtype can be displayed either horizontally or vertically.

Milestone charts

A milestone chart shows the trend (late, early or on-time) of progress towards completion of project milestones.

The X axis shows the dates when you review the completion dates of project milestones. At each date, the Y axis shows the predicted completion date of the milestone.

A horizontal line on the chart indicates that the predicted completion date remains constant.

A rising line indicates that it is predicted that the milestone will be completed late.

A falling line indicates that the milestone is predicted to be completed early.

Milestone chart subtypes

Milestone charts have only one subtype - with markers at each data point.

Step charts

Like Line charts, Step charts represent changes to data over time. But Step charts represent data which changes only at intervals and which otherwise remains constant. So, for example, you might use a Step chart to represent changes to bank interest rates which typically change only each quarter, or even less frequently. But, to represent the daily fluctuations in currency exchange rates over a period you might use a Line chart.

A Step chart displays a horizontal line which rises or falls vertically at each change of value. You can use the 'Move line up first' option, of the Format Data Series dialog box, to specify of whether the steps occur on or between the markers on the axis.

Step chart subtypes

A Filled Steps Chart is the only subtype of Step charts. The area below the step line is filled with a color. If you have two or more data series, the data series with the highest values can wholly or partially mask the other data series. You can overcome this by assigning different degrees of transparency to each data series so that the other data series are visible behind.

Histogram

Histograms are used to analyze the frequency with which each value in a range of values occurs. Histograms can be created in Microsoft Excel but require the Analysis ToolPak add-in. In Application Studio, highlight the row or column of values to be analyzed and click Chart Wizard. Select Histogram as the chart type.

There are four chart subtypes. The first two require only values for the Y-axis. The value intervals on the X-axis are calculated automatically. The second chart includes the normal distribution curve. You can change the number of value intervals in the Format Data Series dialog.

The third and fourth chart subtypes require values for the Y-axis and for the value intervals on the X-axis. The fourth chart includes the normal distribution curve.

To add the normal distribution curve manually to the first or third chart subtypes, double-click the chart so that a hashed border is displayed. Right-click a data series and select Add Normal Distribution.

Tachometer

Each data series is displayed as a pointer to a value range. If the data contains three or more data series, three value ranges are displayed. Typically, Red, Yellow and Green are specified as the colors of the value ranges to represent, for example, Poor, Average and Good sales results.

You can specify the range of values to which each color applies, in either of these ways:

• Click a value range in Design Mode to select it. Then double-click to display the Value Range Series dialog. Specify the value range and the color which will represent it.
• Double-click a data series to display the Format Data Series dialog.

Multi-color bars and columns

There are four multi-color chart types. The default type is a stacked column chart. Each value is represented by a column which is divided into colored segments. Each color within the column represents a range of values. For example, you might classify sales by size of deal, with sales deals worth up to $10,000 represented by red, sales deals worth $10,001 to $20,000 by yellow, and sales deals worth over $20,000 by green. In this example, a deal worth $20,000 would be represented by a red and yellow column. The bottom half of the column would be red and reach 10,000 on the Y-axis. The top half would be yellow and would reach from 10,000 to 20,000 on the Y-axis. A deal worth $21,000 would be represented by a red, yellow, and green column, with the green segment reaching from 20,000 to 21,000 on the Y-axis.

One of the other chart types is a stacked bar chart.

The remaining chart types are a multi-color column chart and a multi-color bar chart. In these charts, each value is represented by a column or bar, colored according to the value that it represents.

To define the value ranges, right-click a data series in Design Mode and select Format Data Series. Click the Options tab of the Format Data Series dialog box.

Colored map

Colored maps are an interactive way to visualize how a measurement varies across a geographic area or to show variations within a region. The values represented by the map fall into a number of ranges. Each range is identified by a different color.

Colored map charts are implemented with the Colored map web extension and so are not available in the chart wizard.