Avatar of Chip Levinson
Chip Levinson
Flag for United States of America asked on

Need Help with Pivot Tables in Excel 2010


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!!

Microsoft ExcelMicrosoft OfficeSpreadsheets

Avatar of undefined
Last Comment
Chip Levinson

8/22/2022 - Mon
Rory Archibald

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Chip Levinson

Hi rorya,

Thanks for helping.  I will follow your advice on #1.  I am not clear what you mean by your #3 answer.  I want to have this table appear in a separate sheet as a dashboard.  When I tried inserting a table Excel said the table must be on the same worksheet as the data.

I figured out a crude way to make Pivot Tables work by using multiple report filters.  Is there a way to turn off the option of selecting multiple choices on a specific filter.  See image below:
Your help has saved me hundreds of hours of internet surfing.