Extract only Certain Data for Pivot Table Column

so I have another one that you folks might be able to help me with with please.   See attached

Employee Name
Possible Colors of Product - RED, GREEN, YELLOW, PURPLE
Amount of Each Sale
Total of Each color

So I need to pivot table the data to show

Employee Name
How Many of Each Color for each employee
Total # of sales regardless of color for each employee
Total Amount of Sales for each employee

How do I get a pivot table to show me only RED or only BLUE, etc. in the respective columns?

Thanks much
Todd WSystems AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
No file attached.

If the colour of the product is in its own column in the source data you can use it as a filter option.

When in the Pivot Table and the field list is visible on the right, drag the Colour column to the Filters pane (top left window in the bottom right area). This will then appear above the pivot table and will have a drop-down to select the colours; you will need to tick the option for selecting multiple items if you want more than one item ticked.
Todd WSystems AdministratorAuthor Commented:
sorry I was sure I attached the sample spreadsheet.  here it is.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may insert a Pivot Table as shown in the attached and insert a Color Slicer so that you can pick the color you want to show on Pivot Table.
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Rob HensonFinance AnalystCommented:
With a pivot table, unfortunately it will show all columns for each item sequentially, eg Green Quantity & Value, Purple Quantity & Value, etc

As mentioned, you can also add the colour as a filter.

I have done both in the attached.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Todd WSystems AdministratorAuthor Commented:
Subodh thank you sir - almost but not quite what I'm looking for.  Something I did not mention is I will need to print this pivot table out for reporting so it cannot have all the extras accompanied by something as the Color Slicer in it.  Needs to be simple formatted.  Also the $ amounts for the varying colors is not what I was looking for. I was looking for the total # (not $ amount) of Red, Green, Purple, Yellow by employee so for example Employee AA has 21 red,  6 green, 3 yellow, 2 purple for a total of 32.
Todd WSystems AdministratorAuthor Commented:
thank you both for your input.  compiling info from the two of you i was able to achieve what I needed.

Thank you again
Rob HensonFinance AnalystCommented:
Glad I was able to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.