Add data points to Excel stacked bar chart
I have a table with salary ranges for various titles and individual salaries (see below).
Data:
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:
Answer
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
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.
