Office 2013 - myitlab:grader - Instructions Exploring - Excel Chapter 4: Assessment Project 1 Credit Card Expenses Project Description: 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 Points Possible Instructions 1 Start Excel. Open the downloaded Excel file named exploring_e04_grader_a1.xlsx. 2 In the Dining Out worksheet, convert the data to a table. Apply Table Style Light 14. 3 Remove duplicate rows in the Dining Out worksheet. 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 using the check box filters 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. 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". Light Green (Standard) fill color Green border color Outline preset border style Updated: 04/04/2013 1 0 10 5 5 10 E_CH04_EXPV1_A1_Instructions.docx Office 2013 - myitlab:grader - Instructions Exploring - Excel Chapter 4: Assessment Project 1 Step Instructions Points Possible 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". Orange (Standard) fill color 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 Light Green (Standard) fill color on Top as the primary sort. Sort on cell color to display the Orange (Standard) 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 custom 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 Updated: 04/04/2013 2 100 E_CH04_EXPV1_A1_Instructions.docx Date Store 6/1/2016 Barnes & Noble 6/1/2016 Wendy's 6/2/2016 United 6/2/2016 Applebee's 6/3/2016 Marriot 6/3/2016 Subway 6/4/2016 Costco 6/4/2016 Costco 6/4/2016 Costco 6/5/2016 Theater 6/6/2016 Amazon.com 6/6/2016 PC Connection 6/6/2016 Hallmark 6/8/2016 Wendy's 6/8/2016 Wendy's 6/9/2016 Dillard's 6/9/2016 Subway 6/12/2016 Chili's 6/12/2016 Theater 6/12/2016 Theater 6/12/2016 Sam's Club 6/13/2016 Costco 6/14/2016 Delta 6/14/2016 Delta 6/14/2016 Texas Roadhouse 6/15/2016 Applebee's 6/15/2016 Hampton Inn 6/15/2016 McGrath's 6/16/2016 Delta 6/18/2016 Sam's Club 6/18/2016 Sam's Club 6/18/2016 Costco 6/19/2016 Costco 6/20/2016 McDonald's 6/20/2016 McDonald's 6/22/2016 Dillard's 6/22/2016 Hallmark 6/22/2016 Subway 6/22/2016 Wendy's 6/22/2016 Wendy's 6/24/2016 Costco 6/25/2016 American 6/25/2016 American 6/25/2016 Texas Roadhouse 6/26/2016 Hampton Inn 6/26/2016 Applebee's 6/27/2016 Tucano's 6/27/2016 Sam's Club 6/27/2016 Red Robin 6/28/2016 Amazon.com 6/30/2016 Theater 6/30/2016 Subway Description Books Lunch Airline Ticket Dinner Hotel Lunch Gas Gas Groceries Entertainment Books Computer Equipment Gifts Lunch Lunch Clothes Lunch Dinner Entertainment Entertainment Gas Groceries Airline Ticket Baggage Dinner Dinner Hotel Lunch Baggage Books Gas Groceries Computer Equipment Lunch Lunch Gifts Gifts Lunch Lunch Lunch Gas Airline Ticket Airline Ticket Dinner Hotel Lunch Dinner Groceries Lunch Books Entertainment Lunch $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Amount 24.95 10.76 385.16 30.15 214.80 7.50 23.45 23.45 100.33 20.00 26.55 250.85 25.38 7.45 7.45 155.65 7.50 18.61 20.00 20.00 22.85 114.44 385.00 25.00 34.18 14.12 95.85 16.44 15.00 18.44 24.11 120.44 885.12 5.65 5.65 54.42 12.18 6.40 7.65 7.65 26.18 400.15 400.15 36.14 112.44 15.81 30.14 85.67 14.42 22.15 20.00 6.40 Business Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Date Store 6/1/2016 Barnes & Noble 6/1/2016 Wendy's 6/2/2016 United 6/2/2016 Applebee's 6/3/2016 Marriot 6/3/2016 Subway 6/4/2016 Costco 6/4/2016 Costco 6/5/2016 Theater 6/6/2016 PC Connection 6/6/2016 Amazon.com 6/6/2016 Hallmark 6/8/2016 Wendy's 6/9/2016 Dillard's 6/9/2016 Subway 6/12/2016 Chili's 6/12/2016 Theater 6/12/2016 Sam's Club 6/13/2016 Costco 6/14/2016 Delta 6/14/2016 Delta 6/14/2016 Texas Roadhouse 6/15/2016 Applebee's 6/15/2016 Hampton Inn 6/15/2016 McGrath's 6/16/2016 Delta 6/18/2016 Sam's Club 6/18/2016 Sam's Club 6/18/2016 Costco 6/19/2016 Costco 6/20/2016 McDonald's 6/22/2016 Dillard's 6/22/2016 Hallmark 6/22/2016 Subway 6/22/2016 Wendy's 6/24/2016 Costco 6/25/2016 American 6/25/2016 Texas Roadhouse 6/26/2016 Hampton Inn 6/26/2016 Applebee's 6/27/2016 Tucano's 6/27/2016 Sam's Club 6/27/2016 Red Robin 6/28/2016 Amazon.com 6/30/2016 Theater 6/30/2016 Subway Total Description Books Lunch Airline Ticket Dinner Hotel Lunch Gas Groceries Entertainment Computer Equipment Books Gifts Lunch Clothes Lunch Dinner Entertainment Gas Groceries Airline Ticket Baggage Dinner Dinner Hotel Lunch Baggage Books Gas Groceries Computer Equipment Lunch Gifts Gifts Lunch Lunch Gas Airline Ticket Dinner Hotel Lunch Dinner Groceries Lunch Books Entertainment Lunch $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Amount 24.95 10.76 385.16 30.15 214.80 7.50 23.45 100.33 20.00 250.85 26.55 25.38 7.45 155.65 7.50 18.61 20.00 22.85 114.44 385.00 25.00 34.18 14.12 95.85 16.44 15.00 18.44 24.11 120.44 885.12 5.65 54.42 12.18 6.40 7.65 26.18 400.15 36.14 112.44 15.81 30.14 85.67 14.42 22.15 20.00 6.40 Err:508 Business Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Date Store 6/15/2016 Applebee's 6/2/2016 Applebee's 6/12/2016 Chili's 6/14/2016 Texas Roadhouse 6/25/2016 Texas Roadhouse 6/27/2016 Tucano's 6/26/2016 Applebee's 6/20/2016 McDonald's 6/15/2016 McGrath's 6/27/2016 Red Robin 6/22/2016 Subway 6/30/2016 Subway 6/3/2016 Subway 6/9/2016 Subway 6/8/2016 Wendy's 6/22/2016 Wendy's 6/1/2016 Wendy's Description Dinner Dinner Dinner Dinner Dinner Dinner Lunch Lunch Lunch Lunch Lunch Lunch Lunch Lunch Lunch Lunch Lunch $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Amount 14.12 30.15 18.61 34.18 36.14 30.14 15.81 5.65 16.44 14.42 6.40 6.40 7.50 7.50 7.45 7.65 10.76 3,955.88 Business Yes Yes Yes Yes Yes Yes Yes Date Store 6/2/2016 United 6/3/2016 Marriot 6/6/2016 PC Connection 6/14/2016 Delta 6/19/2016 Costco 6/25/2016 American 6/26/2016 Hampton Inn 6/2/2016 Applebee's 6/3/2016 Subway 6/14/2016 Delta 6/14/2016 Texas Roadhouse 6/15/2016 Applebee's 6/15/2016 Hampton Inn 6/15/2016 McGrath's 6/16/2016 Delta 6/25/2016 Texas Roadhouse 6/26/2016 Applebee's 6/1/2016 Barnes & Noble 6/1/2016 Wendy's 6/4/2016 Costco 6/4/2016 Costco 6/5/2016 Theater 6/6/2016 Amazon.com 6/6/2016 Hallmark 6/8/2016 Wendy's 6/9/2016 Dillard's 6/9/2016 Subway 6/12/2016 Chili's 6/12/2016 Theater 6/12/2016 Sam's Club 6/13/2016 Costco 6/18/2016 Sam's Club 6/18/2016 Sam's Club 6/18/2016 Costco Description Airline Ticket Hotel Computer Equipment Airline Ticket Computer Equipment Airline Ticket Hotel Dinner Lunch Baggage Dinner Dinner Hotel Lunch Baggage Dinner Lunch Books Lunch Gas Groceries Entertainment Books Gifts Lunch Clothes Lunch Dinner Entertainment Gas Groceries Books Gas Groceries $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Amount 385.16 214.80 250.85 385.00 885.12 400.15 112.44 30.15 7.50 25.00 34.18 14.12 95.85 16.44 15.00 36.14 15.81 24.95 10.76 23.45 100.33 20.00 26.55 25.38 7.45 155.65 7.50 18.61 20.00 22.85 114.44 18.44 24.11 120.44 Business Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Date Store 6/20/2016 McDonald's 6/22/2016 Dillard's 6/22/2016 Hallmark 6/22/2016 Subway 6/22/2016 Wendy's 6/24/2016 Costco 6/27/2016 Tucano's 6/27/2016 Sam's Club 6/27/2016 Red Robin 6/28/2016 Amazon.com 6/30/2016 Theater 6/30/2016 Subway Total Description Lunch Gifts Gifts Lunch Lunch Gas Dinner Groceries Lunch Books Entertainment Lunch $ $ $ $ $ $ $ $ $ $ $ $ Amount 5.65 54.42 12.18 6.40 7.65 26.18 30.14 85.67 14.42 22.15 20.00 6.40 Err:508 Business GradeIT - Submission MarkUp This is a machine generated report of your submission document containing error details as annotations. Details of all points deducted in the submitted document are listed on this sheet. Assignment Details Submission ID 2266576700 Assignment Name Submission Date 07 February, 2016 Report Generated on 07 February, 2016 Points Obtained 66/100 (66%) Worksheet: 'Dining Out' (Points Deducted: 18.000) Points Deducted 3.000 15.000 Details Cell level Incorrect Actions Error Description 1 cell(s) commented inplace. Please check the sheet. Incorrect Actions: Table(s) 6.000 point(s) deducted in instruction #4: -The table was not sorted by Description in alphabetical order. -The table was not sorted by Store in alphabetical order. -The table was not sorted by Amount from the smallest to the largest. 5.000 point(s) deducted in instruction #5: -The records were not filtered to show personal (i.e., non-business) lunch and dinner expenses. 4.000 point(s) deducted in instruction #6: -The Total Row was not displayed. Worksheet: 'June Expenses' (Points Deducted: 16.000) Points Deducted 4.000 12.000 Details Incorrect Actions: Table(s) Error Description 4.000 point(s) deducted in instruction #10: -Orange (Standard) fill color was not applied on Top as the secondary sort. -Light Green (Standard) fill color was not applied on Top as the primary sort. Incorrect Actions: Conditional Format(s) 8.000 point(s) deducted in instruction #8: -A conditional formatting rule was not created with Light Green fill color. -A conditional formatting rule was not created with Outline preset Border type. -A conditional formatting rule was not created with Green Border color. -In the Conditional Format, the 'Conditional Formatting Formula(e)' was not set to '(AND($D2>=100,$E2="Yes"))'. 4.000 point(s) deducted in instruction #9: -A conditional formatting rule was not created with Orange fill color. -In the Conditional Format, the 'Conditional Formatting Formula(e)' was not set to '(AND($D2<100,$E2="Yes"))'. Date Store 6/15/2016 Applebee's 6/2/2016 Applebee's 6/12/2016 Chili's 6/14/2016 Texas Roadhouse 6/25/2016 Texas Roadhouse 6/27/2016 Tucano's 6/26/2016 Applebee's 6/20/2016 McDonald's 6/15/2016 McGrath's 6/27/2016 Red Robin 6/22/2016 Subway 6/30/2016 Subway 6/3/2016 Subway 6/9/2016 Subway 6/8/2016 Wendy's 6/22/2016 Wendy's 6/1/2016 Wendy's Description Dinner Dinner Dinner Dinner Dinner Dinner Lunch Lunch Lunch Lunch Lunch Lunch Lunch Lunch Lunch Lunch Lunch $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Amount 14.12 30.15 18.61 34.18 36.14 30.14 15.81 5.65 16.44 14.42 6.40 6.40 7.50 7.50 7.45 7.65 10.76 3,955.88 Business Yes Yes Yes Yes Yes Yes Yes Date Store 6/2/2016 United 6/3/2016 Marriot 6/6/2016 PC Connection 6/14/2016 Delta 6/19/2016 Costco 6/25/2016 American 6/26/2016 Hampton Inn 6/2/2016 Applebee's 6/3/2016 Subway 6/14/2016 Delta 6/14/2016 Texas Roadhouse 6/15/2016 Applebee's 6/15/2016 Hampton Inn 6/15/2016 McGrath's 6/16/2016 Delta 6/25/2016 Texas Roadhouse 6/26/2016 Applebee's 6/1/2016 Barnes & Noble 6/1/2016 Wendy's 6/4/2016 Costco 6/4/2016 Costco 6/5/2016 Theater 6/6/2016 Amazon.com 6/6/2016 Hallmark 6/8/2016 Wendy's 6/9/2016 Dillard's 6/9/2016 Subway 6/12/2016 Chili's 6/12/2016 Theater 6/12/2016 Sam's Club 6/13/2016 Costco 6/18/2016 Sam's Club 6/18/2016 Sam's Club 6/18/2016 Costco Description Airline Ticket Hotel Computer Equipment Airline Ticket Computer Equipment Airline Ticket Hotel Dinner Lunch Baggage Dinner Dinner Hotel Lunch Baggage Dinner Lunch Books Lunch Gas Groceries Entertainment Books Gifts Lunch Clothes Lunch Dinner Entertainment Gas Groceries Books Gas Groceries $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Amount 385.16 214.80 250.85 385.00 885.12 400.15 112.44 30.15 7.50 25.00 34.18 14.12 95.85 16.44 15.00 36.14 15.81 24.95 10.76 23.45 100.33 20.00 26.55 25.38 7.45 155.65 7.50 18.61 20.00 22.85 114.44 18.44 24.11 120.44 Business Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Date Store 6/20/2016 McDonald's 6/22/2016 Dillard's 6/22/2016 Hallmark 6/22/2016 Subway 6/22/2016 Wendy's 6/24/2016 Costco 6/27/2016 Tucano's 6/27/2016 Sam's Club 6/27/2016 Red Robin 6/28/2016 Amazon.com 6/30/2016 Theater 6/30/2016 Subway Total Description Lunch Gifts Gifts Lunch Lunch Gas Dinner Groceries Lunch Books Entertainment Lunch $ $ $ $ $ $ $ $ $ $ $ $ Amount 5.65 54.42 12.18 6.40 7.65 26.18 30.14 85.67 14.42 22.15 20.00 6.40 Err:508 Business