1

I have data in excel which lists several events as a time-of-day, in a single column for each day, like so:

1/1/2018    2/1/2018
10:23       00:23
14:53       13:14
16:01       14:19
23:01       16:01
            19:02
            20:00
            21:00
            22:23

I'm trying (unsuccessfully) to create a chart which would plot an X axis of the dates and a Y axis of the time of day, from 00:00 to 23:59, and have a distribution of dots for each of the dates, in order to visually show whether events tend to happen more often at certain hours. It should look like this:

Example of chart

I don't know if this is easy to accomplish in Excel (it should be...) Any help would be greatly appreciated. I've been struggling with "Insert X Y Scatter Chart" for the last 45 minutes.

Improve this question
2
  • If the next column doesn't have the same number of entries, how do you want to match them up? Can you edit the question to show both columns side-by-side (uses spaces to fake columns), and then give an example of how you want the results to look?
    – Ian
    Commented Sep 4, 2018 at 1:32
  • @Ian, you need some basic changes. U must create one column for Date & Other for Time, then plot XY graph, and format one for Date & Other for Time. While doing this select Number and from Category select Date/Time & other parameters. ☺
    – Rajesh Sinha
    Commented Sep 4, 2018 at 6:28

1 Answer 1

Reset to default
1

You can use Line with Marker chart to get the result.

  1. Based on the data source insert a Line with Marker chart and go to Chart Tool- Data- Select Data, change the data as shown in the following picture: enter image description here

  2. Change all Series from Solid line to No Line: enter image description here

  3. Change Axis options as shown in the following picture: enter image description here

Then you will get the result you want.

Improve this answer
5
  • Thank you very much for the detailed steps. However, on MacOS the interface is very different and although some of the fields are similarly named, I couldn't reproduce your steps. Please have a look at the MacOS dialog window and see if you can figure out how it should be done. Thanks again for your time. imgur.com/fZEsUSH
    – Cirrocumulus
    Commented Sep 4, 2018 at 19:12
  • It took me a little while to work out how to replicate this in Mac (see here). Without pre-selecting the data I did Insert -> Chart (Line with Markers). Then Select Data. I set the Chart data range to the section of data (excluding date header, eg A2:B9), then Switch Row/Column. Finally add the Horizontal axis labels (this updates the data range to A1:B9) and that's it. It looks like you need to change the line to none for each series individually.
    – Ian
    Commented Sep 4, 2018 at 23:32
  • I can't replicate this behavior here after several tries following your steps & screen grab closely. (1) Insert Chart without selecting the data. (2) Choose Select Data. (3) Set "Chart data range" to the entire table w/o the headers — this results in 2 lines in the "Legend entries" section which look like "6:23 8:20 8:47..." (4) Switch Row/Column — this results in several "Legend entries", each of them showing a time value (not "Series1" etc). (5) Define "Horizontal axis labels" to be the headers row — this results in "Chart Data Range is too complex to be displayed". cf. youtu.be/DoGSeNcbJEs
    – Cirrocumulus
    Commented Sep 5, 2018 at 0:25
  • Wow. That data set behaves differently. Excel must see a pattern. It splits the column into 2: Name is B2:B8 and Y values is B9:B14. You might need to create the data series' manually. Which is tedious. From the Select Data step, don't use the Chart data range. Instead click the + below the Legend entries, set the Y values to the row (first series, A2:C2) and the horizontal axis to the labels (A1:C1). Then create another series, Y values A3:C3, you can skip the labels. Repeat for every row, ensuring they are all the same width (regardless of blank cells).
    – Ian
    Commented Sep 5, 2018 at 1:58
  • Thank you, you are correct. It does work (sorta) and it is pretty tedious to set up. Also, the labels on the vertical axis can't be defined and it seems impossible to define "no line" as a whole, you have to do it for every data row. I thought this would be a simple problem... But this is one more issue with non-feature-parity with Excel for Mac when dealing with more advanced requirements. Anyway, thank you very much for your time and effort. I think it will actually be quicker to just write a python script to plot data from a csv file :)
    – Cirrocumulus
    Commented Sep 5, 2018 at 9:21

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .