Thanks in advance experts!!! This is very complicated to explain, but I'll give it a go:
I have data (Invoice#, Invoice Year, Cat1 Sales, Cat2 Sales, Cat3 Sales), and need to find the percent of sales for each category, for each year in my data. For instance, if I have: Invoice# Year Cat1 Cat2 Cat3 123456 2006 $5.00 $4.00 $3.00 234567 2006 $5.00 $4.00 $3.00 345678 2006 $5.00 $4.00 $3.00 987654 2007 $3.00 $4.00 $5.00 876543 2007 $3.00 $4.00 $5.00 765432 2007 $3.00 $4.00 $5.00 I need a pivot table that will sum all values for each category by year, then tell me what percent of that year's sales belonged to that category. I can get a pivot table to show each category by year with total sales like this: Values 2006 2007 Cat1 $15.00 $9.00 Cat2 $12.00 $12.00 Cat3 $9.00 $15.00 BUT, I really need the percent of each category for that column. For instance, 2006 and 2007 both have $36.00 total sales. So I would want to know what percent of that $36.00 was for Cat1, Cat2, and Cat3. Something like this: Values 2006 2007 Cat1 42% 25% Cat2 33% 33% Cat3 25% 42% Does anybody have any recommendations? I have tried Show Value As Percent of Column, but it always shows 100%. If I Show Value As Percent of Row, it shows what percentage of the total sales of Cat1 was sold for each year. i.e. - Cat1 would show 62% for 2006 and 38% for 2007. Any help would be GREATLY appreciated!!!!!!! Thank you! -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel