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.
Choose to sort by Category, click Add Level, and choose to sort then by Date.
This is how the data set should look:
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):
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:
Label the new column Active Rest and delete the category labels for Active Rest in column E.
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:
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.
Your data set should now look like this:
Get the latest Excel tips and tricks by joining the newsletter!
Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it. You can read more of his writing on his personal blog at NapkinMath.io.
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...
- Save a Broken Chart with Switch Columns/Rows in Chart Tools
- Build a Better, Cleaner, More Professional Line Chart
- How To Add an Average Value Line to a Bar Chart
- How to Add a Vertical Line to a Horizontal Bar Chart
- How to Add Totals to Stacked Charts for Readability
- How to Show Percentages in Stacked Bar and Column Charts
- Building Charts with Multiple Series and Custom X-Axis
- How to Create Waterfall Charts in Excel