You started recording every credit card transaction so that you can analyze your monthly expenses. You used an Excel worksheet to track dates, places, categories, and amounts. Because you are a consultant who travels periodically, you also have business expenses. You included a column to indicate the business-related transactions. Now you need to convert the data to a table, sort and filter the data to analyze it, and apply conditional formatting to apply a visual effect for further analysis.

Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 Start Excel. Open the downloaded Excel file named exploring_e04_grader_a1.xlsx. 0 2 In the Dining Out worksheet, convert the data to a table. Apply Table Style Light 14. 10 3 Remove duplicate rows in the Dining Out worksheet. 5 4 Sort the table by Description in alphabetical order, then by Store in alphabetical order, and then by Amount from the smallest to the largest. 10 5 Filter the records to show personal (i.e., non-business) lunch and dinner expenses. 10 6 Display the Total Row. In cell D48, select the function to total the Amount values. In cell E48, do not show a total. 10 7 In the June Expenses worksheet, apply the conditional formatting style that displays a Green Data Bar Gradient Fill in the range D2:D47. 5 8 Create a conditional formatting rule with these specifications to the range A2:C47: • Formula to determine which cells to format • Formula: =(AND(argument1,argument2)) where argument1 compares $D2 to see if it is greater than or equal to 100 and argument 2 compares $E2 to see if the text is "Yes". • Medium Green fill color (7th color on the 4th row) • Green border color • Outline preset border style 10 9 Create a conditional formatting rule with these specifications to the range A2:C47: • Formula to determine which cells to format • Formula: =(AND(argument1,argument2)) where argument1 compares $D2 to see if it is less than 100 and argument 2 compares $E2 to see if the text is "Yes". • Light Green fill color (7th color on the 2nd row) • Green border color • Outline preset border style 10 10 Create a custom color sort for the Description column with these specifications: • Sort on cell color to display the Medium Green fill color on Top as the primary sort. • Sort on cell color to display the Light Green fill color on Top as the secondary sort. 10 11 Freeze the top row. 5 12 Set the Scale to Fit percentage to 115%. Display the worksheet in Page Break Preview and insert a page break so that row 36 starts on a new page. 5 13 Repeat the titles on row 1 on all pages. 5 14 Insert a footer with the text Exploring Series on the left side, the sheet name code in the center, and the file name code on the right side of the June Expenses worksheet. 5 15 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Dining Out, June Expenses. Save the workbook. Close the workbook, and then exit Excel. Submit the workbook as directed. 0 Total Points 100

Excel worksheet) 1	Start Excel. Open the downloaded Excel file. data is given below	0
2	In the Dining Out worksheet, convert the data to a table. ApplyTable Style Light 14.	10
3	Remove duplicate rows in the Dining Out worksheet.	5
4	Sort the table by Description in alphabetical order, then byStore in alphabetical order, and then by Amount from the smallestto the largest.	10
5	Filter the records to show personal (i.e., non-business) lunchand dinner expenses.	10
6	Display the Total Row. In cell D48, select the function to totalthe Amount values. In cell E48, do not show a total.	10
7	In the June Expenses worksheet, apply the conditional formattingstyle that displays a Green Data Bar Gradient Fill in the rangeD2:D47.	5
8	Create a conditional formatting rule with these specificationsto the range A2:C47:

Dining Out

Date	Store	Description	Amount	Business
6/1/16	Barnes & Noble	Books	$ 24.95
6/1/16	Wendy's	Lunch	$ 10.76
6/2/16	United	Airline Ticket	$ 385.16	Yes
6/2/16	Applebee's	Dinner	$ 30.15	Yes
6/3/16	Marriott	Hotel	$ 214.80	Yes
6/3/16	Subway	Lunch	$ 7.50	Yes
6/4/16	Costco	Gas	$ 23.45
6/4/16	Costco	Gas	$ 23.45
6/4/16	Costco	Groceries	$ 100.33
6/5/16	Theater	Entertainment	$ 20.00
6/6/16	Amazon.com	Books	$ 26.55
6/6/16	PC Connection	Computer Equipment	$ 250.85	Yes
6/6/16	Hallmark	Gifts	$ 25.38
6/8/16	Wendy's	Lunch	$ 7.45
6/8/16	Wendy's	Lunch	$ 7.45
6/9/16	Dillard's	Clothes	$ 155.65
6/9/16	Subway	Lunch	$ 7.50
6/12/16	Chili's	Dinner	$ 18.61
6/12/16	Theater	Entertainment	$ 20.00
6/12/16	Theater	Entertainment	$ 20.00
6/12/16	Sam's Club	Gas	$ 22.85
6/13/16	Costco	Groceries	$ 114.44
6/14/16	Delta	Airline Ticket	$ 385.00	Yes
6/14/16	Delta	Baggage	$ 25.00	Yes
6/14/16	Texas Roadhouse	Dinner	$ 34.18	Yes
6/15/16	Applebee's	Dinner	$ 14.12	Yes
6/15/16	Hampton Inn	Hotel	$ 95.85	Yes
6/15/16	McGrath's	Lunch	$ 16.44	Yes
6/16/16	Delta	Baggage	$ 15.00	Yes
6/18/16	Sam's Club	Books	$ 18.44
6/18/16	Sam's Club	Gas	$ 24.11
6/18/16	Costco	Groceries	$ 120.44
6/19/16	Costco	Computer Equipment	$ 885.12	Yes
6/20/16	McDonald's	Lunch	$ 5.65
6/20/16	McDonald's	Lunch	$ 5.65
6/22/16	Dillard's	Gifts	$ 54.42
6/22/16	Hallmark	Gifts	$ 12.18
6/22/16	Subway	Lunch	$ 6.40
6/22/16	Wendy's	Lunch	$ 7.65
6/22/16	Wendy's	Lunch	$ 7.65
6/24/16	Costco	Gas	$ 26.18
6/25/16	American	Airline Ticket	$ 400.15	Yes
6/25/16	American	Airline Ticket	$ 400.15	Yes
6/25/16	Texas Roadhouse	Dinner	$ 36.14	Yes
6/26/16	Hampton Inn	Hotel	$ 112.44	Yes
6/26/16	Applebee's	Lunch	$ 15.81	Yes
6/27/16	Tucano's	Dinner	$ 30.14
6/27/16	Sam's Club	Groceries	$ 85.67
6/27/16	Red Robin	Lunch	$ 14.42
6/28/16	Amazon.com	Books	$ 22.15
6/30/16	Theater	Entertainment	$ 20.00
6/30/16	Subway	Lunch	$ 6.40

June Expenses

Date	Store	Description	Amount	Business
6/1/16	Barnes & Noble	Books	$ 24.95
6/1/16	Wendy's	Lunch	$ 10.76
6/2/16	United	Airline Ticket	$ 385.16	Yes
6/2/16	Applebee's	Dinner	$ 30.15	Yes
6/3/16	Marriott	Hotel	$ 214.80	Yes
6/3/16	Subway	Lunch	$ 7.50	Yes
6/4/16	Costco	Gas	$ 23.45
6/4/16	Costco	Groceries	$ 100.33
6/5/16	Theater	Entertainment	$ 20.00
6/6/16	PC Connection	Computer Equipment	$ 250.85	Yes
6/6/16	Amazon.com	Books	$ 26.55
6/6/16	Hallmark	Gifts	$ 25.38
6/8/16	Wendy's	Lunch	$ 7.45
6/9/16	Dillard's	Clothes	$ 155.65
6/9/16	Subway	Lunch	$ 7.50
6/12/16	Chili's	Dinner	$ 18.61
6/12/16	Theater	Entertainment	$ 20.00
6/12/16	Sam's Club	Gas	$ 22.85
6/13/16	Costco	Groceries	$ 114.44
6/14/16	Delta	Airline Ticket	$ 385.00	Yes
6/14/16	Delta	Baggage	$ 25.00	Yes
6/14/16	Texas Roadhouse	Dinner	$ 34.18	Yes
6/15/16	Applebee's	Dinner	$ 14.12	Yes
6/15/16	Hampton Inn	Hotel	$ 95.85	Yes
6/15/16	McGrath's	Lunch	$ 16.44	Yes
6/16/16	Delta	Baggage	$ 15.00	Yes
6/18/16	Sam's Club	Books	$ 18.44
6/18/16	Sam's Club	Gas	$ 24.11
6/18/16	Costco	Groceries	$ 120.44
6/19/16	Costco	Computer Equipment	$ 885.12	Yes
6/20/16	McDonald's	Lunch	$ 5.65
6/22/16	Dillard's	Gifts	$ 54.42
6/22/16	Hallmark	Gifts	$ 12.18
6/22/16	Subway	Lunch	$ 6.40
6/22/16	Wendy's	Lunch	$ 7.65
6/24/16	Costco	Gas	$ 26.18
6/25/16	American	Airline Ticket	$ 400.15	Yes
6/25/16	Texas Roadhouse	Dinner	$ 36.14	Yes
6/26/16	Hampton Inn	Hotel	$ 112.44	Yes
6/26/16	Applebee's	Lunch	$ 15.81	Yes
6/27/16	Tucano's	Dinner	$ 30.14
6/27/16	Sam's Club	Groceries	$ 85.67
6/27/16	Red Robin	Lunch	$ 14.42
6/28/16	Amazon.com	Books	$ 22.15
6/30/16	Theater	Entertainment	$ 20.00
6/30/16	Subway	Lunch	$ 6.40