troubleshooting Question

Need Help with Pivot Tables in Excel 2010

Avatar of Chip Levinson
Chip LevinsonFlag for United States of America asked on
Microsoft OfficeMicrosoft ExcelSpreadsheets
2 Comments1 Solution367 ViewsLast Modified:

I need some help in creating pivot tables in Excel 2010.  I have a worksheet that contains raw data for product sales and want to have a dashboard in a separate worksheet that makes it easy for managers to run reports on the data.  To simply things, I created a mock Excel file with the following fields:

Record Number - unique tracking number
Date of Sale
Sales Rep Name
Product they sold
Sales in dollars - some reps did not sell a particular product so this field has the value of #N/A.
Comments - records with no comments have a blank field

Picture of sales data table design
Here are my Initial Questions:
1. I will be running mathematical calculations on the Sales column such as average, minimum and maximum.  Bob did not sell any baseballs (cell F7).  Should that cell be empty of should it contain a text string like it does now (#N/A)?  For these calculations, I want to IGNORE reps with 0 sales - so I do not want to put a zero in this cell.

2. Is there a "Recommended Pivot Table" in Excel 2010?  I cannot find it on my Ribbon.  I only see PivotTable and PivotChart that lets me manually specify the table.

3. I want to have a table report that allows the user to select one of the 5 products, say Baseball, and then a report will be created that shows all the data for baseballs.  The report will include Date, Sales Rep, Product, and Sales.  Ideally, it will be sorted with the most sales first to the lowest.  Then all sales reps with #N/A would be at the bottom.  Here is a screenshot of what I mean:

Example of desired table report when filtering data on baseballs
I will probably have a few follow-up question on Table creation, then will post a separate thread on PivotCharts.  Thanks in advance for your help!!

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros