2

I have a process that starts with State0, and after some time gets to State1, State2, and so on.  Let there be Group1, Group2, Group3, and so on. How can I plot each state for each group having x as the time and y-axis for the Groups?

I would like to plot this example data:

              State0  State1  State2  State3

Group1:          0       1       4       8
Group2:          0       3       6      12
Group3:          0       2       5       9
Group4:          0       5       6       7  

So each GroupN should be on Y-axis from low to high, and then for each group and state there should be a corresponding scatter point on the time axis. I would like to have each state in different color so one can distinguish the time for StateN.

How can this be plotted in Excel? I have tried several ways but it seems that Excel always takes Groups in x-axis and state in y-axis. Could anyone help me out?

This is the gist of what I'm trying to achieve:

enter image description here

5
  • Hi Elekko, welcome to SE. Have you tried Switch Row/Column. This option is available when you have selected the chart and then access the Design tab on the excel Ribbon Jun 19 '19 at 4:19
  • (1) As a matter of principle, @BharatAnand’s suggestion is a good one.  Please try it and let us know if it solves your problem, or if it even helps.  (2) As to the specifics of this question, I don’t understand what you want.  Can you show us what you want the chart to look like?  (Draw something in a drawing tool like Paint, or draw something on a piece of paper and take a photograph of it.) … … … … … … … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete. Jun 19 '19 at 5:11
  • What are the numbers and where is time represented in the data? How is the State represented/identified in the chart? Can you sketch or mock up what the resulting chart would look like for this sample data?
    – fixer1234
    Jun 19 '19 at 5:27
  • I have just added a drawing, for it. Hope it helps. i.stack.imgur.com/h41EA.jpg
    – Elekko
    Jun 19 '19 at 5:56
  • The only chart where you can make vertical axis to be categorical is the bar chart in Excel (support.office.com/en-gb/article/…), there is no way to make categorical vertical axis with an xy chart. Jun 19 '19 at 9:08
2

This chart is relatively simple to make, once you understand the point that Mate made-XY/Scatter chart axis are only numeric-not categorical. So, the trick is to use a numeric proxy for each group, then label use data labels to make show the category name. As with most Excel charts, setting up your data is the most important step.

  1. First, format your data. Using your provided data table, you'll just need to add one additional "helper" column, which holds the numeric value for your groups. In this case, 1 for Group 1, 2 for Group 2, etc... The data table then looks like this:

enter image description here

  1. Insert a blank XY/Scatter Chart.
  2. Select data from your table for your chart. Each data series is one of your "States", with the following values:
    Series Name = "State"
    X Values = Numeric values in State column
    Y Values = Numeric values in Y Values column (corresponding to your group numbers)
  3. Format to taste. To match your image, I reversed the values in the vertical axis, so 1 is at the top and 4 at the bottom. I also stripped the vertical axis of it's labels and simply added labels to the State 0 data points, using your Group Names as the labels.

Formatted Chart

2
  • Good job! But a little hard to understand. (I know Excel fairly well — although I don’t know charts as thoroughly as I know formulas — and I had a lot of trouble following your steps. I suspect that it would be just about impossible for a newbie.) I suggest that you expand your explanations. (1) OK, I guess step 1 is fine. (2) Maybe just say “click on an empty cell and do ‘Insert’ → ‘Chart’.” (3) First of all, explain how to add series. Then, this would be clearer if you explicitly said that you have to do this separately for each state, and showed an example … (Cont’d)
    – Scott
    Jul 10 '19 at 18:04
  • (Cont’d) …  (showing actual cell coordinates). Also, do the series names matter? I tried it leaving the series name blank, and it seemed to work OK. (4) I was able to reproduce your results, but I’m not sure I did it the way you intended. What are the steps you recommend? And, since the labels are attached to the State 0 data points, it depends on them all having a value of zero. I don’t fully understand the OP’s problem space, so I don’t know whether this is guaranteed. You might want to extend your answer to add another helper column, with all zeros, just as an anchor for the “Group” labels.
    – Scott
    Jul 10 '19 at 18:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.