Questions
1. From the worksheet Data, create a Custom List to sort your Regions in this order:
West Coast, East Coast, North Coast, South Coast
2. From the worksheet Data, create a set of Subtotals to Add the Product Sales by
Region, then by Category Name
3. From the worksheet Data, apply Filtering to only view the transactions for the Region,
East Coast and the Product, Ipoh Coffee
4. From the worksheet Data, apply a Custom Filter to view all transactions where the
Product Sales are greater than or equal to 1000
5. From the worksheet Data, apply an Advanced Filter to show transactions for:
Chocolade where the Sales are >500, Outback Lager where the Sales are >600
and Vegie-spread where the Sales are >400
6. From the worksheet Template, you would like to create a drop down list for the
column Product. Using Data Validation use the range M2:M7 as your source to
create a drop down list
7. From the worksheet Grouping, group the Products together
Region Category Name Product Name Date Quantity Unit Price Product Sales
East Coast Beverages Ipoh Coffee 3/4/2008 3 36.80 110.40
East Coast Beverages Laughing Lumberjack Lager 12/12/2008 3 14.00 42.00
North Coast Confections Teatime Chocolate Biscuits 8/5/2008 5 9.20 46.00
South Coast Confections Teatime Chocolate Biscuits 10/20/2008 5 9.20 46.00
West Coast Condiments Aniseed Syrup 10/13/2008 6 10.00 60.00
West Coast Condiments Chef Anton's Cajun Seasoning 10/14/2008 6 22.00 132.00
East Coast Confections Chocolade 7/18/2008 6 12.75 68.85
East Coast Beverages Ipoh Coffee 9/18/2008 6 46.00 276.00
South Coast Condiments Vegie-spread 6/20/2008 6 43.90 263.40
East Coast Beverages Laughing Lumberjack Lager 5/21/2008 7 14.00 98.00
East Coast Dairy Products Mozzarella di Giovanni 11/18/2008 7 34.80 243.60
East Coast Beverages Outback Lager 5/15/2008 7 15.00 105.00
North Coast Confections Teatime Chocolate Biscuits 9/22/2008 7 9.20 64.40
South Coast Confections Teatime Chocolate Biscuits 10/8/2008 7 9.20 48.30
North Coast Beverages Outback Lager 10/9/2008 8 15.00 114.00
South Coast Condiments Vegie-spread 11/19/2008 9 43.90 395.10
West Coast Dairy Products Camembert Pierrot 4/28/2008 10 34.00 323.00
West Coast Condiments Chef Anton's Cajun Seasoning 8/18/2008 10 22.00 198.00
East Coast Dairy Products Mozzarella di Giovanni 12/26/2008 10 34.80 348.00
North Coast Confections Teatime Chocolate Biscuits 5/28/2008 10 9.20 92.00
West Coast Dairy Products Camembert Pierrot 4/21/2008 12 34.00 346.80
West Coast Condiments Chef Anton's Cajun Seasoning 9/2/2008 12 22.00 237.60
North Coast Confections Teatime Chocolate Biscuits 9/1/2008 12 9.20 82.80
South Coast Confections Teatime Chocolate Biscuits 10/21/2008 12 9.20 110.40
West Coast Condiments Aniseed Syrup 7/7/2008 14 10.00 140.00
West Coast Confections Chocolade 2/21/2008 15 10.20 137.70
West Coast Confections Chocolade 4/15/2008 15 12.75 162.56
East Coast Beverages Ipoh Coffee 3/7/2008 15 36.80 552.00
East Coast Beverages Ipoh Coffee 4/15/2008 15 46.00 586.50
East Coast Beverages Outback Lager 7/31/2008 15 15.00 213.75
East Coast Beverages Outback Lager 8/21/2008 15 15.00 168.75
North Coast Confections Teatime Chocolate Biscuits 8/7/2008 15 9.20 124.20
West Coast Condiments Chef Anton's Cajun Seasoning 3/4/2008 16 17.60 225.28
West Coast Condiments Aniseed Syrup 3/25/2008 20 8.00 144.00
West Coast Condiments Aniseed Syrup 11/14/2008 20 10.00 200.00
West Coast Condiments Aniseed Syrup 12/3/2008 20 10.00 180.00
West Coast Dairy Products Camembert Pierrot 3/4/2008 20 27.20 544.00
West Coast Condiments Chef Anton's Cajun Seasoning 7/22/2008 20 22.00 352.00
East Coast Beverages Ipoh Coffee 2/26/2008 20 36.80 736.00
East Coast Beverages Ipoh Coffee 8/25/2008 20 46.00 920.00
East Coast Beverages Outback Lager 8/5/2008 20 15.00 300.00
South Coast Condiments Vegie-spread 10/3/2008 20 43.90 878.00
East Coast Confections Chocolade 10/14/2008 24 12.75 306.00
East Coast Beverages Ipoh Coffee 10/16/2008 24 46.00 1104.00
South Coast Confections Teatime Chocolate Biscuits 12/30/2008 24 9.20 209.76
South Coast Condiments Vegie-spread 7/11/2008 24 43.90 842.88
West Coast Condiments Chef Anton's Cajun Seasoning 8/19/2008 25 22.00 550.00
West Coast Condiments Chef Anton's Cajun Seasoning 11/3/2008 25 22.00 550.00
East Coast Beverages Ipoh Coffee 5/8/2008 25 46.00 1150.00
East Coast Beverages Laughing Lumberjack Lager 9/16/2008 25 14.00 350.00
North Coast Beverages Outback Lager 10/16/2008 28 15.00 357.00
East Coast Beverages Ipoh Coffee 12/1/2008 30 46.00 1035.00
East Coast Beverages Laughing Lumberjack Lager 4/10/2008 30 14.00 420.00
East Coast Dairy Products Mozzarella di Giovanni 12/18/2008 30 34.80 887.40
East Coast Beverages Outback Lager 10/8/2008 30 15.00 337.50
North Coast Confections Teatime Chocolate Biscuits 8/27/2008 30 9.20 276.00
c68452d5-c924-41a3-8ff0-09f62847af20.xlsx, 12/24/2011,4:23 AM 2 of 17
South Coast Confections Teatime Chocolate Biscuits 9/25/2008 30 9.20 248.40
South Coast Condiments Vegie-spread 12/22/2008 30 43.90 1317.00
East Coast Dairy Products Mozzarella di Giovanni 11/14/2008 35 34.80 1218.00
North Coast Beverages Outback Lager 12/16/2008 35 15.00 525.00
North Coast Confections Teatime Chocolate Biscuits 6/2/2008 35 9.20 257.60
South Coast Condiments Vegie-spread 1/29/2008 35 35.10 921.37
West Coast Dairy Products Camembert Pierrot 3/20/2008 40 27.20 1088.00
East Coast Beverages Ipoh Coffee 10/3/2008 40 46.00 1840.00
East Coast Beverages Outback Lager 9/5/2008 40 15.00 570.00
North Coast Beverages Outback Lager 11/28/2008 40 15.00 600.00
West Coast Condiments Aniseed Syrup 1/6/2008 50 8.00 400.00
West Coast Condiments Chef Anton's Cajun Seasoning 4/18/2008 50 22.00 935.00
West Coast Condiments Chef Anton's Cajun Seasoning 5/5/2008 50 22.00 990.00
West Coast Condiments Chef Anton's Cajun Seasoning 5/12/2008 50 22.00 1045.00
East Coast Beverages Outback Lager 10/6/2008 50 15.00 750.00
South Coast Confections Teatime Chocolate Biscuits 12/22/2008 50 9.20 437.00
West Coast Condiments Aniseed Syrup 5/19/2008 60 10.00 600.00
West Coast Dairy Products Camembert Pierrot 1/20/2008 60 27.20 1550.40
East Coast Beverages Ipoh Coffee 5/1/2008 60 46.00 2760.00
South Coast Condiments Vegie-spread 1/2/2008 65 35.10 2281.50
West Coast Confections Chocolade 1/3/2008 70 10.20 606.90
West Coast Dairy Products Camembert Pierrot 4/23/2008 84 34.00 2427.60
c68452d5-c924-41a3-8ff0-09f62847af20.xlsx, 12/24/2011,4:23 AM 3 of 17
Region
Report Month
Document Number Date Customer Name Salesman Name Product Product Category Quantity
New sleeve tubing
Hollow tubing
Unit Price Total Inclusive Price Long steel rod
Tube rod
Hollow rod
New sleeve rod
CategoryName ProductName Date Quantity UnitPrice ProductSales
Ipoh Coffee Total 8930.90
Beverages Ipoh Coffee 2/26/2008 20 36.80 736.00
Beverages Ipoh Coffee 3/4/2008 3 36.80 110.40
Beverages Ipoh Coffee 3/7/2008 15 36.80 552.00
Beverages Ipoh Coffee 4/15/2008 15 46.00 586.50
Beverages Ipoh Coffee 5/1/2008 60 46.00 2760.00
Beverages Ipoh Coffee 5/8/2008 25 46.00 1150.00
Beverages Ipoh Coffee 8/25/2008 20 46.00 920.00
Beverages Ipoh Coffee 9/18/2008 6 46.00 276.00
Beverages Ipoh Coffee 10/3/2008 40 46.00 1840.00
Laughing Lumberjack Lager Total 910.00
Beverages Laughing Lumberjack Lager 4/10/2008 30 14.00 420.00
Beverages Laughing Lumberjack Lager 5/21/2008 7 14.00 98.00
Beverages Laughing Lumberjack Lager 9/16/2008 25 14.00 350.00
Beverages Laughing Lumberjack Lager 12/12/2008 3 14.00 42.00
Outback Lager Total 2916.00
Beverages Outback Lager 5/15/2008 7 15.00 105.00
Beverages Outback Lager 7/31/2008 15 15.00 213.75
Beverages Outback Lager 8/5/2008 20 15.00 300.00
Beverages Outback Lager 8/21/2008 15 15.00 168.75
Beverages Outback Lager 9/5/2008 40 15.00 570.00
Beverages Outback Lager 10/6/2008 50 15.00 750.00
Beverages Outback Lager 10/8/2008 30 15.00 337.50
Beverages Outback Lager 10/9/2008 8 15.00 114.00
Beverages Outback Lager 10/16/2008 28 15.00 357.00
c68452d5-c924-41a3-8ff0-09f62847af20.xlsx, 12/24/2011,4:23 AM 6 of 17
Region Category Name Product Name Date Quantity Unit Price Product Sales
West Coast Condiments Aniseed Syrup 1/6/2008 50 8.00 400.00
West Coast Condiments Aniseed Syrup 3/25/2008 20 8.00 144.00
West Coast Condiments Aniseed Syrup 5/19/2008 60 10.00 600.00
West Coast Condiments Aniseed Syrup 7/7/2008 14 10.00 140.00
West Coast Condiments Aniseed Syrup 10/13/2008 6 10.00 60.00
West Coast Condiments Aniseed Syrup 11/14/2008 20 10.00 200.00
West Coast Condiments Aniseed Syrup 12/3/2008 20 10.00 180.00
West Coast Dairy Products Camembert Pierrot 1/20/2008 60 27.20 1550.40
West Coast Dairy Products Camembert Pierrot 3/4/2008 20 27.20 544.00
West Coast Dairy Products Camembert Pierrot 3/20/2008 40 27.20 1088.00
West Coast Dairy Products Camembert Pierrot 4/21/2008 12 34.00 346.80
West Coast Dairy Products Camembert Pierrot 4/23/2008 84 34.00 2427.60
West Coast Dairy Products Camembert Pierrot 4/28/2008 10 34.00 323.00
West Coast Condiments Chef Anton's Cajun Seasoning 3/4/2008 16 17.60 225.28
West Coast Condiments Chef Anton's Cajun Seasoning 4/18/2008 50 22.00 935.00
West Coast Condiments Chef Anton's Cajun Seasoning 5/5/2008 50 22.00 990.00
West Coast Condiments Chef Anton's Cajun Seasoning 5/12/2008 50 22.00 1045.00
West Coast Condiments Chef Anton's Cajun Seasoning 7/22/2008 20 22.00 352.00
West Coast Condiments Chef Anton's Cajun Seasoning 8/18/2008 10 22.00 198.00
West Coast Condiments Chef Anton's Cajun Seasoning 8/19/2008 25 22.00 550.00
West Coast Condiments Chef Anton's Cajun Seasoning 9/2/2008 12 22.00 237.60
West Coast Condiments Chef Anton's Cajun Seasoning 10/14/2008 6 22.00 132.00
West Coast Condiments Chef Anton's Cajun Seasoning 11/3/2008 25 22.00 550.00
West Coast Confections Chocolade 1/3/2008 70 10.20 606.90
West Coast Confections Chocolade 2/21/2008 15 10.20 137.70
West Coast Confections Chocolade 4/15/2008 15 12.75 162.56
East Coast Confections Chocolade 7/18/2008 6 12.75 68.85
East Coast Confections Chocolade 10/14/2008 24 12.75 306.00
East Coast Beverages Ipoh Coffee 2/26/2008 20 36.80 736.00
East Coast Beverages Ipoh Coffee 3/4/2008 3 36.80 110.40
East Coast Beverages Ipoh Coffee 3/7/2008 15 36.80 552.00
East Coast Beverages Ipoh Coffee 4/15/2008 15 46.00 586.50
East Coast Beverages Ipoh Coffee 5/1/2008 60 46.00 2760.00
East Coast Beverages Ipoh Coffee 5/8/2008 25 46.00 1150.00
East Coast Beverages Ipoh Coffee 8/25/2008 20 46.00 920.00
East Coast Beverages Ipoh Coffee 9/18/2008 6 46.00 276.00
East Coast Beverages Ipoh Coffee 10/3/2008 40 46.00 1840.00
East Coast Beverages Ipoh Coffee 10/16/2008 24 46.00 1104.00
East Coast Beverages Ipoh Coffee 12/1/2008 30 46.00 1035.00
East Coast Beverages Laughing Lumberjack Lager 4/10/2008 30 14.00 420.00
East Coast Beverages Laughing Lumberjack Lager 5/21/2008 7 14.00 98.00
East Coast Beverages Laughing Lumberjack Lager 9/16/2008 25 14.00 350.00
East Coast Beverages Laughing Lumberjack Lager 12/12/2008 3 14.00 42.00
East Coast Dairy Products Mozzarella di Giovanni 11/14/2008 35 34.80 1218.00
East Coast Dairy Products Mozzarella di Giovanni 11/18/2008 7 34.80 243.60
East Coast Dairy Products Mozzarella di Giovanni 12/18/2008 30 34.80 887.40
East Coast Dairy Products Mozzarella di Giovanni 12/26/2008 10 34.80 348.00
East Coast Beverages Outback Lager 5/15/2008 7 15.00 105.00
East Coast Beverages Outback Lager 7/31/2008 15 15.00 213.75
East Coast Beverages Outback Lager 8/5/2008 20 15.00 300.00
East Coast Beverages Outback Lager 8/21/2008 15 15.00 168.75
East Coast Beverages Outback Lager 9/5/2008 40 15.00 570.00
East Coast Beverages Outback Lager 10/6/2008 50 15.00 750.00
East Coast Beverages Outback Lager 10/8/2008 30 15.00 337.50
North Coast Beverages Outback Lager 10/9/2008 8 15.00 114.00
North Coast Beverages Outback Lager 10/16/2008 28 15.00 357.00
c68452d5-c924-41a3-8ff0-09f62847af20.xlsx, 12/24/2011,4:23 AM 7 of 17
North Coast Beverages Outback Lager 11/28/2008 40 15.00 600.00
North Coast Beverages Outback Lager 12/16/2008 35 15.00 525.00
North Coast Confections Teatime Chocolate Biscuits 5/28/2008 10 9.20 92.00
North Coast Confections Teatime Chocolate Biscuits 6/2/2008 35 9.20 257.60
North Coast Confections Teatime Chocolate Biscuits 8/5/2008 5 9.20 46.00
North Coast Confections Teatime Chocolate Biscuits 8/7/2008 15 9.20 124.20
North Coast Confections Teatime Chocolate Biscuits 8/27/2008 30 9.20 276.00
North Coast Confections Teatime Chocolate Biscuits 9/1/2008 12 9.20 82.80
North Coast Confections Teatime Chocolate Biscuits 9/22/2008 7 9.20 64.40
South Coast Confections Teatime Chocolate Biscuits 9/25/2008 30 9.20 248.40
South Coast Confections Teatime Chocolate Biscuits 10/8/2008 7 9.20 48.30
South Coast Confections Teatime Chocolate Biscuits 10/20/2008 5 9.20 46.00
South Coast Confections Teatime Chocolate Biscuits 10/21/2008 12 9.20 110.40
South Coast Confections Teatime Chocolate Biscuits 12/22/2008 50 9.20 437.00
South Coast Confections Teatime Chocolate Biscuits 12/30/2008 24 9.20 209.76
South Coast Condiments Vegie-spread 1/2/2008 65 35.10 2281.50
South Coast Condiments Vegie-spread 1/29/2008 35 35.10 921.37
South Coast Condiments Vegie-spread 6/20/2008 6 43.90 263.40
South Coast Condiments Vegie-spread 7/11/2008 24 43.90 842.88
South Coast Condiments Vegie-spread 10/3/2008 20 43.90 878.00
South Coast Condiments Vegie-spread 11/19/2008 9 43.90 395.10
South Coast Condiments Vegie-spread 12/22/2008 30 43.90 1317.00
c68452d5-c924-41a3-8ff0-09f62847af20.xlsx, 12/24/2011,4:23 AM 8 of 17
Region Category Name Product Name Date Quantity Unit Price Product Sales
Beverages Total 14424.90
Confections Total 374.85
Dairy Products Total 2697.00
East Coast Total 17496.75
Beverages Total 1596.00
Confections Total 943.00
North Coast Total 2539.00
Condiments Total 6899.25
Confections Total 1099.86
South Coast Total 7999.11
Condiments Total 6938.88
Confections Total 907.16
Dairy Products Total 6279.80
West Coast Total 14125.84
Grand Total 42160.70
c68452d5-c924-41a3-8ff0-09f62847af20.xlsx, 12/24/2011,4:23 AM 9 of 17
Region Category Name Product Name Date Quantity Unit Price
East Coast Beverages Ipoh Coffee 2/26/2008 20 36.80
East Coast Beverages Ipoh Coffee 3/4/2008 3 36.80
East Coast Beverages Ipoh Coffee 3/7/2008 15 36.80
East Coast Beverages Ipoh Coffee 4/15/2008 15 46.00
East Coast Beverages Ipoh Coffee 5/1/2008 60 46.00
East Coast Beverages Ipoh Coffee 5/8/2008 25 46.00
East Coast Beverages Ipoh Coffee 8/25/2008 20 46.00
East Coast Beverages Ipoh Coffee 9/18/2008 6 46.00
East Coast Beverages Ipoh Coffee 10/3/2008 40 46.00
East Coast Beverages Ipoh Coffee 10/16/2008 24 46.00
East Coast Beverages Ipoh Coffee 12/1/2008 30 46.00
c68452d5-c924-41a3-8ff0-09f62847af20.xlsx, 12/24/2011,4:23 AM 10 of 17
Product Sales
736.00
110.40
552.00
586.50
2760.00
1150.00
920.00
276.00
1840.00
1104.00
1035.00
c68452d5-c924-41a3-8ff0-09f62847af20.xlsx, 12/24/2011,4:23 AM 11 of 17
Region Category Name Product Name Date Quantity Unit Price
West Coast Dairy Products Camembert Pierrot 1/20/2008 60 27.20
West Coast Dairy Products Camembert Pierrot 3/20/2008 40 27.20
West Coast Dairy Products Camembert Pierrot 4/23/2008 84 34.00
West Coast Condiments Chef Anton's Cajun Seasoning 5/12/2008 50 22.00
East Coast Beverages Ipoh Coffee 5/1/2008 60 46.00
East Coast Beverages Ipoh Coffee 5/8/2008 25 46.00
East Coast Beverages Ipoh Coffee 10/3/2008 40 46.00
East Coast Beverages Ipoh Coffee 10/16/2008 24 46.00
East Coast Beverages Ipoh Coffee 12/1/2008 30 46.00
East Coast Dairy Products Mozzarella di Giovanni 11/14/2008 35 34.80
South Coast Condiments Vegie-spread 1/2/2008 65 35.10
South Coast Condiments Vegie-spread 12/22/2008 30 43.90
c68452d5-c924-41a3-8ff0-09f62847af20.xlsx, 12/24/2011,4:23 AM 12 of 17
Product Sales
1550.40
1088.00
2427.60
1045.00
2760.00
1150.00
1840.00
1104.00
1035.00
1218.00
2281.50
1317.00
c68452d5-c924-41a3-8ff0-09f62847af20.xlsx, 12/24/2011,4:23 AM 13 of 17
Region Category Name Product Name Date Quantity Unit Price Product Sales
Chocolade >500
Outback Lager >600
Vegie-spread >400
Region Category Name Product Name Date Quantity Unit Price Product Sales
West Coast Confections Chocolade 1/3/2008 70 10.20 606.90
East Coast Beverages Outback Lager 10/6/2008 50 15.00 750.00
South Coast Condiments Vegie-spread 1/2/2008 65 35.10 2281.50
South Coast Condiments Vegie-spread 1/29/2008 35 35.10 921.37
South Coast Condiments Vegie-spread 7/11/2008 24 43.90 842.88
South Coast Condiments Vegie-spread 10/3/2008 20 43.90 878.00
South Coast Condiments Vegie-spread 12/22/2008 30 43.90 1317.00
c68452d5-c924-41a3-8ff0-09f62847af20.xlsx, 12/24/2011,4:23 AM 14 of 17
Region
Report Month
Document Number Date Customer Name Salesman Name Product Product Category Quantity
New sleeve tubing
Hollow tubing
Unit Price Total Inclusive Price Long steel rod
Tube rod
Hollow rod
New sleeve rod
CategoryName ProductName Date Quantity UnitPrice ProductSales
Ipoh Coffee Total 8930.90
Laughing Lumberjack Lager Total 910.00
Outback Lager Total 2916.00
c68452d5-c924-41a3-8ff0-09f62847af20.xlsx, 12/24/2011,4:23 AM 17 of 17