Microsoft Excel

Data in Different Scales

When you add multiple series, each series should use the same scale. In other words, the points for each series should be plotted (placed on the chart) using the same measurement system.

The worksheet in previous page figures works perfectly well because the different series of sales figures all use the same unit dollars. But if one series recorded sales totals in dollars and another recorded them in Euros (or even worse, recorded totally different data like the number of units sold), the chart would be inconsistent.

Excel doesn't complain if your series use different scales in fact, it has no way of noticing that anything's amiss. And if you don't notice either, you'll create a misleading chart.

Your chart may imply a comparison that isn't accurate or, if the scale is radically different, the chart can get so stretched that it starts to lose detail. If you have sales figures from $50,000 to $100,000 and units sold from 1 to 100, the scale stretches from 1 to 100,000, and the differences in sales totals or units sold are too small to show up at all.

What's the solution? Don't mix different scales. Ideally, convert values to the same scale (in this case, use the currency exchange rate to turn Euros into U.S. dollars before you create the chart). Or just create two charts, one for each data series. But if you really want to compare the changes in different types of data across the same categories, there's a way. in Section 18.6.4, "Creating Combination Charts" shows you how to build combination charts that fuse together two incompatible sets of data in a logical way.

Difference Between a Column and a Line Chart

With simple column charts, life is easy. It doesn't matter too much what data you choose to use for your category axis because your choice simply changes the way data's grouped. Other chart types that follow the same principle include pie charts (which only allow one series), bar charts (like column charts, but oriented horizontally instead of vertically), and donut charts (where each series is a separate ring).

The same isn't true for line charts and most other types of Excel charts. The category axis you use for a line chart is important because the values in each series are connected (in this case, with a line). This line suggests some sort of "movement" or transition as values move from one category to another. That means it makes sense to use a line to connect different dates in a region (showing how sales have changed over time), but it probably doesn't make sense to use a line to connect different regions for each date. Technically, this latter scenario should show how yearly sales vary as you move from region to region, but it's just too counter intuitive for anyone to interpret it properly.

As a general rule of thumb, use time or date values for the category axis. You should do this especially for chart types like line and area, which usually show how things change over time.

by BrainBellupdated
Advertisement: