ids200, excel

Assignment

From the file “CanadianSalesData.xlsx”, generate the following ten results using pivot tables. Each pivot table should appear on a separate worksheet. Within each pivot table, the cell(s) containing the required information should be highlighted, preferably in light orange. In the highlight tab, the theme colors block has “Orange, Accent 2, Lighter 60%” – you can hover your cursor over colors to see their names. But if you have trouble figuring it out, any non-obnoxious light color shade for highlighting is fine.

1) Find the total sales for Product Category = Furniture and Province = Manitoba

2) Display the total sales for Region = Prairie and Province = Manitoba, separately for each month from 1/2010 to 12/2010 (use Order Date, not Ship Date)

3) Show the 3 customers who have the highest total sales during 2011 (again, use Order Date)

4) Show all orders (use Order ID) where Sales >= $800 and the Order Category = Office Supplies

5) From the Province with the highest total sales, show the three Product Sub-Categories with the highest total sales

6) Display the average Sales value for each combination of Province and Order Priority

7) Display the average Sales value for each combination of Customer Segment and Product Category

8) Display the total Sales for each Province, separately for each month from 6/2010 to 8/2011 (use Ship Date, not Order Date)

9) Display each Province’s total Sales as a percentage of all sales, for the entire year 2011 (again, use Ship Date, not Order Date)

10) Display the 5 largest orders, where Product Container = Large Box and Customer Segment = Home Office