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