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
LVL 2
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.
0
Todd WSystems AdministratorAuthor Commented:
sorry I was sure I attached the sample spreadsheet.  here it is.
Book1.xlsx
0
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.
PivotTable.xlsx
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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.
Book1.xlsx
0

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.
0
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
TW
0
Rob HensonFinance AnalystCommented:
Glad I was able to help
0
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.