Flexibility with data scales on Y axis in scatter graph

[stephjayne165]

Hey guys

I’m not sure this question has a resolution, but I really hope it does…

I have a large amount of data (8000+ rows) which detail a monthly customer purchase volume vs discount. I have arranged this data into a scatter chart no issues with the Y axis being the discount percentage, and the x axis being the purchase amount. The issue I have is that the vast majority of the purchase amounts are relatively small, however we have a couple of big spenders which are making it very difficult to differentiate between the ‘little guys’ and I end up with a big blob which shows no real correlation.

What I would like to do is change the format of the y axis and have ‘bands’ as such, for example the y axis would display, $1, $50, $25, $500, $1500 etc. As you can see, the difference increases with each new number, as obviously would the scale on the graph.

I really hope that makes sense, if not feel free to ask!

Also, if I’m being a donut and there’s a better graph that you think will show what I’m after, please let me know!

 

[RickXL]

Hi,

You might want to try a logarithmic Y-axis.

Find the Format Axis options and (in Excel 2013 at least) on the same screen as min min and max values there is a tick box for "Logarithmic scale". Instead of getting a constant length for a constant difference you will get a constant length for a constant ratio.

The downside is that you will not be able to plot either zero or any negative values on the Y-axis.