Excel Chart Types: Pie, Column, Line, Bar, Area, and Scatter
This tutorial discusses the six common chart types supported by Microsoft Excel and provides many sample charts. Each chart's sub-types, such as stacked, 100% stacked, and 3-D, are also discussed. We begin our tutorial by discussing what a "data series" is.
Author: Keynote Support
For step-by-step directions on how to create an Excel chart in all versions of Excel, please see our tutorial Beginner's Guide to Creating Excel Charts.
If you like video-based introduction, check out Excel 2010 Tutorial for Beginners. We've have a copy of this course (over 9 hours of hands-on lessons) and find it very well done. And you can preview several chapters online!
Let's begin. You may click on a link below to go directly to that topic.
Defining Data Series
A data series is a related set of data points. It is usually one row of data in an Excel worksheet with the associated column headings; or one column of data with the associated row headings. All chart types can plot both single and multiple data series except the Pie Chart - as you will see shortly. In the worksheet below, we have outlined in red a single series of data.
In the worksheet below, we have outlined in red 3 series of data. As discussed in How to Create Excel Charts, we can select non-adjacent rows in the spreadsheet to chart by pressing and holding the Ctrl key as we highlight the rows and/or columns.
The Pie Chart
A Pie Chart can only display one series of data. Excel uses the series identifier as the chart title (e.g. Flowers) and displays the values for that series as proportional slices of a pie. If we had selected multiple series of data, Excel would ignore all but the first series.
There are sub-types of the Pie Chart available. The second chart above is the Pie in 3-D and the third chart is an Exploded Pie Chart; an Exploded Pie in 3-D is also available. Several other sub-types include the Pie of Pie and Bar of Pie - in which a second pie is created from certain values in the first pie in order to emphasize them. To customize the values that the second pie contains, right-click on the segment in the first pie, select "Format Data Point," and specify how to split the series.
Notice that the Pie Chart's legend contains the column headings from the worksheet. These can be changed by editing the headings in the worksheet, or by editing the chart directly. The legend can be moved to the top, bottom, left, right, or top right ("corner" in older versions of Excel) of the chart.
It is possible to customize the design of the pie chart so either the numeric values or the percentages display inside the chart on top of the slices of the pie.
The Column Chart
The Column Chart very effectively shows the comparison of one or more series of data points. But the Clustered Column Chart is especially useful in comparing multiple data series. In the chart at right, we plotted the data points in all three series: Flowers, Shrubs, and Trees. Because Excel uses a different color for each data series, we can easily see how a single series, Flowers for example, changes over time. But because the columns are "clustered," we can also compare the three data series for each time period.
In a Column Chart, the vertical axis (Y-axis) always displays numeric values, and the horizontal axis (X-axis) displays time or other category. The horizontal axis (X-axis) in our charts displays our time segments, and the series type (Flowers, Shrubs, and Trees) is plotted per time segment. Excel has designed the chart in this manner because the number of time segments (4) is greater than the number of series (3). Whichever has the highest quantity will be placed on the horizontal axis (X-axis).
In other words, we could have Flowers, Shrubs, and Trees run along the X-axis, and the value of the four quarters plotted for each. For more customizing information, see How to Customize for a GREAT-Looking Excel Chart.
One variation of this chart type is the Stacked Column Chart. We show a 3-D Stacked Column Chart at left. In a Stacked Column Chart, the data points for each time period are "stacked" instead of "clustered." This chart type lets us see the percentage of the total for each data point in the series.
Also available is the 100% Stacked Column Chart, where each value in a series is shown as a portion of 100%. An example of a 100% Stacked Chart is shown in the section on Bar Charts.
All the Column Charts have a version in which the columns display in three-dimension - as illustrated by the 3-D Stacked Column Chart above. But one chart, the "3-D Column Chart," is special because the chart itself is three-dimensional - displaying multiple series on the X-axis, Y-axis, and Z-axis. The first chart below is a 3-D Column Chart of our data series.
In newer versions of Excel, cylinders, pyramids, and cones can be used instead of bars for most of the Column charts. The second chart above shows a 3-D Pyramid Chart.
The Line Chart
The Line Chart is especially effective in displaying trends. In a Line Chart, the vertical axis (Y-axis) always displays numeric values and the horizontal axis (X-axis) displays time or other category.
We selected the Line with Markers chart for our single series chart at left. You may choose each Line Chart type with or without markers. Markers are circles, squares, triangles, or other shapes which mark the data points. Excel displays a unique marker - different shape and/or color - for each data series.
The Line Chart is equally effective in displaying trends for multiple series as shown in our chart at right. As you will notice, each line is a different color. This image shows a Line Chart without markers.
Though not as colorful as the other charts, it is easy to see how effective the Line Chart in showing a trend for a single series, and comparing trends for multiple series of data values.
Besides the Line Chart, we have the Stacked Line Chart and the 100% Stacked Line Chart - with or without markers. A 3-D Line Chart is available, but the Line Chart does not display data well in three dimensions.
The Bar Chart
The Bar Chart is like a Column Chart lying on its side. The horizontal axis of a Bar Chart contains the numeric values. The first chart below is the Bar Chart for our single series, Flowers.
When to use a Bar Chart versus a Column Chart depends on the type of data and user preference. Sometimes it is worth the time to create both charts and compare the results. However, Bar Charts do tend to display and compare a large number of series better than the other chart types.
All of the Bar Charts are available in 2-D and 3-D formats, but only the bars are 3-D. There is no 3-D Bar chart containing three axes.
As with the other chart types, Excel provides the Stacked Bar Chart and 100% Stacked Bar Chart. The second chart above is our 100% Stacked Bar Chart in 3-D. This chart type doesn't display currency on the horizontal axis, but percentages. It allows us to see what percentage each data point has out of 100%.
As with the other chart types, new versions of Excel provide the option of using cylinders, pyramids, or cones instead of bars.
The Area Chart
Area Charts are like Line Charts except that the area below the plot line is solid. And like Line Charts, Area Charts are used primarily to show trends over time or other category. The chart at left is an Area Chart for our single series.
There are three charts available: the Area Chart, the Stacked Area Chart, and the 100% Stacked Area Chart. Each of these charts come in 2-D format and in true 3-D format with X, Y, and Z axes. The chart at right is our 3-D Area Chart, and effectively displays our three series.
In many cases, the 2-D version of the Area Chart can be ineffective in displaying multiple series of data meaningfully. Series with lesser values may be completely hidden behind series with greater values - as demonstrated in the first chart below. Flowers is totally hidden, and just a wee bit of Trees peaks through. Not a very effective chart! This problem does not occur in the Stacked Area Chart (shown below) or the 100% Stacked Area Chart.
The Scatter Chart
The purpose of a Scatter Chart is to observe how the values of two series compares over time or other category. To illustrate the Scatter Chart, we will use the worksheet values shown below:
According to Scatter Plots (U. of Illinois), "Scatter plots are similar to line graphs in that they use horizontal and vertical axes to plot data points. However, they have a very specific purpose. Scatter plots show how much one variable is affected by another. The relationship between two variables is called their correlation."
The series pair has a Positive Correlation if they increase similarly, and a Negative Correlation if they both decrease in like manner. Otherwise, they have No Correlation.
Excel does not use labels from the worksheet to label the horizontal axis; it just numbers the X-axis chronologically.
The Scatter Chart comes in several different formats: markers can indicate the data points; and the points can be unconnected, or connected with smooth or straight lines.
The first chart below is our Scatter Chart with Only Markers, and the second chart is a Scatter Chart with Smooth Lines. In general, markers work well when the number of data points is small, and smooth lines without markers are often used when the number of data points is large. But it is best to try the different sub-types to see which one best presents your data.
For another good discussion on Scatter Plots, see Scatter Plots - U. of Illinois.
Other Chart Types
Excel offers other chart types, depending on your version, but the average user will not use these types of charts. Some of the other available chart types are: Stock, Surface, Doughnut, Bubble, and Radar.
In Conclusion ...
Creating a standard chart in Excel takes a mnute. Customizing a chart can take a long time unless you follow a logical order (see Customizing Excel Charts for customizing charts in Excel 2007 and 2010). If you want to dig really deep into Excel charting, check out the charting tutorials at Peltier Technical Services.
We hope this article has been helpful. Cheers!
Please link to this tutorial using the following HTML:
<a href="http://www.keynotesupport.com/excel-chart-types.shtml">Excel Chart Types: Pie, Column, Line, Bar, Area, and Scatter</a>
Return to the top of Excel Chart Types: Pie, Column, Line, Bar, Area, and Scatter