Microsoft Office Tutorials and References

In Depth Information

**Charts with Multiple Series of Numbers**

Practical Charting

But what causes the situation in the first place? Excel creates your charts according to

the way the data’s organized in your worksheet. A simple example shows you the effect.

The worksheet in Figure 19-9 looks at sales based on two factors: the year when the

sales were recorded, and the region where the sales were made. In technical

charting terms, the regions form the
category axis
, while the sales figures form the
value

axis
. In other words, Excel creates a separate series for each year. But it makes just as

much sense to organize the table in a different way, by making the year the category

axis and creating a separate series for each region! Figure 19-9 contrasts these two

different ways of looking at the same data, and shows how they affect the way Excel

groups your data in a column chart.

Figure 19-9:

This worksheet shows

the same data

charted in two different

ways. In the first table

(left), the category

axis lists the sales

years, which are used

to group the regions.

In the second table

(right), the category

axis lists the regions,

which are used to

group the years.

The column chart example is fairly innocent. Although you may prefer one way of

looking at the data over the other, they’re relatively similar. However, most Excel

charts aren’t as forgiving. The line chart is a classic example.

In a line chart, each line represents a different series. If you list the sales years on the

category axis (as shown on the left side of Figure 19-10), you end up with a separate

line for each region that shows how the region has performed over time. But if you

invert the table (shown on the right side), you end up with a chart that doesn’t make

much sense at all: a series of lines that connect different regions in each year. Figure

19-10 shows the problem.

Clearly, when you create a line chart, you need to make sure the chart ends up using

the data in a way that makes the most sense. So, how does Excel decide how to plot

the data? Essentially, Excel makes a best guess about your data. If you have more

rows than columns, Excel assumes that the first column represents the category axis.

If you have more columns than rows (or if you have the same number of rows and

columns), Excel assumes that the first
row
represents the category axis, as in Figure

19-10.