Understanding Excel charts (Series data and Category axis labels)

Before we dig into different chart types in excel, it will be good to understand the way excel handles chart data. We’ll straight away dive into an example to explain this:

Suppose we have the following data (Gender-wise numbers of graduating students) and we want to represent this in a chart.

As learned in the last tutorial, we will select the whole range (B2:G4), then select an appropriate chart from the Insert->Charts menu, this gives us a fairly good starting point. We also know the various elements of excel chart and how to display/hide them.

TIP: Keep the top-left cell blank to help in a better automatic detection of your data.

What we intend to do in this tutorial is to understand the structure behind this chart.

To do this, right click somewhere in the middle of the chart and click on ‘Select Data’ as shown below:

Select Data menu

This will expose the ‘Select Data Source’ form. This window is the heart of any excel chart.

Select Data Explained

Though the chart parameters were automatically picked up by excel, we will still see this in depth so that we know the levers that we need to change to change something on the chart.

Click on a series name & then click on Edit button. The following dialog will open, this allows us to select new data or change Name of the series. The data that we select for the series can be a single cell or multiple cells in a single column or a row.

Editing series data

It is important to understand the concept of Series in a bit more details here. A ‘series’ is a list of numbers for excel that will be plotted one by one on the chart and all of these points will have the same color so as to help us in identifying them.

Selecting the series data is the most crucial part of getting your chart right in excel.

If you ever get confused about what to keep as series (es) and what to input in Category axis labels, get a clarity on what you want by thinking on the following lines:

I want to Track “Series” by “Category Labels”

Yes, what you want to track is the real data (series), which in this case is the number of Male students (series 1) & number of female students (series 2).  We want to track this by years (& hence those years go to Horizontal axis). If you ever go wrong on this, there is a quick Switch button available on Select Data dialog to swap series vs Categories.

Also, please note that series data is the most important one, Category Axis are just the appropriate labels for this. If we miss the category axis part, we will still get the same chart with Category Axis reading the default 1, 2, 3… & so on.

The concept of selecting series data & how series vs Category Axis label selection changes the chart is shown in detail in the following examples:

Series Data vs Category axis label switch

You may follow the following broad guidelines:

-          Usually with a time series data, time labels are likely to get in as category labels while all other data will make series(es).

-          If there is no time factor in the data(Say Student-wise marks by Subjects), usually the dimension that has more names(labels) will suit category labels and the other data will form the series.

These are just guidelines and we encourage you to think for each problem at hand. Please go through the excellent examples given in our exercise sheets to hone you skills in selecting right series and category data.