The Five Data Comparisons That Excel Charts Make

Trying to figure out how to use Excel charts more effectively? Make sure you understand the five data comparisons that Excel charts make says bestselling computer book author Stephen L. Nelson.

Charts allow you to visually compare data in five basic ways, which means that your first step in determining the appropriate chart type is often simply to consider what data comparison you want to make.

Identifying the data comparison you want to make

Suppose, for example, that you’ve collected detailed product sales revenue data for a golf equipment manufacturer. Using a chart, you might decide to look at this data in any of the ways summarized in the paragraphs that follow:

Part-to-whole Compares an individual data point value to the sum of a data series. Comparing sales of a particular golf club set to total sales, for example, is a part-to-whole comparison.

Whole-to-whole Compares individual data point values to each other or data series to each other. Comparing sales of a starter men’s golf club set to a starter women’s golf club set, for example, is a whole-to-whole comparison.

Time-series Compares data point values from different time periods to show how values change over time. Showing monthly sales over the last year, for example, is a time-series comparison.

Correlation Compares different data series to explore correlation between the data series. Comparing industry-wide sales to the average age of the population, for example, is a correlation comparison.

Geographic Compares data values using a geographic map. Comparing sales by country,for example, is a geographic comparison.

Picking the right chart for a given data comparison

Once you decide what data comparison you want to make, it’s generally quite straightforward to identify the appropriate Excel chart types and sometimes even to identify appropriate chart sub-types. Here are some rules you can follow:

1. To make a part-to-whole comparison when working with just a single data series, you might choose a pie chart. (Pie charts plot only a single data series.) You might choose a doughnut chart or area chart if you’re working with more than one data series.

2. To make a whole-to-whole comparison, you might choose a chart that uses horizontal data markers, such as a bar chart or one of the cylinder, cone, or pyramid chart sub-types that uses a vertical data category axis and data markers. You might also choose a doughnut chart or radar chart.

3. To make a time-series comparison, you would typically choose a chart that uses vertical data markers, such as a column chart, a line chart, or one of the cylinder, cone, or pyramid chart sub-types that uses a horizontal data category axis and data markers. You might also choose the stock chart if you’re performing technical analysis of security prices. (Time-series charts typically use a horizontal data category axis because of the Western convention of using a horizontal axis to denote the passage of time.)

4. To make a correlation comparison, you might choose the XY (scatter) chart if you’re working with two data series or the bubble chart if you’re working with three data series. You might also choose the surface chart if you want to explore trends in two dimensions.

5. To make a geographic comparison, you would probably use Excel’s Data Map tool (refer to the Excel online help) or, possibly, the surface chart.

ABOUT THE AUTHOR