Chart Type Examples
After you select the data to use in a chart, the next step is to select the type of chart. The commands in the Insert > Charts group are all drop-down controls. Click a control, and you see icons that represent the subtypes for the chart type. For example, a Line chart has seven subtypes.
Column charts
Probably the most common chart type is column charts. Column charts actually come in several different variations (technically known as subtypes). The main difference between the basic column chart and these subtypes is how they deal with data tables that have multiple series.
A column chart displays each data point as a vertical column, the height of which corresponds to the value. The value scale is displayed on the vertical axis,which is usually on the left side of the chart. You can specify any number of data series, and the corresponding data points from each series can be stacked on top of each other. Typically, each data series is depicted in a different color or pattern.
Column charts are often used to compare discrete items, and they can depict the differences between items in a series or items across multiple series. Excel offers seven column-chart subtypes.
In order to learn about a chart subtype, you need to know its name. The name appears when you hover over the subtype thumbnail, either in the Insert Charts list or the Insert Chart dialog box.
Quick summary of column chart choices
Clustered Column
In a clustered column, each value's shown in its own separate column. To form a cluster, the columns are grouped together according to category.
Stacked Column
In a stacked column, each category has only one column. To create this column, Excel adds together the values from every series for each category. However, the column is subdivided (and color-coded), so you can see the contribution each series makes.
100% Stacked Column
The 100% stacked column is like a stacked column in that it uses a single bar for each category, and subdivides that bar to show the proportion from each series. The difference is that a stacked column always stretches to fill the full height of the chart. That means stacked columns are designed to focus exclusively on the percentage distribution of results, not the total numbers.
3-D Clustered Column, Stacked Column in 3-D, and 100% Stacked Column in 3-D
Excel's got a 3-D version for each of the three basic types of column charts, including clustered, stacked, and 100 percent stacked. The only difference between the 3-D versions and the plain-vanilla column charts is that the 3-D charts are drawn with a three-dimensional special effect, that's either cool or distracting, depending on your perspective.
3-D Column
While all the other 3-D column charts simply use a 3-D effect for added pizzazz, this true 3-D column chart actually uses the third dimension by placing each new series behind the previous series. That means if you have three series, you end up with three layers in your chart. Assuming the chart is tilted just right, you can see all these layers at once, although it's possible that some bars may become obscured, particularly if you have several series.
Along with the familiar column and three-dimensional column charts, Excel also provides a few more exotic versions that use cylinders, cones, and pyramids instead of ordinary rectangles. Other than their different shapes, these chart types work just like regular column charts. As with column and bar charts, you can specify how cylinder, cone, and pyramid charts should deal with multiple series. Your options include clustering, stacking, 100% stacking, and layering (true 3-D).
Bar Charts
Many people use bar charts because they leave more room for category labels. If you have too many columns in a column chart, Excel has a hard time fitting all the column labels into the available space.
A bar chart is essentially a column chart that has been rotated 90 degrees clockwise. One distinct advantage to using a bar chart is that the category labels may be easier to read. Excel offers six bar chart subtypes. The venerable bar chart is the oldest form of data presentation. Invented sometime in the 1700s, it predated the column and pie chart.
Excel provides almost the same set of subtypes for bar charts as it does for column charts. The only difference is that there's no true three-dimensional (or layered) bar chart, although there are clustered, stacked, and 100% stacked bar charts with a three-dimensional effect. Some bar charts also use cylinder, cone, and pyramid shapes.
Line charts
Line charts are often used to plot continuous data and are useful for identifying trends. For example, plotting monthly sales as a line chart may enable you to identify sales fluctuations over time. Normally, the category axis for a line chart displays equal intervals. Excel supports seven line chart subtypes.
A line chart can use any number of data series, and you distinguish the lines by using different colors, line styles, or markers.
Quick overview:
Line
The classic line chart, which draws a line connecting all the points in the series. The individual points aren't highlighted.
Stacked Line
In a stacked line chart, Excel displays the first series just as it would in the normal line chart, but the second line consists of the values of the first and second series added together. If you have a third series, it displays the total values of the first three series, and so on. People sometimes use stacked line charts to track things like a company's cumulative sales (across several different departments or product lines). Stacked line charts aren't as common as stacked bar and column charts.
100% Stacked Line
A 100% stacked line chart works the same as a stacked line chart in that it adds the value of each series to the values of all the preceding series. The difference is that the last series always becomes a straight line across the top, and the other lines are scaled accordingly so that they show percentages. The 100% stacked line chart is rarely useful, but if you do use it, you'll probably want to put totals in the last series.
Line with Markers, Stacked Line with Markers, and 100% Stacked Line with Markers
These subtypes are the same as the three previous line chart subtypes, except they add markers (squares, triangles, and so on) to highlight each data point in the series.
3-D Line
This option draws ordinary lines without markers but adds a little thickness to each line with a 3-D effect.
Pie Charts
Pie charts can show only one series of data. If you create a pie chart for a table that has multiple data series, you'll see just the information from the first series. The only solution is to create separate pie charts for each series.
A pie chart is useful when you want to show relative proportions or contributions to a whole. A pie chart uses only one data series. Pie charts are most effective with a small number of data points. Generally, a pie chart should use no more than five or six data points (or slices). A pie chart with too many data points can be very difficult to interpret.
The values used in a pie chart must all be positive numbers. If you create a pie chart that uses one or more negative values, the negative values will be converted to positive values—which is probably not what you intended!
Overview of pie chart subtypes:
Pie
The basic pie chart everyone knows and loves, which shows the breakup of a single series of data.
Exploded Pie
The name sounds like a Vaudeville gag, but the exploded pie chart simply separates each piece of a pie with a small amount of white space. Usually, Excel charting mavens prefer to explode just a single slice of a pie for emphasis. This technique uses the ordinary pie subtype, as explained in the next chapter.
Pie of Pie
With this subtype, you can break out one slice of a pie into its own, smaller pie (which is itself broken down into slices). This chart is great for emphasizing specific data; it's demonstrated in the next chapter.
Bar of Pie
The bar of pie subtype is almost the same as the pie of pie subtype. The only difference is that the breakup of the combined slice is shown in a separate stacked bar, instead of a separate pie.
Pie in 3-D and Exploded Pie in 3-D
This option is the pie and exploded pie types in three dimensions, tilted slightly away from the viewer for a more dramatic appearance. The differences are purely cosmetic.
XY (scatter) charts
Another common chart type is an XY chart (also known as scattergrams or scatter plots). An XY chart differs from most other chart types in that both axes display values. (An XY chart has no category axis). This type of chart often is used to show the relationship between two variables.
Scatter charts are common in scientific, medical, and statistical spreadsheets. They're particularly useful when you don't want to connect every dot with a straight line. Instead, scatter charts let you use a smooth "best fit" trendline, or omit the line altogether. If you plot multiple series, the chart uses a different symbol (like squares, triangles, and circles) for each series, ensuring that you can tell the difference between the points.
Scatter chart subtypes overview:
Scatter with Only Markers
This scatter chart uses data markers to show where each value falls. It adds no lines.
Scatter with Smooth Lines and Markers
This scatter chart adds a smooth line that connects all the data points. However, the points are connected in the order they occur in the chart, which isn't necessarily the correct order. You're better off adding a trendline to your chart.
Scatter with Straight Lines and Markers
This subtype is similar to the scatter chart with smoothed lines, except it draws lines straight from one point to the next. A line chart works like this, and this subtype makes sense only if you have your values in a set order (from lowest to largest or from the earliest date to the latest).
Scatter with Smooth Lines and Scatter with Straight Lines
These subtypes are identical to the scatter with smooth lines and markers and the scatter with straight lines and markers. The only difference is they don't show data markers for each point. Instead, all you see is the line.
Area charts
Think of an area chart as a line chart in which the area below the line has been colored in. An area chart is very similar to a line chart. The difference is that the space between the line and the bottom (category) axis is completely filled in. Because of this difference, the area chart tends to emphasize the sheer magnitude of values rather than their change over time.
Area charts exist in all the same flavors as line charts, including stacked and 100% stacked. You can also use subtypes that have a 3-D effect, or you can create a true 3-D chart that layers the series behind one another.
Doughnut charts
A doughnut chart is similar to a pie chart, with two exceptions: It has a hole in the middle, and it can display more than one series of data. Doughnut charts are listed in the Other Charts category.
The donut chart is actually an advanced variation on that other classic food-themed chart, the pie chart. But while a pie chart can accommodate only one series of data, the donut can hold as many series as you want. Each series is contained in a separate ring. The rings are one inside the other, so they all fit into a single compact circle.
The donut chart's ideal for comparing the breakdown of two different sets of data. However, the data on the outside ring tends to become emphasized, so make sure this series is the most important.
Donuts have two subtypes: standard and exploded. (An exploded donut doesn't suggest a guilty snack that's met an untimely demise. Instead, it's a donut chart where the pieces in the topmost ring are slightly separated.)
Although the donut chart can hold as many series as you want, if you add more than two or three, the chart may appear overly complicated. No matter what you do, the center of the donut never gets filled in (unless you decide to add some text there using Excel's drawing tools.
Radar charts
Radar charts are listed in the Other Charts category. You may not be familiar with this type of chart. A radar chart is a specialized chart that has a separate axis for each category, and the axes extend outward from the center of the chart. The value of each data point is plotted on the corresponding axis.
There are three radar subtypes: the standard radar chart; a radar chart with data markers indicating each point; and a filled radar, where each series appears as a filled shape, somewhat like an area chart. No matter what subtype you use, choosing data for a radar chart isn't easy.
Surface charts
Surface charts display two or more data series on a surface. Surface charts are listed in the Other Charts category. It’s important to understand that a surface chart does not plot 3-D data points. The series axis for a surface chart, as with all other 3-D charts, is a category axis—not a value axis. In other words, if you have data that is represented by x, y, and zcoordinates, it can’t be plotted accurately on a surface chart unless the x and y values are equally spaced.
A surface chart shows a 3-D surface that looks a little like a topographic map, complete with hills and valleys. Surface charts are different from most other charts in that they show the relationship of three values. Two category axes (X and Y) determine a data point's position. The value determines the height of the data point (technically known as the Z-axis). All the points are linked to create a surface.
Surface charts are neat to look at, but ordinary people almost never create them as they're definitely overkill for tracking your weekly workout sessions. For one thing, to make a good surface chart, you need a lot of data. (The more points you have, the smoother the surface becomes.) Your data points also need to have a clear relationship with both the X and Y axes (or the surface you create is just a meaningless jumble). Usually, rocket-scientist types use surface charts for highly abstract mathematical and statistical applications.
Bubble charts
Think of a bubble chart as an XY chart that can display an additional data series, which is represented by the size of the bubbles. As with an XY chart, both axes are value axes (there is no category axis). Bubble charts are listed in the Other Charts category.
Stock charts
Stock charts are most useful for displaying stock-market information. These charts require three to five data series, depending on the subtype. This chart type is listed in the Other Charts category. Usually, these charts show how a stock value changes over a series of days. The twist is that the chart can display information about the daytime high and the daytime low of the stock, along with its opening and closing value. Excel uses all this information to draw a vertical bar from the stock's low point to its high point on a given day. If you're really ambitious, you can even add volume information (which records the number of shares traded on a given day).
Stock charts are more rigid than most other chart types. In order to use a stock chart, you need to create a column of numbers for each required value. The type of columns you need and their order depends on the stock chart subtype that you select. Here are your choices:
- High-Low-Close
- Open-High-Low-Close
- Volume-High-Low-Close
- Volume-Open-High-Low-Close
In each case, the order of terms indicates the order of columns you should use in your chart. If you select Volume-High-Low-Close, then the leftmost column should contain the volume information, followed by another column with the stock's daytime high, and so on. (Technically, you can use the Chart Tools : Design > Data > Select Data command to specify each series, even if it's not in the place Excel expects it to be. However, this maneuver is tricky to get right, so it's easiest to just follow the order indicated by the chart type name.) No matter which subtype you use, a stock chart shows only values for a single stock.
The simplest stock chart (High-Low-Close) is also occasionally useful for charting variances in scientific experiments or statistical studies. You could use a stock chart to show high and low temperature readings. Of course, you still need to follow the rigid stock chart format when ordering your columns.
Charting a Table
You can use the Excel table feature with charts. Tables and charts make a perfect match. Tables grow and shrink dynamically in size as you add or delete records. If a chart's bound to a table, the chart updates itself as you add new information or remove old data.
If you've already created the chart with an ordinary range of cells, you can still use a table, all you need to do is convert the linked range to a table.
- Select the range of cells that contain all the data, not including the chart's title
- Select Insert > Tables > Table.
Now, as you add new items to the table, Excel adds them to the chart immediately.
When you chart a table, you also gain the ability to use other features, like easier sorting and filtering. You can use sorting to determine the order that items appear within a chart (which is occasionally useful), and you can use filtering to hide rows and to chart only a portion of the data (which is often indispensable). If you apply a filter condition that shows only the three best performing regions, the chart updates itself so that it shows only this data.