Need Help with Pivot Tables in Excel 2010
Hello,
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
Region
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!!
Chip
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
Region
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!!
Chip
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
Select-one-part..jpg