Building Charts with Multiple Series and Custom X-Axis

Building Data Categories into Data Series

The next problem we have to tackle has to do with how the data is laid out. Excel doesn’t recognize that all the rows that are labeled with each Category are related to each other. Excel can make charts with multiple data series that have separate formatting, but our data set is organized as one big series right now.

In order to organize the data into multiple series, we need to build each Category into its own column of data. The easiest way to do this is to start with a sort.

Select all the columns (A:D).

Click on Sort from the Data menu tab.

Data Sort Tab

Choose to sort by Category, click Add Level, and choose to sort then by Date.

Sort Dialog

Click OK.

This is how the data set should look:

Data Set Sorted

We need to give each Category it’s own column, and we’ll start with Accl/Testing at the top. Change the name of column C in row 1 from Percentage to Accl/Testing.

You can delete the Category title and the Accl/Testing cells in column D (D1:D5):

First Category Column

Now, we’re going to create the second Category column. Select the percentages and the labels of the next Category (Active Rest). Drag the edge of the selection right to move the entire range to the next column of cells:

Second Category Column

Label the new column Active Rest and delete the category labels for Active Rest in column E.

Second Category Column Complete

Repeat the process for the remaining categories, moving the percentages to a new column, labeling the column it after the Category the data belongs to, and deleting the row labels. The completed table will look like this:

Category Columns Complete

Now, we’re going to sort the data back into the ascending date order. Select columns A through H. Click on Sort from the Data tab and choose to sort by Date only.

Category Date Re-Sort

Click OK.

Your data set should now look like this:

Category Date Re-Sort Result

Get the latest Excel tips and tricks by joining the newsletter!

Andrew Roberts ThumbnailAndrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it.

Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!

Other posts in this series...

6 thoughts on “Building Charts with Multiple Series and Custom X-Axis

  1. Hello!

    In the every first picture there are only September and November dates in Column A, but then in the 5th picture the Dates in Column A have changed to include December, January, and February. I don’t understand how the dates got changed after reading the instructions a couple of times – feel free to email me.

    Thanks for this Webpage on Multi-series charts. You helped to display the data much more efficiently and that’s Important when presenting data.

    Joe Miyaki
    [email protected]

    1. Hi Joe,

      The data was sorted to put all the data points from the same category next to each other. This makes it easier to build out columns for each category.

  2. Andrew, thanks a million for this presentation. We have been bashing our brains out trying to figure out how to chart multiple species’ abundance over multiple sites in our intertidal survey of the California coast using newly acquired Excel skills and had not yet learned how to present data to Excel to achieve this result. Your effort on this page is very much appreciated. James.

  3. Hi Andrew, Thank you so much for this. Like James I’ve been trying to figure out how to chart one species, Pearl-bordered fritillary, over 5 sites, over 5 years. Your tutorial helped me a huge amount. Thank you so much. Paula.

  4. Hi,

    I just wanted to thank you for this amazing guide! It really saved me time and gave me further insight into the powers of Excel.


  5. How would you set the Axis to recognize 3 different formats. My data includes whole numbers, percentages and currency depending on the selection.

Leave a Reply

Your email address will not be published. Required fields are marked *