Multi-level doughnut chart in Excel 2013

[Translationguy]

Hi All

I'm hoping you may be able to help with an issue I'm experiencing when trying to create a multi-level doughnut chart using Excel 2013.

It's a work project and I've modified an existing file that contains all of the income and expenditure during a month. It tracks a variety of data however I would like to create a graph to see which translators we are spending the most amount of money on as well as the language they translate into. In addition to this I would like to group the translators by their language.

I've tried a variety of methods, the closest I came to was having both tracked but the translators weren't grouped by their language and when changing the label data, the label data for the other layer would reset, eg:

Adam Adamson translates into German, when labeling the translators' doughnut, the languages doughnut would start displaying translators' names. When setting the languages doughnut back to the list of languages, Adam Adamson's segment and the other translators' names would display languages.

I can play around and manipulate the way data is measured so the structure of these columns can change in any way, but here are the sources of information I am using currently:

Language section:
Column A: List all the languages we translate into
Column B: SUMIFS formula that will calculate the amount spent for each language in column A

Translator section:
Column D: The language the translator translates into
Column E: The Translator's name
Column F: The SUMIFS formula that tracks the amount they have earned that month

Column G: Not essential to this however aside from translators we have other costs such as Desktop Publishing, Subtitling etc. If another layer could be added to the doughnut where the cost of translation is grouped as well as other services, it would make it tidier.

I'm not sure whether the Language section is necessary as each translator has the language next to them but I included it just in case.

If the end result of this could be that I have a graph showing who we spend the most on, grouped by the language they translate into, grouped by the service and have labels that accurately show what is being measured then that would really help.

Let me know whether you have any ideas or questions, I can provide these if necessary.

Many thanks!

 

[Translationguy]

Bump

 

[SpillerBD]

Send to a pIvotTable.
Row: Translator
Values: Spent
Values: Earned

Hit F11 to chart it and change chart to Doghnut

 

[Translationguy]

Hi SpillerBD

Many thanks for your reply but I'm afraid this didn't work.

I created a Pivot Table from the Data which does make it very presentable however the Doughnut is only a single layer and when trying to select the data (possibly because it's referencing a Pivot Table) the options to select the data are greyed out.

I've repeated this twice however I end up with the same result.

If you need additional details do let me know, I really appreciate your help.

Best regards

 

[Translationguy]

Hi again

I've been playing around to try and solve the issue, still no luck however I've adjusted the way the data is displayed in the sheet (not the graph) after watching this video: https://www.youtube.com/watch?v=pg88qc66p5U

I created a new sheet with:

Column "F": The types of resources we have (translators, DTP etc) followed by the list of languages, followed by the individual translators/DTP'ers etc.
Column "G": The column name is "First" and displays the totals for the types of resources.
Column "H": The column name is "Second" and displays the totals for the list of languages.
Column "I": The column name is "Third" and displays the totals for the individual translators/DTP'ers etc.

The resulting doughnut has 3 rings showing the above values. It still resets the labels etc when I try to show the innermost ring as "Resource Type" and any of the outer rings as another. It also doesn't group all translators under the segment for their language nor group the languages under translators and DTP'ers under DTP.

I want to look into presenting my data like this as I prefer having the three rings but having the data not conforming to their categories really makes this feel unfinished.

Any help would as ever be really appreciated!

Best regards

 

[Translationguy]

Bump

 

[SpillerBD]

I'll try take further look into this. after regular working hours...

 

[Translationguy]

*bump

 

[SpillerBD]

I have a solution that involves creating two Pivot Tables and Pivot
Charts. Set the Fill to the charting areas to "none" so you can overlay
them.
The outer should be doughnut and the inner either doughnut or pie.
(I'll follow up with a link to a sample file with solution after I get home from work.)

(I see 2016 has a Starburst Chart type BUT it doesn't work with Pivot Table !!)

 

[SpillerBD]

The following link is to a file a set up two Pivot Tables with Charts.
They are overlaid to provide an approach that may be an alternative for you.
(I had found another technique years ago, but can't find it to reproduce.)

You will need to download as a copy as the charting won't display in the web-browser/web-app.
I don't believe my file created in v2013 has any features unavailable to v2010

https://1drv.ms/x/s!Auu67iC5u9608mKouP8KBrwDim5K