Secondary axis problem of pivot chart

[Villalobos]

Hello,

I would like to add the secondary axis to the pivot chart but I have met some difficulties. My problem is that the values of first axis (column name is "UNIQUE NUMBER") are very low (e.g from 1,2 or 3 up to maximum 10) and values of secondary axis (column name is "QUANTITY") are very high (e.g up to 200.000).

Could somebody help me how should I visible these values on same chart?

I have attached the sample file.

 

[Luke M]

Right-click on Quantity series, format series. Under Series Options, change to "plot on secondary axis".

That answers your direct question. Since you have a column chart, this will initially cause one series to be sometimes hidden behind the other, so I would suggest looking into different chart layouts. One idea I had, w/o knowing what message you are trying to present, is to change to a line chart showing only markers (as this does not appear to be a time-based chart (left to right) I would avoid using connecting lines, as they cause readers to assume correlation as they read left-to-right).
W/o lines, could be good to show values on markers, so readers don't have to figure out which axis to use. Then, since you have data labels, could format the 2 y-axis to show no labels/no lines, for a cleaner look. Now you've got a nice simple image, which you can even shrink down.

Again, depending on what story you are trying to tell, may need to adjust one or both of the y-axis max value.

PS. IMO, I might not even plots these 2 items on same chart, unless the message is that there is no correlation between the two.

 

[Villalobos]

Hi Luke M,

Thank you the value added answer.