1

I need help with this specific situation. I have a table with values between 0 - 115 and i want to display them with a chart like in the picture below. Picture1

The thing is that on chart i want the interval between 0-60 to be shorter and the interval between 85-115 to be shorter as well.

Also the x values on axis i want to be displayed only with the values 0 60 75 85 115. Basically all I need is to replicate the exact chart like in the picture. Do you think is possible?

Improve this question

1 Answer 1

Reset to default
1

I think it is possible, but my way is a little complex. You could try to combine 2 charts, one is Clustered Bar and another one is Scatter with smooth Lines.

Here is a simple sample according to your description, I add 3 columns as X-values, Y-values and data labels for Scatter. The numbers of X-value determine the distance between scales, you could modify them as your needs.

enter image description here

Step 1: Choose the range of BR to insert a bar chart, select and right click the X-Axis > Format Axis > Check the box of "Categories in reverse order".

**enter image description here**

Step 2: Right click the chart > Select data > Add X-Values to the chart > OK.

Step 3: Select bars of X-value in chart > Right click and choose Change Series Chart Type > Choose Scatter, Scatter with Smooth Lines.

Select line of X-value > Right click it to choose Select Data > Choose X-value, Edit.

Then select the values of X-value and Y-value. Click OK twice and you may get the following picture.

enter image description here

Step 4: Right click the secondary vertical axis (right one) > Format Axis > Check the box of "Values in reverse order".

Go to the Format Axis of > set the minimum and maximum and major unit as the below picture.

enter image description here

Step 5: Right click the left vertical axis > Add Major Gridlines.

Double click the major gridlines of Horizontal Axis (top one)> No line.

enter image description here

Step 6: Delete the right and top axis. Right click the chart > Select Data > Choose BR > Edit in Horizontal Axis Labels > Select the range from A2 to A4.

Right click the line of X-value > Format Data Series > Marker Options > Built-in.

Right click the line of X-value again > Add Data Labels > then select one of them, use "=" and choose the cell of Data Labels, do the same for others.

enter image description here

Step 7: Select the chart > Layout > Error Bars in Analysis group > More Error Bars Options > X-value.

Plus for Direction, No Cap for End Style, 3 for Error Amount.

enter image description here

Delete the legends. Then you will get the result as following picture.

m

Improve this answer
3
  • Thank you so much! Nailed it
    – Robert Pascu
    Commented Mar 25, 2020 at 9:12
  • You are welcome~, if you think the reply is helpful, please mark it as an answer. Thanks very very very much.
    – Emily
    Commented Mar 25, 2020 at 9:19
  • Nice answer, as far as it goes. The problem is that it destroys the cognitive basis for using a bar chart. When we see a bar chart, our brain interprets the lengths of the bars as our values, precognitively, before we even have a chance to consciously think about it. By faking the scale, you are breaking the correlation between the length of each bar and its encoded value, and distorting the meaning of the chart.
    – Jon Peltier
    Commented Mar 30, 2020 at 14:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .