Column charts are one of the most common chart types. 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.

Table 2-1 lists and describes Excel's 19 column chart subtypes.

Figure 2-4 shows an example of a clustered column chart that depicts annual sales for two products. From this chart, it is clear that Sprocket sales have always exceeded Widget sales. In addition, Widget sales have been declining over the years, whereas Sprocket sales are increasing.

Figure 2-4. This clustered column chart compares sales for two products.

The same data, in the form of a stacked column chart, is shown in Figure 2-5. This chart has the added advantage of depicting the combined sales over time (note the scale of the vertical axis). It shows that total sales have remained relatively steady over the years, but the relative proportions of the two products have changed.

Figure 2-5. This stacked column chart displays sales by product and depicts the total sales.

Figure 2-6 shows the same sales data plotted as a 100% stacked column chart. This chart type shows the relative contribution of each product by year. Notice that the value axis displays percentage values, not sales amounts. This chart provides no information about the actual sales volumes. This type of chart is often a good alternative to using several pie charts. Instead of using a pie to show the relative sales volume in each year, the chart uses a column for each year.

Figure 2-6. This 100% stacked column chart displays annual sales as a percentage.

3–D or not 3–D? That is the question

Some of Excel's charts are referred to as 3–D charts. This terminology can be somewhat confusing, because some of these so–called 3–D charts aren't technically 3–D charts. Rather, they are 2–D charts with a perspective look to them; that is, they appear to have some depth. The accompanying figure shows two "3–D" charts.

The chart on the left is a 3–D clustered column chart. However, it's not a true 3–D chart. It's just a 2–D chart that uses perspective to add depth to the columns. The chart on the right (a 3–D column chart) is a true 3–D chart because the data series extend into the third (depth) dimension.

A true 3–D chart has three axes: a value axis (the height dimension), a category axis (the width dimension), and a series axis (the depth dimension). The series axis is always a category axis — it cannot depict scaled numerical values.

When a 3–D chart is active (whether it's a true 3–D chart or not), you can change the chart's perspective and viewpoint by using the Chart Tools

The data is plotted using a 3–D column chart in Figure 2-7. Many people use this type of chart because it has more visual pizzazz. Although it may be more appealing visually, this type of chart often makes it difficult to make precise comparisons because of the distorted perspective view. Generally speaking, a 3–D column chart is best used when the goal is to show general trends rather than precise comparisons.

Figure 2-7. A 3–D column chart.

The final column chart variation is shown in Figure 2-8. This is a stacked cone chart. This chart subtype (as well as the cylinder and pyramid variations) is usually more difficult to interpret, compared to a standard column chart. This particular chart can be a bit misleading because the Sprockets series may be viewed as being less important because of the reduced volume at the top of the cones. In addition, note the perspective distortion. Although the combined sales for the products exceed 120, 000 for all years, that fact is not apparent in this 3–D chart.

Figure 2-8. A stacked cone chart.

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. Figure 2-9 shows a bar chart that displays a value for each of ten survey items. The category labels are lengthy, and it would be difficult to display them legibly using a column chart.

Table 2-2 lists Excel's 15 bar chart subtypes.

Figure 2-9. If you have lengthy category labels, a bar chart may be a good choice.

As with a column chart, you can include any number of data series in a bar chart. In addition, the bars can be "stacked" from left to right. Figure 2-10 shows a 100% stacked bar chart.

This chart summarizes the percentage of survey respondents who replied to each option.

Line charts are often used to plot continuous data and are useful for identifying trends over time. For example, plotting daily sales as a line chart may help you to identify sales fluctuations over time. Normally, the category axis for a line chart displays equal intervals.

Figure 2-10. A 100% stacked bar chart.

Table 2-3 lists Excel's seven line chart subtypes.

See Figure 2-11 for an example of a line chart that depicts daily sales (200 data points). Although the data varies quite a bit on a daily basis, the chart clearly depicts an upward trend.

Figure 2-11. A line chart often can help you spot trends in your data.

A line chart can use any number of data series, and you distinguish the lines by using different colors, line styles, or markers. Figure 2-12 shows a line chart that uses three series, each with 48 data points. Each line is displayed in a different color and with a different marker. The series are identified by the legend.

Figure 2-12. This line chart uses three series.

Figure 2-13 shows a 3–D line chart that depicts population growth for three states. Most would agree that a 3–D line chart is definitely not a good chart type for this data. For example, the 3–D perspective makes it virtually impossible to determine the relative growths of Washington and Oregon. In fact, the chart may present an optical illusion that makes it impossible to discern the order of the line series across the depth axis. Using a standard line chart, as in Figure 2-14, is a better choice.

Figure 2-13. This 3–D line chart is not a good choice for this data.

Figure 2-14. Using a standard line chart is a better choice for this data.

A pie chart is useful when you want to show relative proportions or contributions to a whole. A pie chart can use 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.

You can "explode" one or more slices of a pie chart for emphasis (see Figure 2-15). Activate the chart and click any pie slice to select the entire pie. Then click the slice that you want to explode and drag it away from the center.

Figure 2-15. A pie chart with one slice exploded.

Table 2-4 lists Excel's six pie chart subtypes.

Figure 2-16 shows an example of a pie in 3–D chart. You have complete control over how the chart is rotated and how much the slices are separated.

Figure 2-16. A pie chart in 3–D.

The pie of pie and bar of pie chart types enable you to display a secondary chart that provides more detail for one of the pie slices. Refer to Figure 2-17 for an example. The pie chart shows the breakdown of four expense categories: Rent, Supplies, Utilities, and Salary. The secondary bar chart provides an additional regional breakdown of the Salary category.

The pie chart.xlsx file on the companion CD–ROM includes an alternate presentation of this data, using a stack column chart.

Figure 2-17. A bar of pie chart that shows detail for one of the pie slices.

Another common chart type is a scatter chart (also known as an XY chart). A scatter chart differs from most other chart types in that both axes display values. (A scatter chart has no category axis.)

This type of chart often is used to show the relationship between two variables. Figure 2-18 shows an example of a scatter chart that plots the relationship between sales calls made during a month (horizontal axis) and actual sales for the month (vertical axis). The chart shows that these two variables are positively related: Months in which more calls were made typically had higher sales volumes.

Figure 2-18. A scatter chart shows the relationship between two variables.

Table 2-5 lists Excel's five scatter chart subtypes.

Figure 2-19 shows another example of a scatter chart, this one using the straight lines and markers subtype. As you can see, the data points, when connected, draw a right triangle.

Figure 2-19. The data points in this scatter chart define a right triangle.

Another scatter chart example is shown in Figure 2-20. This chart displays 200 x–y data points, using straight lines and no markers. This is also an example of a minimalist chart. All the chart elements (except the data series) are removed.

Figure 2-20. 200 data points displayed in a scatter chart.

Think of an area chart as a line chart in which the area below the line has been colored in.

Table 2-6 lists Excel's six area chart subtypes.

Figure 2-21 shows an example of a stacked area chart. Stacking the data series enables you to see clearly the total sales for each quarter, plus the contribution by each series.

Figure 2-21. A stacked area chart.

Figure 2-22 shows the same data, plotted as a 3–D area chart. Although this chart has lots of visual appeal, it has a serious weakness: The data toward the back is often obscured. In this example, the first three quarters for Product C are not even visible.

Figure 2-22. The first three quarters for Product C are not visible in this 3–D area chart.

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. Figure 2-23 shows an example of a doughnut chart with two series (1st Half Sales and 2nd Half Sales). The legend identifies the data points. The arrows and series descriptions were added manually. Oddly, a doughnut chart does not provide a direct way to identify the series.

Figure 2-23. A doughnut chart with two data series.

Notice that Excel displays the data series as concentric rings. As you can see, a doughnut chart with more than one series can be very difficult to interpret. For example, the relatively larger sizes of the slices toward the outer Part of the doughnut can be deceiving.

Consequently, doughnut charts should be used sparingly. In many cases, a stacked or clustered column chart for such comparisons expresses your meaning better than does a doughnut chart. Figure 2-24 shows the same data, displayed in a stacked column chart.

Figure 2-24. Using a stacked column chart as a doughnut chart replacement.

Perhaps the best use for a doughnut chart is to plot a single series as a visual alternative to a pie chart. Figure 2-25 shows a single–series doughnut chart, with one slice exploded. I also added a bevel to the doughnut for some additional visual appeal (or "clutter," depending on your perspective).

Figure 2-25. A doughnut chart with one data series.

Table 2-7 lists Excel's two doughnut chart subtypes.

You may not be familiar with radar charts. A radar chart 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.

Figure 2-26 shows an example of a radar chart. This chart plots two data series across 12 categories (months) and shows the seasonal demand for snow skis versus water skis. Note that the water ski series partially obscures the snow ski series — which I solved by making the water ski series semitransparent.

Figure 2-26. A radar chart with 12 categories and 2 series.

It's probably a safe bet that the vast majority of people would have no idea how to interpret the ski sales chart. Figure 2-27 shows a much better alternative for the data: a stacked column chart.

Figure 2-27. A stacked column chart is a better choice for the ski sales data.

Figure 2-28 shows another radar chart, with three categories. This chart depicts the red, green, and blue components for a color using the RGB color system. In the RGB color system, each color is represented by a value (between 0 and 255) for red, green, and blue.

Figure 2-28. A radar chart with three categories.

Table 2-8 lists Excel's three radar chart subtypes.

A surface chart displays two or more data series as a three–dimensional surface. As Figure 2-29 shows, these charts can be quite interesting.

Unlike other charts, Excel uses color to distinguish values, not to distinguish the data series. The number of colors used is determined by the major unit scale setting for the value axis. Each color corresponds to one major unit.

Figure 2-29. A surface chart.

Table 2-9 lists Excel's four 3–D surface chart subtypes.

Figure 2-30 shows the contour subtype, using the same data as in the previous figure. As you can see, it's as if you're looking directly down on the surface chart.

Figure 2-30. A contour chart.

Think of a bubble chart as a scatter chart that can display an additional data series, which is represented by the size of the bubbles. As with a scatter chart, both axes are value axes — there is no category axis.

Figure 2-31 shows an example of a bubble chart that depicts the results of a weight–loss program. The horizontal value axis represents the original weight, the vertical value axis shows the length of time in the program, and the size of the bubbles represents the amount of weight lost.

Figure 2-31. A bubble chart.

Table 2-10 lists Excel's two bubble chart subtypes.

Stock charts are most useful for displaying stock market information. These charts require three to five data series, depending on the subtype.

Table 2-11 lists Excel's four stock chart subtypes.

Figure 2-32 shows an example of each of the four stock chart types. The two charts on the bottom display the trade volume and use two value axes. The daily volume, represented by columns, uses the axis on the left. The "up bars," sometimes referred to as candlesticks, depict the difference between the opening and closing price. A black up bar indicates that the closing price was lower than the opening price. These charts can be difficult to interpret because Excel does not display symbols in the legend for all series.

A stock market chart can display any number of data points. Figure 2-33, for example, shows three years of data for a company. This chart plots all four variables: volume, high, low, and close. With this many data points, individual days are not discernible, but trends are easy to identify.

Figure 2-32. Four stock charts.

Figure 2-33. Three years (747 days) of stock market data

Keep in mind that stock charts are not limited to financial data, and this chart type can be used for a variety of other purposes. Figure 2-34 shows an example of daily temperature data displayed in a stock chart. For each day, the chart shows the high temperature, the low temperature, and the average temperature.

Figure 2-34. Stock charts aren't just for financial information.