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:
1 Answer
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.
- 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:
- Insert a blank XY/Scatter Chart.
- 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) - 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.
