Trying to Make Complicated Line Chart from Pivot Table

[brandnew22]

I have attached an example of the pivot data I am working from. Basically I want a chart that has the city names on the X axis with Price on the Y, but for each City there are different types of data points (e.g., Other, *Us, Comm), and I want these to be stacked for each city, so DC (in my example) will have 2 points above it, each at different prices, and I want to distinguish between these points so I can see us, vs. other in the DC area.

Please help!

[zumbalj]

What are columns A, C, & D for? I would set your data up so that cities are on a row, Other, US, Comm, etc are columns, and the values are at the intersection of the two, unless the A, C, & D are used for something you're just not illustrating here.

[brandnew22]

It's for program type. so I will be creating a chart for type A, another chart for type C, etc.

[brandnew22]

I'm hoping to do something like a high-low for each city, so that there are points lined up for each city while also being able to apply some sort of line chart format at the same time. Is this possible?

[zumbalj]

I would try to format the data into multiple tables, one for A, one for C, etc. Leave blanks in cells that don't apply for a specific city/data point type combination. If it helps, draw your image of your graph (doesn't have to be exact data, just a sketch) on paper first. You should have all your X-axis labels in one column in consecutive rows. You should have all your data series (I don't know what the plural of series is...) in one row in consecutive columns. Y values are in the cells in the resulting table.

If you still need to have your data in a pretty picture like you do, you can link to the pretty picture cells for the data table format to make it easier on yourself.

[brandnew22]

OK you have helped a lot. I am really close to having what I want (I hope I can quickly replicate these with different sets of data). Please look at the attached workbook and let me know how I can distinguish between each one, e.g., if A B and C all belong to one overall group, I want them to have the same color, while the others like E F G will appear different categorically.

Thanks!

[zumbalj]

Try right clicking on one of the data points and choosing Format Data Series > Marker Fill, then select whatever color you want. You can also change the marker styles.

[brandnew22]

Try right clicking on one of the data points and choosing Format Data Series > Marker Fill, then select whatever color you want. You can also change the marker styles.

Yea but I have to do this over 50 times.. is there not a better way? I also need to know how to get the points to align with the cities on the X axis.

[zumbalj]

1. Not with the way your series are set up, at least that I'm aware of. That's a lot of data, and once you change it once, you won't have to do it again.

2. What exactly do you mean by "get the points to align with the cities on the X axis"? To me, they look nicely aligned.

[brandnew22]

1. Not with the way your series are set up, at least that I'm aware of. That's a lot of data, and once you change it once, you won't have to do it again.

2. What exactly do you mean by "get the points to align with the cities on the X axis"? To me, they look nicely aligned.

OK yes you are right -- I already have each point plotted in accordance with the cities on the x axis. I can try to go ahead and format the points as you suggest, but my worry is that when I go back and refilter the pivot table to produce a different set of data that when I create another chart from that, i will have to go back and manually reformat each data point. I need to do about 20 of these charts. So you are saying I won't have to reformat the points?

Also, is there a way to lock the chart so I can keep refiltering the pivot table without messing up the chart?

[zumbalj]

If you reformat everything, then copy and paste, then just change your source data, that is correct.