Sometimes you want to have a chart that seems impossible in Excel – a simple chart with some marks against categories listed on the Y axis, especially when the category names are long
Excel makes it easy to have this the other way around – a normal bar chart; or indeed to have a horizontal bar chart but neither are easy to read and as clean looking as the vertical version
To achieve the desired result we will use a combination of a hidden horizontal bar chart and an XY scatter chart.
Enter the data
Arrange your data in three columns, the first two containing your data:
- The first (work stream in this example) will form the catagories on the Y axis.
- The second (scores in this example) will form the X axis and also act as one of the co-ordinates to the XY scatter chart which will drive your dots.
- The third (height in the example) contains a formula in C2 and copied down:
=(ROWS($B$2:$B$8)-ROW()+ROW($B$2)-0.5)/ROWS($B$2:$B$8)
Note you will need to adjust the ranges so that they cover the length of the data in B:B.
Build the chart
Now we start to build the chart, first insert a simple horizontal bar chart
- Select A1 to B8
- Insert / bar chart / clustered bar chart
This produces a horizontal bar chart
However you can see that the categories are the wrong way around so simply click on the Y axis and right mouse to edit it and select the check boxes to:
- Categories in reverse order
- Horizontal Axis crosses at: At Maximum Category
This correctly aligns the categories.
Adding the dots (xy scatter information)
Now copy the second and third columns of data (columns B and C) and select the chart. Chose paste / special (either click on the ribbon or use <ALT><E> then <S>
This applies the copied data to the bar chart; don’t worry if it looks odd
Now we make the data series into dots and leave the other series as a bar which "supports" the dot in the correct plan
- Select the new data series (height in this example) and then right mouse and select change chart type.
- Select xy scatter (markers only)
- This makes excel add a new Y axis on the right as it can’t use the existing on the left and plots the data against this axis and the existing X axis.
- If the marks don’t line up with the bars because the new axis stretches from 0 to 1.2 set the new right hand Y axis to a fixed minimum of 0 and a maximum of 1
Final formatting of the chart
OK, now the data is in place we need to hide some things:
- Format the right hand Y axis so that the tick marks are “none” which hides the axis (in office 2016 you can just delete the axis with the <DEL> key).
- Hide the bars 'supporting' the dots by formatting the data series to "no fill" and "no outline".
- Set the Markers - the dots - to the format that you want - I like dots, with no fill, size 8 pt outline = 2 pt and a colour of your choice, in this case orange.
