9.4. Chart Types Although there's a lot to be said for simple column chartsthey can illuminate trends in almost any spreadsheetthere's nothing quite as impressive as successfully pulling off the exotic bubble chart. This section covers the wide range of charts that Excel offers. If you can use these specialized chart types when they make sense, you can convey more information, make your point more effectively, and add a little bit of flair to your workbook.
Note: The following sections explain all of the Excel chart types. To experiment on your own, try out the downloadable examples, which you can find on the "Missing CD" page at MissingManuals.com. The examples include worksheets that show most chart types. Remember, to change a chart from one type to another, just right-click it and then select Chart Type.
9.4.1. Column By now, column charts probably seem old hat. But column charts actually come in several different flavors (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. The quickest way to understand the difference is to look at Figure 9-12, which shows the same data using several different subtypes. Figure 9-12. Excel generated all these different column-chart subtypes from a single worksheet shown. The data compares the population of males and females in selected rooms. Each subtype emphasizes a different aspect. 
Here's a quick summary of your column chart choices: Clustered Column (Figure 9-12, top left). In a clustered column, each value is shown in its own separate column. To form a cluster, the columns are grouped together according to category. The Clustered Column makes it easy to compare data series, but somewhat more difficult to compare categories. Stacked Column (Figure 9-12, top right). In a stacked column, Excel displays only one column for each category. 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. The Stacked Column is an elegant way to compress the data, and it lets you compare series totals without losing individual series information. 100% Stacked Column (Figure 9-12, bottom left). The 100% stacked column is similar to 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 Effect Column (three types). There's a 3-D version for each of the three basic types of column charts, including clustered, stacked, and 100% stacked. The only difference is that the chart is drawn with a dash of special effects, making the columns look three-dimensional. True 3-D Column (Figure 9-12, bottom right). The true 3-D column chart (the last entry in the list of subtypes), shows a bar for each value. However, Excel displays 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.
9.4.2. Bar The venerable bar chart (Figure 9-13) is the oldest form of data presentation. Invented sometime in the 1700s, it predates the column and pie chart. Bar charts look and behave almost exactly the same as column chartsthe only difference being that their bars stretch horizontally from left to right, unlike column charts, where they rise from bottom to top. Excel provides almost the same set of subtypes for bar charts as it does for column charts. The only difference is that Excel doesn't offer a true three-dimensional (or layered) bar chart, although it does offer clustered, stacked, and 100% stacked bar charts that give a three-dimensional effect. Figure 9-13. Top: Most people use bar charts because they leave more room for category labels. If you have too many columns in a column chart, as in this example, Excel either can't display all the column labels, or the labels are difficult to read.
Bottom: Displaying data in bars removes this problem because there's almost always ample room. 
9.4.3. Line Line charts are almost always used to show changes over time. Line charts emphasize trends by connecting each point in a series with a line. The category axis represents a time scale or is just a set of regularly spaced labels. Excel provides several subtypes for line charts: Line. This is 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, the first series is displayed just as it is 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. Stacked line charts are sometimes used to track things like a company's cumulative sales (across several different departments or product lines), as Figure 9-14 demonstrates. (Stacked area charts are another alternative, as shown in Figure 9-16.) Stacked line charts aren't nearly as common as stacked bar and column charts.
Figure 9-14. The top chart is a regular line chart that compares the sales for three different regions over time. The bottom chart is a stacked line chart, which plots each subsequent line by adding the numbers from the earlier lines. That makes the stacked line chart a great vehicle for illustrating cumulative totals. However, the stacked line chart also obscures differences between the series. You'd never guess that Region 3 is the underperforming region because it actually reflects the total of all three regions. 
Tip: Lines can never cross in a stacked line chart because each series is added to the one (or ones) before it. You can change which line is stacked at the top by changing the order of the series. To change the order, either rearrange your table of data on the worksheet (Excel places the rightmost column on top) or refer to Section 9.3.3, which describes how you can change the order of your series manually.
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 probably want to put totals in the last series. Line with Markers (3 types). These subtypes are the same as the three previous line chart subtypes, except they add square boxes to highlight each data point in the series. 3-D Effect Line. The 3-D effect draws ordinary lines without markers but adds a little thickness to each line with a 3-D effect.
Tip: If you need to draw smooth trendlines, you don't want to use a line chart. That's because a line chart connects every point with a straight line, leading to jagged, zig-zagging lines. Instead, use a scatter chart (Section 9.4.5) which doesn't draw any lines, and add the trendlines afterward, as explained in Excel: The Missing Manual.
9.4.4. Pie Pie charts show the breakdown of a series proportionally, using "slices" of a circle. Pie charts are one of the most recognizable types of charts, and one of the simplest (they work only if you're trying to chart a single data series). Here are the pie chart subtypes you can choose from: Pie. This is the basic pie chart everyone knows and loves, which shows the breakup of a single series of data. Exploded Pie. The exploded pie 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 Excel: The Missing Manual. 3-D Effect Pie (2 types). The pie and exploded pie types can both be rendered in three dimensions, tilted slightly away from the viewer for a more dramatic appearance. 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 is a great way to emphasize specific data. 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.
Note: Pie charts can show only one series of data. If you create a pie chart for a table that has multiple data series, you see only the information from the first series. The only solution is to create separate pie charts for each series (or to try a more advanced chart type, like a donut, which is covered on Section 9.4.6).
9.4.5. XY (Scatter) XY Scatter charts show the relationship between two different sets of numbers. 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 different symbols (like squares, triangles, and circles) for each series, ensuring that you can tell the difference between the points. Why would you want to plot data points without drawing a line? One reason is that you may need to draw conclusions from an inexact or incomplete set of scientific or statistical data. For example, scientific types may use a scatter chart to determine the relationship between a person's age and his reflex reaction time. The problem is that no matter how disciplined the experimenters, they can't test every different age. In addition, their data will include natural variations from the overall trend. (In other words, if the trend is for older people to have gradually slowing reactions, it's still likely that you'll run across a few exceptionally speedy older folks.) In this case, the best approach is to include no line or use a smooth "best fit" line that indicates the overall trend, as shown in Figure 9-15. Figure 9-15. XY Scatter chart showing the relationship between age and reflex-reaction time. 
Excel offers several scatter chart subtypes, including: Scatter. The scatter chart uses data markers to show where each value falls. It adds no lines. Scatter with Smoothed Lines. 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. A better option is to add a trendline to your chart, as explained in Excel: The Missing Manual. Scatter with Straight Lines. This subtype is similar to the scatter chart with smoothed lines, except it draws lines straight from one point to the next. It works like a line chart, and it makes sense only if you have your values in a set order (for example, from lowest to largest or from the earliest date to the latest). Scatter with No Data Markers (2 types). These subtypes are identical to the scatter with smoothed lines and the scatter with straight lines. The only difference is that they don't show data markers for each point. Instead, all you see is the line.
9.4.6. Area 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, to the point that it can actually obscure some data series. Figure 9-16 demonstrates. 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.
Note: Stacked area charts make a lot of sensein fact, they're easier to interpret than stacked line charts. That's because it's easy to get a feeling for how much contribution each series makes to the total by judging the thickness of the area. If you're not convinced, compare the stacked charts in Figure 9-14 and Figure 9-16. In the area chart, it's much clearer that region 3 is making a fairly trivial contribution to the overall total.
9.4.7. Donut The donut chart (Figure 9-17) 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. Excel displays each series in a separate ring. The rings are one inside the other, so they all fit into a single compact circle. Figure 9-16. Top: An area chart makes a dramatic point about sales in Region 2. But it obscures similar sales in Region 1.
Bottom: A stacked or 3-D area chart reveals the hidden data. 
Figure 9-17. Here, a donut chart compares the sales in two different years. The inner ring shows the sales for 2003, broken down by region. The outer ring shows the sales for 2004. 
Note: Donut charts sometimes need a bit of customizing before they look right. In the example in Figure 9-17, Excel uses chart labels that detail the percentage of each slice, and the year represented by each ring. You can add spiffy labels like these using Excel's drawing tools, which you can access by clicking the Drawing button located on the standard toolbar. Excel: The Missing Manual has details on using Excel's drawing tools.
The donut chart is ideal for comparing the breakdown of two different sets of data. But keep in mind that most people emphasize the data on the outside ringit's a human thingso make sure this actually is the most important series. To change the order of the series, you can modify the series in step 2 of the Chart Wizard, as described on Section 9.1.2. Excel offers two donut 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, Excel never fills in the center of the donut.
Tip: Think twice before you use a donut chart in a presentation! Most Excel gurus avoid this chart because it's notoriously difficult to explain.
9.4.8. Radar The radar chart (Figure 9-18) is a true oddity, and it's typically used only in specialized statistical applications. In a radar chart, each category becomes a spoke, and every spoke radiates out from a center point. Each series has one point on each spoke, and a line connects all the points in the series, forming a closed shape. The effect of all these spokes and lines is to make the chart look something like the radar on an old-time submarine. 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. 9.4.9. Surface 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. There are actually two category axes (x and y), which determine the position of a data point. The value links all the points to create a surface. Figure 9-18. This filled radar chart compares the products sold in sales offices in two different cities. Because all the categories (in this case, the various products) are joined into a closed shape, the radar chart acts somewhat like an area chart, so you can judge the significance of values by looking at the size and shape of the filled-in area. 
Surface charts are neat to look at, but ordinary people almost never create them. They're definitely overkill for tracking your weekly workout sessions. One problem is that 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. Figure 9-19 shows an example. Figure 9-19. A surface chart usually represents scientific data or mathematical models. Here, the surface chart represents a heat-index table. Heat-index tables show the perceived temperature under different conditions of humidity. For example, if it's 70° with 90% humidity, the dampness makes it feel like 66.9°. On the other hand, if it's 85° with 90% humidity, it feels to a human observer like a balmy 102°. 
9.4.10. Bubble The bubble chart is an innovative variation on the scatter chart. It plots only a single series, and it never draws a line. Each point is marked with a circleeither an ordinary circle or a three-dimensional sphere (bubble), depending on the subtype you choose. The extra frill is that the bubble sizes change based on a second set of related values. The larger the value, the larger the data-point bubble. Figure 9-20 shows an example. Figure 9-20. The bubble's position represents two values: the month (the category axis) and the number of units sold (the value axis). The size of each bubble reflects the profit generated by the units sold. 
9.4.11. Stock A stock chart displays specialized data for stocks. 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 GEM IN THE ROUGH
Even More Chart Types | The basic chart types you've seen so far aren't everything that Excel has in store. Excel also provides "custom chart" types. Custom chart types aren't really different chart typesinstead, they're a combination of an existing chart type that you've already learned about (like a pie chart, column chart, radar chart, and so on) and some preset formatting options.  For example, the B&W Area is a custom chart type that combines an ordinary area chart with color choices that are designed to be easy to distinguish when printed on a black-and-white printer. A less useful example is Blue Pie, which creates a pie chart where every slice is the same shade of blue, suspended above a cosmic blue gradient background. To take a look at the custom chart types, just select the Custom Types tab in the first step of the Chart Wizard, instead of the Standard Types tab. Depending on the option you select at the bottom of the dialog box, you can choose to see "User-defined" custom types (those that you've created yourself) or "Built-in" custom types, which are the presets that Excel provides. Most of the time, it's easier to start from scratch with an ordinary chart type rather than use a custom chart type. |
of columns you need and their order depends on the stock chart subtype that you select. Here are your choices: In each case, the order of terms indicates the order of columns you should use in your chart. For example, if you select Volume-High-Low-Close, 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 step 2 of the Chart Wizard to specify each series, even if it's not in the place Excel expects it to be, but it's tricky to get right, so it's easiest to just type in data in the order indicated by the chart type name.) No matter which subtype you use, a stock chart shows only values for a single stock. Figure 9-21 shows an example of a Volume-High-Low-Close chart. Figure 9-21. The columns at the bottom of this Volume-High-Low-Close chart show the number of shares traded (using the value scale on the left). The lines above these columns show the stock price (using the value scale on the right). For each day, the stock price is represented by a bar that spans from the day's low to the day's high. Excel marks the closing price with a tick in the middle of the bar. 
9.4.12. Cylinder, Cone, and Pyramid The cylinder, cone, and pyramid are really just more exotic versions of the tried-and-true column and bar charts. But instead of using ordinary rectangles, these charts use 3-D cylinder, cone, and pyramid shapes. 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). Figure 9-22 shows a few examples. Figure 9-22. Remember the column charts from Figure 9-13, which showed different ways to look at the gender breakdown of students in various rooms at a university? For added pizzazz, consider replacing your columns with other shapes like cones (top) or stacked cylinders (bottom). Though they're classified as separate chart types, they're really just column and bar charts in disguise. 
|
