1

I want to create an exploding stacked column chart similar to this: Explode bar chart

For example, I want to create the graph from the following data:

+--------------------------------+--------+--------+
| Process                        |  Solid | Liquid |
+--------------------------------+--------+--------+
| Workover & Well Intervention   | 90.73% |  5.76% |
+--------------------------------+--------+--------+
| WTP                            |  9.27% |        |
+--------------------------------+--------+--------+
| SP                             |        | 40.58% |
+--------------------------------+--------+--------+
| SPU                            |        | 53.66% |
+--------------------------------+--------+--------+
| Workover & Well Intervention 1 |    35% |        |
+--------------------------------+--------+--------+
| Workover & Well Intervention 2 |    52% |        |
+--------------------------------+--------+--------+
| Workover & Well Intervention 3 |    12% |        |
+--------------------------------+--------+--------+
| SPU – Demulsifier              |        |    59% |
+--------------------------------+--------+--------+
| SPU – Deoiler                  |        |    41% |
+--------------------------------+--------+--------+

Note: to import the data into your own spreadsheet, select the whole table and copy/paste it into your workbook, say in A1. Select what's in column A and click Text to Columns. Select Delimited data, and enter the fence character as the delimiter. Click Finish. There will be a bunch of border stuff in column A; just delete column A.

In this case, I'd like to explain the Workover & Well Intervention and the SPU data from the first 4 rows with the details provided within the last rows. Anyone knows how should I proceed? I am using Microsoft Excel for Mac 2011

10
  • Exploding as looks with Question is not possible,, Data Series can be set for Series Overlap can make some effects,, but not exactly as shown Graph,, also Custom combination can be applied but again not like shown graph,,, possibly person has used some Tool/Application to make Graph!! Jul 28, 2019 at 7:54
  • @RajeshS, see my comment on Máté Juhász's answer. I don't have ready access to Excel to play with it, but I'm thinking this might be doable.
    – fixer1234
    Jul 28, 2019 at 7:56
  • Basic concept: ignoring the "sides" of the exploded bars, they are just bars for additional data, which can be derived from the data to be in the appropriate proportions. The segment at the bottom and the one before the top need to be transparent (no fill color). The "sides" of exploded bars can be filled shapes.
    – fixer1234
    Jul 28, 2019 at 8:09
  • The values for the exploded bars need to be adjusted for the scale.
    – fixer1234
    Jul 28, 2019 at 8:14
  • 1
    @RajeshS, they have the data. In the exploded bar, it will be to a different scale. So the values need to be adjusted for the scale, then some filler values calculated to position the two groups of data on the bar (displayed with no fill).
    – fixer1234
    Jul 28, 2019 at 8:19

2 Answers 2

2

After all the discussion in comments, I had to see what was possible. The example below was roughed out in LibreOffice Calc, which has pretty lame charting and drawing tools compared to Excel. But this will illustrate the general approach of what you could do in Excel. Your example is a lot simpler than the example chart you used in the question.

You've got only one segment to explode in each column. In your sample image, the exploded column has a 3D quality because it is suspended off the top of each column and is expanded both up and down so it looks like it's closer. In your data, you're exploding the largest segment in each case. Unless you get really fancy, Excel will want to put the biggest segment of the first column at the bottom (it would look strangely top heavy if a 9% slice is at the bottom and a 91% slice sits on top), and then match the order on the subsequent columns. Since you're exploding the largest segment in each case, the left column will always be exploding the bottom segment. It would look weird to elevate that exploded column for no apparent reason, so I rested the exploded column on the axis.

This provides another benefit. In every column, the column shows the breakdown of 100%, including the exploded columns. In your sample image, the exploded column has no scale, it just illustrates proportions. I originally stuck a scaling value in E1 to be able to play with the scaling on the exploded column. In your case, that's counter-productive since it makes the exploded column look like it totals more than 100%. By not scaling the detail values, you can see those properly on the same Y axis scale. The result is that the shape connecting the relevant segment with the exploded column will have one horizontal edge.

So you can do the exploded segment, but it won't have a 3D illusion.

enter image description here

At the top of the image above is the data preparation. Your data is in the first three columns. In columns D through G, I rearranged and scaled the data. The first column is supposed to show the major categories from the top of the data, and the exploded column is supposed to show the detail from the bottom. I split the detail into a separate column, and filled all unused values with zero.

The scaling value in E1 is just a multiplier for the detail values to be exploded. Since I left those at the same scale, the value is set to 1.

I left the headings off the detail columns so they wouldn't get an X axis label. I didn't play with the Y axis, but that should probably be adjust in the axis settings to show a maximum of 100% and whole percentages (no decimals).

Then I selected column A, rows 2-11, and used the Ctrl key to select the same row range in columns D through G. From there, just tell Excel to insert a chart, select the stacked column chart, and tell it the series are in rows.

You can play with the color choices for each series, and the chart titles and labeling. If you decide you want a 3D effect and fudge the order so the exploded segment is always at the top, you can make the exploded column look suspended by calculating a dummy value for a bottom segment to fill the desired distance. Then set the color for that "series" to no fill.

For the shapes that relate the segment to its exploded column, I just used drawing tools. I inserted triangles where needed at the top or bottom and a rectangle for the rest. The line (outline) was set to none, and I set the area (fill) transparency to 70%.

6
  • Nicely done! Do you mind posting the data as data (if you still have it) instead of a screenshot? I think you could use the exploded column parts on a secondary axis but increase the min. number so it starts higher on the chart ...if that makes sense?
    – BruceWayne
    Jul 28, 2019 at 14:21
  • @BruceWayne, clever idea about the secondary axis. Then you wouldn't need to stick in dummy segments to elevate the column. I do still have the spreadsheet. Do you want just the original data. or all the values in the top half of the image? (That's all one image, but I can add a text table so people can play with their own solutions.)
    – fixer1234
    Jul 28, 2019 at 14:32
  • @BruceWayne, I replaced the data image in the question.
    – fixer1234
    Jul 28, 2019 at 14:53
  • if you don't mind adding all the data you used, that would be awesome. Thanks for editing the OP
    – BruceWayne
    Jul 28, 2019 at 15:36
  • 1
    @BruceWayne, the other columns shouldn't go in the question. Duplicating the whole table in the answer would make a mess. But the other columns are trivial to reproduce. It's all just =cell_reference for the values that go in the column, or 0. It's obvious what cells go where (and explained in the answer). For scaling, cell references in E&G become =cell_reference*$E$1. Once you import the question data, recreating the other columns is fast. It would take a lot more time to create and integrate the text table into the answer. But if you create an answer, you could do it there. :-)
    – fixer1234
    Jul 28, 2019 at 16:50
0

There is no such option in Excel to create exactly what's on your sample chart, you've multiple options :

  1. If you definitely need the same in Excel you might create multiple charts and manually edit them in e.g. paint to get desired result.
  2. You can create something similar in Excel as specified already in comments, however it might not look like an exploding chart.
  3. You might look for another tool which supports the chart you need (I'm sure it's possible with R which is free however it requires learning).
3
  • 1
    I wouldn't make this statement yet. It isn't a native feature, but I suspect this might be able to be faked with some helper data to create additional bars, transparent segments at the bottom, and drawing tools for the "sides" of the bars.
    – fixer1234
    Jul 28, 2019 at 7:55
  • @fixer1234,, upto certain extend some effects like Explode can be generated,, but not similar as show with OP,,, one I've made,,, check the Chat ☺ Jul 28, 2019 at 7:59
  • @RajeshS, see my comment on the question. I think this can be done even without getting that fancy.
    – fixer1234
    Jul 28, 2019 at 8:12

You must log in to answer this question.

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