What many people don't realize is that you can make only five data comparisons in Excel charts. And if you want to be picky, there are only four practical data comparisons that Excel charts let you make. The table summarizes the five data comparisons.
| Comparison | Description | Example |
|---|---|---|
| Part-to-whole | Compares individual values with the sum of those values. | Comparing the sales generated by individual products with the total sales enjoyed by a firm. |
| Whole-to-whole | Compares individual data values and sets of data values (or what Excel calls data series) to each other. | Comparing sales revenues of different firms in your industry. |
| Time-series | Shows how values change over time. | A chart showing sales revenues over the last 5 years or profits over the last 12 months. |
| Correlation | Looks at different data series in an attempt to explore correlation, or association, between the data series. | Comparing information about the numbers of school-age children with sales of toys. |
| Geographic | Looks at data values using a geographic map. | Examining sales by country using a map of the world. |
If you decide or can figure out which data comparison you want to make, choosing the right chart type is very easy:
Pie, doughnut, or area: If you want to make a part-to-whole data comparison, choose a pie chart (if you're working with a single data series) or a doughnut chart or an area chart (if you're working with more than one data series).
Bar, cylinder, cone, or pyramid: If you want to make a whole-to-whole data comparison, you probably want to use a chart that uses horizontal data markers. Bar charts use horizontal data markers, for example, and so do cylinder, cone, and pyramid charts. (You can also use a doughnut chart or radar chart to make whole-to-whole data comparisons.)
Line or column: To make a time-series data comparison, you want to use a chart type that has a horizontal category axis. By convention, western societies (Europe, North America, and South America) use a horizontal axis moving from left to right to denote the passage of time. Because of this culturally programmed convention, you want to show time-series data comparisons by using a horizontal category axis. This means you probably want to use either a line chart or column chart.
Scatter or bubble: If you want to make a correlation data comparison in Excel, you have only two choices. If you have two data series for which you're exploring correlation, you want to use an XY (Scatter) chart. If you have three data series, you can use either an XY (Scatter) chart or a bubble chart.
Surface: If you want to make a geographic data comparison, you're very limited in what you can do in Excel. You might be able to make a geographic data comparison by using a surface chart. But, more likely, you need to use another data mapping tool such as MapPoint from Microsoft.
The data comparison that you want to make largely determines what chart type you need to use. You want to use a chart type that supports the data comparison that you want to make.