Create a bubble chart with sections

[EAlden01]

I need to create a bubble chart that will have a separate section for each audit type. The audit types are operational, financial, government, technology, FDR and Special Projects. Attached is the file and I have drawn an example the graph. It is located in columns O. I repeated audits over the years but the area will be different. The goal is to have a 5 year view and not a 3 year view as illustrated.

Notes:
1. The bubble chart needs to be split up into six sections.
2. The X axis is the year.
3. The Y axis is the frequency of audits based on the area.
4. The bubble is the area under audit.
5. The size of the total of findings based on the area.
6. The label within the bubble will include the number of findings.
7. The color of the bubble is based on the area. Colors can be repeated if it is a different type of audit. For example, if claims is under operations and government, they can be the same color.

Really appreciate everyone's time, consideration and assistance.

[MrShorty]

Since I am not sure exactly how you would want to proceed at every step, a broad overview of what I see needing to happen for this chart. Then you can identify the decisions you want to make and the places where you really need help.

1) The first step is to summarize the data in the spreadsheet. I don't know how you usually summarize this kind of data, but I would expect you either
1a) use a pivot table or
1b) build an equivalent table using COUNTIFS() functions.
2) If you are unfamiliar with bubble charts in Excel, I would probably suggest taking the time at this step to build a single bubble chart from the results in step 1. In the end, it might be easier to build 6 single bubble charts for each section rather than try to combine the data into 6 panels of one bubble chart, and you may decide that it is not worth the effort to proceed beyond this point.
2a) You need to recognize that a pivot chart cannot be a bubble chart. If you chose to use a pivot table, you will need a technique like this https://peltiertech.com/regular-char...-pivot-tables/ in order to build a bubble chart from your pivot data.
3) What I see in your picture is a panel chart. This tutorial explains how to build a panel chart with panels stacked on top of each other: https://peltiertech.com/Excel/Charts...venScales.html In your case, you have panels stacked on top of each other and panels positioned side by side, so you would need to extend the same techniques that are used to position points vertically and use those same techniques in both the vertical and horizontal directions. Note that a lot of the work is in the spreadsheet, calculating the appropriate horizontal and vertical positions of each bubble. Pay close attention to how Peltier performs these calculations.
4) If the shaded backgrounds are important, we can use a technique like this https://peltiertech.com/shaded-quadr...scatter-chart/ (basically combining a stacked area chart with our bubble chart) to give the colored backgrounds.

At this point I see two major questions that you need to answer. Do you prefer to summarize your data using a pivot table? Is it worth the extra effort to build a single panel chart rather than have 6 individual bubble charts?

[Tkhanna]

Hi, this information is super helpful! I think I'm running into a similar issue and am hoping you can help.

I want to create a panel chart within a pivot chart (separate panels for multiple categories within the single pivot chart). In the article above about how to create a panel chart, it looks like the data from the pivot table is first pasted as values in order to create the panel chart. I want to keep the pivot chart in order to use the interactive capability such as slicers to automatically update the pivot chart. Do you know if its possible to incorporate panels into the pivot chart? Any guidance would be extremely appreciated! Thanks in advance!

[MrShorty]

@Tkhanna: This forum is pretty strict about some rules -- like starting your own thread instead of adding your question to an existing thread. They would prefer that you start your own thread. If this thread is helpful in describing your question, then you can mention it.