2

I created a bar graph of the mean abundances of fish from several families across 3 different sites.

One family is significantly more abundant than the others and so I am trying to put all of that families data on a secondary axis.

I've tried splitting the axis, but Excel will only let me put a series (in this case, which site I sampled from) on a secondary axis.

Is there any way I can put an entire data set on a new axis?

I have highlighted the data I want to put on a separate axis in orange and on the graph itself.

enter image description here

ChanganAuto
  • 1
  • 4
  • 18
  • 19
Aidan C
  • 21

2 Answers2

4

My proposal is to use logarithmic scale.
Fish data

MGonet
  • 4,015
2

You cannot directly move a single point to a secondary y-axis but you can fake it. First, though, a frame challenge: In your case, I'd recommend having two copies of the chart. One as shown above and one with the Y-Axis max manually set to 50 or so. Having one bar be on a different axis than the rest is likely to make it difficult for others to interpret the data in your chart.


That being said, you can setup an entirely new set of dummy data where everything is unchartable except the one point you want to move. In the example below, the 23 value was originally in cell C2 but that threw off my chart. I had 2 series (columns B & C) so I made 2 dummy series (columns D & E). I set every value in there to =NA() so it's be an error and not show up. You can probably also leave this blank but that sometimes shows up as 0 if you turn on values so I'm in the habit of using errors. Then, I moved the 23 value from C2 to E2 and extended the chart to include my two dummy series. I set both those dummy series to be on a secondary y-axis.

By default, they'll overlap the series on the primary axis. Since I have the same number of series on both vertical axes and since there is no data in the same position in both sets, it looks like just that one series is on a different axis. If you do this, you should at least turn on data labels for that weird series and possibly hide the values on the secondary y-axis like I did in the second picture. You'll probably also want to manually set the colors so it blends in with the rest.

Example

Cleaned Up a bit