You may have sets of data for several categories, and you want to show the values for each category, and highlight some kind of range for each category (a permissible range, a target range, or some statistically computed range).
The following chart shows three categories (A, B, and C) with highlighted ranges and several data points for each.
This combination chart is simple to make.
The Individual Values
We can plot the points as an XY Scatter chart series. This chart uses 1, 2, and 3 for categories A, B, and C.
There is substantial overlap between points, so it would be helpful to jitter the points, that is, spread them out laterally so they no longer overlap.
And of course, we want to show A, B, and C instead of 1, 2, and 3. Plus add the highlighted ranges for each category.
We’ll use the following data. The floating columns are defined in B2:D5, and the actual points are described in F2:I17. Column F has the point identification, category and index, which will not be shown in the chart. Column G has X values 1, 2, and 3 corresponding to the categories A, B, and C. Column H has the jittered X values. Column I has the actual Y values.
I’ve never encountered a useful jittering algorithm, so I fudged my own, which works well enough. The formula in H3 is
This gives a random number within ±0.2 of G3, biased closer to the middle of the ±0.2 range.
Building the Chart
Make a stacked column chart with the data in B2:D5.
Format the bottom series (“Min”) so its fill is No Color. The result is the floating column chart that serves as a backdrop for the actual points.
When an XY series is plotted on the axes of a column chart, an X value of 1 corresponds to the first category, 2 corresponds to the second category, etc. A value between 1 and 2 will be plotted proportionally between the first and second categories: 1.5 is midway between the columns at A and B. The jittered X values between 0.8 and 1.2 will fit within the width of the column at A.
Copy the range H2:I17, select the chart, and use Paste Special to add the data as a new series, with series in columns, series name in first row, category labels in first column.
The new series was added as a new set of columns stacked on the floating columns and on extra categories further to the right.
Right click one of the added points, choose Change Chart Type, and select the XY variant with markers and no lines.
The XY points were initially plotted onto secondary axes, which are misaligned from the primary axes of the floating columns. Format the XY series so it is plotted on the primary axis to finish the chart.