Microsoft Office Tutorials and References
In Depth Information
Charts with Multiple Series of Numbers
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.
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