Need PIE Slices as percent of entire PIE

**binar** · 06-30-2009, 01:33 PM

Fellow Forum Members,
Attached is five months of dummy sales data for eight products. My objective is to filter this data for a 2, 3, 4, 5, and 6 week date ranges, and have my pie chart dynamically change for each corresponding week range I filter. I'm seeking for the size of the Pie Slices to automatically change, as well as the Legend and the color of the Pie slices depending on the products that were sold for the filtered weeks.

LASTLY, I need the Pie Slices to show as a single pie slice for products that show up multiple times once I complete a filter. Attached you will find a dummy data excel file I have setup. You will notice that Product A shows up 4 times in the filtered data, and also shows up as 4 individual pie slices in the Pie chart. This is WRONG. How do I make Excel display the Product A Pie slice as a single slice that is proportionate to the entire pie? I do not want to see four pie slices for Product A.

Does this require very complicated VBA coding? I would appreciate any help any one can offer.

**NBVC** · 06-30-2009, 01:43 PM

Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.

I will change it to Charting this time for you.

**Andy Pope** · 07-01-2009, 04:48 AM

No need to any VBA code.

Just create some extra data via formula to determine whether a record is within a 2,3,4,5 or 6 week period.

Then use a pivot table to summarise your data.
If you also display items with no data you will retain the colours for each product.

**binar** · 07-01-2009, 10:24 AM

Originally Posted by Andy Pope

No need to any VBA code.

Just create some extra data via formula to determine whether a record is within a 2,3,4,5 or 6 week period.

Then use a pivot table to summarise your data.
If you also display items with no data you will retain the colours for each product.

Andy,
Thanks for the file you bounced back. I think it is a very impressive approach. However, is there an approach that does not rely on pivot tables? To me the ideal solution would be one that requires no pivot tables and any additional data processing. Is there a way to do just using a formula that will control the way the chart outputs its quantity of slices?

**Andy Pope** · 07-01-2009, 10:53 AM

Without using pivot tables you would need to summarize the data your self using formula.

**binar** · 07-01-2009, 12:41 PM

Originally Posted by Andy Pope

Without using pivot tables you would need to summarize the data your self using formula.

Andy,
Thanks again for your help. I feel that what I am trying to do with a pie chart is something that I am sure thousands of business people using Excel 2003 have wanted to do at one time or another. It bewilders me why Microsoft has not made it easier to produce a pie chart with pie slices that combine common data into one pie slice and that shows all the pie slices in proportion to the entire pie. To have to do what you have shown me in the two files you have shared is in my opinion not very user friendly.

Therefore, I am now wondering if Excel 2007 has improved Pie Chart capabilities. What I am trying to accomplish is to filter data by a date range, and show the resulting filtered data as percentage pie slices in relation to an entire pie (100% represents the entire pie). At the very least I think Excel 2007 differs in that it supports the ability to easily filter by setting up a data range you specify. I remember seeing in Excel 2007 how one could filter using a date range such as 6-1-2009 to 6-24-2009. Are the Pie Charting cababilities in Excel 2007 more robust? Will it enable me to show the pie slices the way I want to show them? Any info you can provide on Excel 2007 relating to this matter will be greatly appreciated. Thanks

**Andy Pope** · 07-02-2009, 05:45 AM

They have built something, it's called pivot chart but you chose not to use it.

The charting engine itself was completely rewritten but no new chart types were added.

You can use the custom filter in xl2003 to get information between 2 dates.