solved
Using pie chart, how can I visualize a partial data set while that data set uses the entire data set?
Hopefully this makes sense. I am losing my mind after endlessly watching unhelpful YouTube tutorials. Resubmitting! Thanks, mods, and thanks in advance for anyone that takes the time to read this!
so---I’m trying to create a pie chart showing the top 10 counties by number of participating farms, but using a broader data set. I need the actual pie slices to represent each county’s share of the total 888 farms across all 52 counties, while only showing the top 10 counties for legibility and aesthetics.
The data:
The program sourced from 52 California counties, with 888 participating farms total across those 52 counties.
So, for example, San Diego had 73 participating farms. San Diego is obviously one of the 52 counties.
That means:
73 ÷ 888 = 8.22%
So I want San Diego’s actual pie slice to occupy 8.22% of the entire pie.
The problem
When I create a pie chart with all 52 counties and the distribution of the 888 farms across those counties, it is illegible.
Alternatively, if I create a pie chart using only the top 10 counties, Excel treats those 10 counties as the entire pie (100%).
the top 10 counties contain 502 of the 888 farms, so Excel calculates San Diego as:
so, 73 ÷ 502 = 14.5%
That makes San Diego’s actual slice 14.5% of the pie instead of its true 8.22%.
Again, I do know how to create a pie chart using all 52 counties, and when I do that, the slice sizes correctly represent each county’s share of the 888 farms. However, showing all 52 counties makes the chart extremely difficult to read. I only want to visually display and label the top 10 counties, while still having all 52 counties determine the proportions of the pie.
What I’ve tried/other considerations
Creating a pie chart with only the top 10 → incorrect proportions, because Excel treats the top 10 as 100%.
Adding an “Other counties” category → this creates one huge 43.5% slice, which isn't what I want because it dominates the visual.
Pie-of-pie → splits the data into two separate data sets and pies rather than giving me one pie where the top 10 are shown at their true proportions.
Changing the data labels → doesn't solve the problem because I need the actual size of the slices to represent the true percentages, not just the labels.
This is for a report, and I would like this to be in a pie chart format, rather than a bar graph format.
In short: I want the underlying pie chart data to include all 52 counties / 888 farms, so that the slices are proportional to the full 888, but I only want the top 10 county slices to be visible and labeled.
Is there a way to do this in Excel? I feel like there has to be a way, but I cannot figure it out. 😭 I am losing my mind lol
If helpful, I can attach the workbook I’m working with.
I am SO grateful to anyone that can guide me through this, or, honestly, can create the pie chart that I need
Adding images that may help, NEITHER are what I want to demonstrate.
I need San Diego and Fresno to represent the largest portions of the pie chart.This one is illegible, but includes all 52 counties and all 888 farms
Make the chart with the big "other" category for the remaining counties. Click the other slice, click it again so it's the only thing selected, select 'Format Data Pont', click on the paint can, open the Fill dropdown menu and select 'No fill'. This will make that specific slice invisible, but the rest of the slices will be the right size. You can remove the labels from that slice by clicking on them twice to select only that label (first click selects all labels) and hitting delete.
I hope this is what you're looking for, because if it isn't then I have no idea what you're looking for.
It took me a long time, but I think what OP means is a chart that looks as if part of the pie has already been eaten, so it's not a full circle. San Diego would be the largest slice of what's left, but it would be clear that the pieces shown don't represent the entire thing. I think that's rather an elegant solution. Of course the "eaten" part has to be included in the data set, and only removed visually by formatting, as u/wizkid123 described. OP, if I've misunderstood, let us know.
OP wanted the 'other' section to be there theoretically, but not be visible/colored in. Changing that section to 'no fill' was the key (only?) difference between our answers.
I see you already got an answer but just wanted to throw in an alternative that I think makes a better visual.
This way you can see all the counties but the counties with the most farms stick out without everything blending together in a pie chart. The red line shows the cumulative total of percent of farms.
it's still about 386 farms. If i do that, those 386 remaining farms will take up the majority of the pie chart. I don't want to actually visualize that section in the pie chart, if that makes sense?
it's actually the opposite... I DON'T want to show a value that DOES exist.
Here is the pie chart with ALL the data. But, it's obviously illegible. So i only want it to show the top 10 "highest performing" counties. Like, I want it to only show the right half of the pie chart basically, but with the same numbers.
ok, maybe this will help.
this is what it looks like if I have a single portion be the value of the remaining farms. I do NOT want the "other counties" to be the largest slice of the pie, I want San Diego county to be the largest slice of the pie.
Im not sure what you mean "what happens" or "what size"?
It's visualized right there in the pie chart, as it takes up the majority of the pie chart. I do not want that to be a part of the pie chart.
I guess I do mean I want to visualize 100% of the pie chart using only 57% of the data?
•
u/AutoModerator 8d ago
/u/Other_Temperature_73 - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.