# How-to Create a Time Data Series Step Chart in Excel

In our recent Friday challenge, we were asked by Anna how can we make our popular Excel Step Chart work for data that is not date based, but time based.  The use case is a medical study on sleeping.

You can read all about it here:   Friday Challenge – Time Data Series Step Chart

Here is what the data looked like: Here is what the final chart will look like: The Breakdown

1) Add Column of Data to Convert Text Values to Numbers

2) Create Step Chart Horizontal Data

3) Create Step Chart Vertical Data

4) Create XY Scatter Line Chart from Chart Data

5) Change Horizontal Axis Alignment to Vertical

6) Change Number format of Vertical Axis to Custom Number Format

8) Delete Chart Junk

Step-by-Step

1) Add Column of Data to Convert Text Values to Numbers

First, we need to modify the Awake, Other and Sleeping data to numbers instead of text since Excel doesn’t know how to graph text.

Here is a similar tutorial were we fake Excel to chart text:

How-to-make-categories-for-vertical-and-horizontal-axis-in-an-excel-chart

In order to do this, we need to add a column of data that converts the W, O and S into numbers.  So in Column C, I used this formula:

C3 =IF(A3=”w”,2,IF(A3=”o”,1,0))

Essentially, this formula converts W’s to a value of 2, and converts O’s to values of 1 and all other values to 0. We will use this data for our Vertical Axis values.

2) Create Step Chart Horizontal Data

Now we need to create the following formula in column D and copy it down:

D3   =SMALL(Table1[Start Time],TRUNC(ROW()/2,0))

What this does is copy the start time data and duplicates each value except the first value as you see here: Just copy it down until you see all #Num values.

3) Create Step Chart Vertical Data

Now we need to create the following formula in column E and copy it down:

E3   =IF(ISNUMBER(D2),IF(D3<>D2,E2,VLOOKUP(D3,Table2[[Start Time]:[Chart Y Axis Position]],2,FALSE)),VLOOKUP(D3,Table2[[Start Time]:[Chart Y Axis Position]],2,FALSE))

What this does is copy the chart y axis position data for the matching time and then duplicates each value one time for the next horizontal point. 4) Create XY Scatter Line Chart from Chart Data

Now that we have our data setup, we can create our chart.  Here is where we need to adjust our step chart to use a different chart type.  Normally, we can just use a line chart, but Excel doesn’t plot time data the same that it does with dates.  So a line chart won’t work.  Here is what it looks like if we chart the data as a line chart: So we need to pick a different type chart.  In order for Excel to treat our data correctly, we need to pick an XY Scatter with Straight Lines: If you pick that chart type, Excel will create your desired chart in a step format like you see here: 5) Change Horizontal Axis Alignment to Vertical

Now there is a few things we need to fix in order to get the final Excel chart.  First, we need to make it so that we can see the horizontal axis values as they are overlapping each other.  To do this, double click on the horizontal axis time numbers. Then change the alignment axis options to Rotate all text 270 degrees.  Your chart will now look like this: Don’t worry about the numbers just yet, we will fix that in a later step.

6) Change Number format of Vertical Axis to Custom Number Format

Now our desired chart was to have text values show up in the vertical axis instead of numbers.  In order to do this, we need to trick Excel number formats.  To do this, double click on the vertical axis and add this custom number format

[>1]”Awake”;[=1]”Other”;”Sleeping”   (Note: sometimes copying this from the web to Excel, you may need to delete and replace the quotation marks)

Your chart will now look like this: Okay, lets fix the horizontal axis numbers so that we can see them.  In order to do this, we need to shrink the plot area of the chart.  First select your chart and then select the plot area as you see here: Then drag and drop the bottom control box up so that the plot area gets smaller from the bottom of the chart.  Your chart will now look like this: 8) Delete Chart Junk

Your chart may have vertical gridlines that you may not want.  If you don’t want them, simply click on the vertical gridlines and press your delete key.  Here is what your final chart will look like: Video Tutorial

Check out a video tutorial on making this chart here: