Scatter Plot Graph with Dynamic formatting

shanghaileon

New Member
Joined
Sep 11, 2013
Messages
4
Hi Guys, I'm working on creating a scatter plot with lines graph to show both progression in levels for a person, year by year, as well as the performance rating that the person received that year. I'd like to do the formatting based on rating on a yearly basis (only one rating a year) and in the year they're promoted, they still hold that same rating, but move up a level.

I have most of the ground work laid for what I"m trying to do in the attached file, but I"m having trouble getting rid of connector lines across non sequential data points. For example, in 2013, someone is green, 2012, someone is yellow, and 2011 they were also green. I want each of those years colored respectively, but not 2011 with a line to 2013.

Any help would greatly be appreciated. I know that I can get around some of these issues by ordering the series, and I have a backup version that doesn't use the connecting lines which is decent, but I'm hoping there's a way to make this work! Thanks in advance!



Each vertical gridline represents a year.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,010
The following is a scatter plot with separate series for each line segment:

 

shanghaileon

New Member
Joined
Sep 11, 2013
Messages
4
I think that's very similar to what I am looking for, the one difference is that I have changes happening between periods but I think I can handle that in my graphing. How did you get it to not draw the lines, can you share your file or approach please?
 

shanghaileon

New Member
Joined
Sep 11, 2013
Messages
4
Worf, Thanks for the quick reply. Do you know a way to do it so that the coloring of the segments is dynamic? I've split my data into separate series for the separate formatting, but am having trouble preventing the connecting of the different lines. I've put a screenshot below to give you an idea of my data set and graph in it's current state:

 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,010
I could write a macro to create the chart based on the data table. Are the colors defined by the performance rating? When the performance is #N/A on an entire row, what should the color be?
I need all the details in order to write the code.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,709
Messages
5,524,433
Members
409,577
Latest member
Dwg

This Week's Hot Topics

Top