2

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

RBT
  • 24,161
  • 21
  • 159
  • 240
Mad Monk
  • 23
  • 1
  • 1
  • 4
  • 1
    The answer @teylyn provided worked for me! I want to add that in Excel 365 Version 1812, when I clicked "Change Chart Type" for the newly added data row, I was presented with a "Combo" chart where I could individually select the chart type for each data row. See screenshot: [![Excel Screenshot](https://i.stack.imgur.com/cKt0n.png)](https://i.stack.imgur.com/cKt0n.png). I didn't even know this feature existed. But it works pretty well and is very helpful! – JROS Jan 13 '19 at 02:05

2 Answers2

4

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

teylyn
  • 34,374
  • 4
  • 53
  • 73
  • Thanks @teylyn , this is exactly what I was looking for. P.S.: Funny coincidence: I searched many websites yesterday looking for an answer and came across your blog too. Good stuff! – Mad Monk Jan 10 '17 at 20:17
  • Adding a 2023 update for this if you're working in Windows 10 Business. The above method only works if you choose vertical stacked bars, not horizontal stacked bars (for horizontal bars, Excel automatically adds a new secondary axis that I can't seem to get to work correctly). – BonnieM Mar 01 '23 at 20:30
1

Make a bar chart with all your data-points, including the one/ones you wish to add as single dots. Under "Design" go to Change Chart Type and select Combo. This enables you to select the individual chart type for all your data-series. For single dots on a bar chart, I have used the stacked line with markers and stacked column for the bar chart data.

Cecilie Heide
  • 11
  • 1