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!

rLs5lIY.png


Each vertical gridline represents a year.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Worf

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

chart11.JPG
 

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:

TUfR6Kz.png
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,188
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,827
Messages
5,855,877
Members
431,771
Latest member
CoryMelth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top