Add data points to Excel stacked bar chart

Mad Monk picture Mad Monk · Jan 10, 2017 · Viewed 25.6k times · Source

I have a table with salary ranges for various titles and individual salaries (see below).

Data:

enter image description here

What I'd like to do, is to create a stacked bar (or column) chart in MS Excel (Office 365 ProPlus) which shows the ranges, as well as the individual salaries with employee names as data markers. Please note that the number of employees is not the same for each title, e.g. there are 2 clowns, but 3 jugglers and so on.

Here is what I'd like to achieve. I have manually added a couple of data markers for illustration by editing the image:

Desired chart:

enter image description here

Answer

teylyn picture teylyn · Jan 10, 2017

With your horizontal bar chart already in place make sure it is a stacked horizontal chart, then create the data for the blue dots like in the table of my screenshot. Select B12 to C19 and copy. Then select the chart and use Paste Special. Paste with these options selected and all others unticked

  • new series
  • values in columns
  • Categories (X Labels) in First column

enter image description here

That will stack bars onto the existing chart. Select the stacked series and change the series chart type to Scatter chart.

The row position and the salary column are in the wrong order for our purpose. Using copy and paste, paste the row position into column D, then copy C11 to D19 and paste to B11.

Then add data labels with the option "Values from cells" and select A12 to A19.

Adjust the salary values and the formatting of the dots.

enter image description here