2

I would like to create a chart as the one below using Microsoft Excel 2013 and additionally show the name of each stakeholder besides the bullet point. See my data below. I have tried using the "dot (X,Y) plot template" but just dont know how to draw that grid. This somehow is a three-dimensional diagramm type...

Any help is greatly appreciated.

enter image description here

So basically I have the following data:

Stakeholder Betroffenheit   erwartete Reaktion      Macht
--------------------------------------------------------------------
A           Hoch            Positiv                 Hoch
B           Hoch            Neutral                 Hoch
C           Hoch            Positiv                 Mittel
D           Mittel          Negativ                 Mittel
E           Niedrig         Neutral                 Niedrig
4
  • Instead of a screenshot, can you post your data as a table? Copy/Paste then use the code tags ({}) to format it. (You can of course obfuscate the data in your Stakeholder column, so you don't show anything confidential). Vielen Dank!
    – BruceWayne
    Mar 17, 2017 at 17:39
  • @BruceWayne Thanks for replying. Please check my modified post. Mar 20, 2017 at 8:22
  • Essentially it's a BCG-Matrix or Magic Quadrant. Right now your data is insufficient for the kind of diagram you want to draw. If there is a relative different between entries in the same square, your need some way to distinguish them. Both kinds of diagrams are scoring based so you would need some kind of score for each dimension and data point.
    – Seth
    Mar 20, 2017 at 8:37
  • 1
    @Matze To draw the grid, you need some more data rows. These rows have two points of your grid, e.g. (0, 3) and (3, 3) for a horizontal grid. Then you let the line be drawn between these points. If this doesn't work with the x-y-diagram, you need an overlay with a "normal" line diagram.
    – IQV
    Mar 20, 2017 at 8:42

2 Answers 2

1

You need to have some actual coordinates for your diagram. After that you can manipulate a point diagram to display it the way you want using the formatting options. At least for two dimensions (you could view your trend as a third) this works.

Lets assume that each value has a numeric XY coordinate available that ranges between 0.0 and 3.0 (as you have three major regions). With that assumption half intervals would be the middle of a field.

Create a new diagram and setup the data area using two dimensions. After that check the Layout options in order to set the axis format with a fixed minimum of 0.0 and a fixed maximum of 3.0 for both axes. Now you have your Layout.

In a more visual way you would do the following:

Setup the Initial data with the index values (you might be able to use conditional formatting for the icons later on):

Initial data

Setup the data rows for the point graph:

Setup data rows

Navigate the axis options and set a fixed interval for both axes:

Navigate axis options Setup the interval

Enable the drawing of main grid lines and enable the display of information for data points:

Enable grid Enable data point information

As you can see, it's not a complete solution but it might be a starting point.

One option to get the "third" dimension would be to setup a data row for all positive/neutral/negative entries and assign a marker type for those that corresponds to the icon you want. Depending on the number of entries it might also be possible to setup a row for each entry which would mean you would be able to use the row name as a description for the point.

1
  • Thanks a bunch for your time and effort. In the end I had to chose Visio for time reasons... lame but more efficient... Mar 21, 2017 at 12:07
2

Your chart is possible with Excel (at least 2010, but it should work in 2013 also). However, as with many custom Excel charts, you'll need to use a number of tricks and one add-in, Rob Bovey's Chart Labeler. Here's how you can accomplish this:

  1. Convert your data into an Excel table-it's just good practice and makes maintaining your data much easier.

  2. You need to convert your categorical data (gering, mittel, hoch) into something that Excel can chart on a XY/Scatter plot. So, add two columns to your table:
    Betro_1, where niedrig = 0.5, mittel = 1.5, hoch = 2.5
    Macht_1, where niedrig = 0.5, mittel = 1.5, hoch = 2.5

  3. Since you want to treat your series (postiv, neutral, negativ) differently, you'll need to plot them separately. This means you'll need 6 additional columns, one X and one Y for each of your three series. So add six more columns to your table:
    Pos_X =IF([@Reaktion]="Positiv",[@[Macht_1]],NA())
    Pos_Y =IF([@Reaktion]="Positiv",[@[Betro_1]],NA())...repeat for all three series
    You're using NA() for your null values, so Excel ignores them while charting the values.
  4. Add an XY/Scatter Chart to your worksheet.
  5. Add your three series to your worksheet.
  6. To get your axis labels correct (i.e. categorical text, not numbers), you'll need to add two additional series, one for each axis since their formatting is different.
  7. Add two additional tables for the labels, three columns Label, X, Y. Add the labels gering, mittel, hoch, and the corresponding X, Y values:
    macht_labels 0,0.5 0,1.5 0,2.5
    betro_labels 0.5,0 1.5,0 2.5,0
  8. Add the two label series to your chart.
  9. Use the Chart Labeler add-in to label your axis points and each of your series points.
  10. Format to taste, and you should be able to get something like this:

enter image description here

  1. Pro tip: since your X and Y values are simply numeric replacements for categorical data, you'll very quickly end up with overlapping points. To reduce this problem, multiply each of your X and Y values by a random number between .8 and 1.2. This will slightly skew the values in their respective grids and reduce overlap. So, for example, your Positive X formula would look like:
    =IF([@Reaktion]="Positiv",[@[Macht_1]]*(RANDBETWEEN(90,110)/100),NA())

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .