Friday Challenge – Step Chart for an Excel Time Series

I was recently asked for assistance with this request:

Hi Steve,

I am trying to make a hypnogram with data that we collected over the course of a day. I want the y-axis to denote wake (W), other (O), and sleep (s) with just three tick marks. The y-axis should represent the time spent in these states. 

A hypnogram plots sleep patterns over time and is used to asses a person’s wake/sleep behavior at night (or during the day). It is used for diagnostic and treatment purposes for individuals with sleep disorders.

Some sample data: 





W 8:20:49AM 8:21:04
O 8:21:05AM 8:21:24
W 8:21:25AM 8:21:54
S 8:21:55AM 8:23:43
W 8:23:44AM 8:23:57
O 8:23:58AM 8:24:44
W 8:24:45AM 8:28:11
S 8:28:12AM 8:29:34
W 8:29:35AM 8:30:31
S 8:30:32AM 8:35:26

The second set of numbers is when the state ended.

Thank you,

Anna

 

You can learn more about our Excel step chart solutions here:

https://www.exceldashboardtemplates.com/how-to-easily-create-a-step-chart-in-excel/

https://www.exceldashboardtemplates.com/how-to-easily-create-a-step-chart-in-excel-2/

https://www.exceldashboardtemplates.com/how-to-create-an-excel-step-chart-formula-using-the-small-function/

https://www.exceldashboardtemplates.com/understanding-how-to-make-a-step-chart-in-excel-using-index-and-match-functions/

 





 

Even though you can use the tutorials above to help with this Excel challenge, there is a trick to working with a Time Series in Excel.  So it might not be a easy as it may first appear.

Give it a shot and let me know if you were able to solve this challenge.  I was able to solve Anna’s request and she was able to automate it with the formulas you see in the tutorials above.  I might even get a mention in her publication if it is accepted 🙂 Way cool!

 





If you found the website and tutorials helpful, please consider donating to keep the lights on.

Donate with PayPal here:





Steve=True

 





5 COMMENTS

  1. Hi,

    I was trying something very similar with a different time scale (in 30s intervals) and numbers instead of Sleep Stage letters, however I always get diagonal vertical lines and I can not get it properly done. Can you help me with this Steve?

    Columns extend up to 2400 cells, so a way to semi-automatize the process would be very apreciated. Here is a brief example of the data:

    20.28.00 3
    20.28.30 3
    20.29.00 4
    20.29.30 1
    20.30.00 2
    20.30.30 2
    20.31.00 3
    20.31.30 3
    20.32.00 3
    20.32.30 3
    20.33.00 1
    20.33.30 1
    20.34.00 1
    20.34.30 2
    20.35.00 3
    20.35.30 3
    20.36.00 3
    20.36.30 3
    20.37.00 4
    20.37.30 5
    20.38.00 5
    20.38.30 5
    20.39.00 5
    20.39.30 5
    20.40.00 1
    20.40.30 2
    20.41.00 2

    Thanks in advance!




  2. If you found the website and tutorials helpful, please consider donating to keep the lights on.

    Donate with PayPal here:




  3. Hi Steve,

    I was trying to make something very similar to this hypnogram, the difference is the time scale, my data consists in 30 s intervals and instead of the Sleep Stage I have numbers.

    I already tried to make it following every tutorial and I always get diagonal lines instead of vertical lines.

    Here is some example data:

    20.11.30 1
    20.12.00 1
    20.12.30 2
    20.13.00 2
    20.13.30 2
    20.14.00 3
    20.14.30 3
    20.15.00 2
    20.15.30 2
    20.16.00 1
    20.16.30 2
    20.17.00 1
    20.17.30 2
    20.18.00 1
    20.18.30 1
    20.19.00 1
    20.19.30 2
    20.20.00 3
    20.20.30 3
    20.21.00 1
    20.21.30 1
    20.22.00 1
    20.22.30 2
    20.23.00 1
    20.23.30 2

    First column is the time and the second is the stage.
    The thing is that I have more than 2400 values in each column…

    Thanks in advance!

    Leo

    • Hi Leo,

      You only need where the value changes. I used your data and my formulas from the post and got this:
      Step Chart Sample

      Remember that you also need to use a XY Scatter Chart

LEAVE A REPLY

Please enter your comment!
Please enter your name here