Mountain View Realty Project Description: A coworker developed a spreadsheet listing houses listed and sold during the past several months. She included addresses, location, list price, selling price, listing date, and date sold. You need to convert the data to a table. You will manage the large worksheet, prepare the worksheet for printing, sort and filter the table, include calculations, and format the table. 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 file named exploring_e04_grader_h1.xlsx. 0 2 In the Sales Data worksheet, freeze the top row. 5 3 In the Sales Data worksheet, convert the data to a table and apply Table Style Medium 17. 6 4 In the Sales Data worksheet, remove duplicate records in the table. 5 5 In the Sales Data worksheet, insert a new field to the right of the Selling Price field. Name the new field Percent of List Price. 6 6 In the Sales Data worksheet, create a formula in cell E2 with structured references to calculate what percent the selling price is of the list price.Format the field with Percent Style with one decimal place in the range E2:E43. 6 7 In the Sales Data worksheet, insert a new field to the right of the Sale Date field. Name the new field Days on Market. Create a formula with structured references to calculate the number of days on the market. 6 8 In the Sales Data worksheet, add a total row to display the average percent of list price and average number of days on market. Apply the General number format to the average number of days on market. Type Averages in cell A44. 10 9 In the Sales Data worksheet, sort the table by City in alphabetical order and add a second level to sort by Days on Market with the houses on the market the longest at the top within each city. 8 10 In the Sales Data worksheet, select the Listing Date and Sale Date fields and set a column width of 11.00. Wrap the column labels in the range E1:H1. 6 11 Set up the Sales Data worksheet so that when printed, the first row containing the field names will print as titles on each page. 5 12 Display the Sales Data worksheet in Page Break Preview and move the page break to occur between rows 26 and 27, and then change back to Normal view. 5 13 Display the Filtered Data worksheet. Convert the table to a range of data. 5 14 Filter the data to display the cities of Alpine, Cedar Hills, and Eagle Mountain. 6 15 Use a custom AutoFilter to display records for houses that were on the market 30 days or more. 5 16 Apply the 3 Arrows (Colored) icon set to the Days on Market field values. Do not include the field name in the range. 5 17 Apply the Light Blue Data Bar conditional formatting in the Gradient Fill section to the selling prices. 5 18 Create a new conditional format that applies Yellow fill (fourth color in the bottom row) and bold font to values that are higher than 98% for the Percent of List Price column. 6 19 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Sales Data, Filtered Data. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0 Total Points 100

Address	City	List Price	Selling Price	Listing Date	Sale Date
8687 Kenwood Road	Cedar Hills	$ 725,000	$ 645,250	4/1/2016	6/16/2016
11 West Oak Circle	American Fork	$ 350,000	$ 340,000	4/1/2016	5/15/2016
614 West Cedar Drive	Lehi	$ 215,800	$ 200,000	4/1/2016	5/25/2016
750 South Apple Way	American Fork	$ 385,900	$ 385,900	4/1/2016	4/30/2016
7 Kingston Court	Alpine	$ 500,000	$ 465,000	4/3/2016	6/10/2016
321 North Choctaw	American Fork	$ 565,000	$ 535,000	4/6/2016	9/30/2016
1370 Pinellas Road	Alpine	$ 219,000	$ 215,000	4/8/2016	6/2/2016
1971 Glenview Road	Lehi	$ 165,000	$ 156,750	4/12/2016	6/12/2016
575 South Choctaw	Alpine	$ 750,250	$ 700,000	4/12/2016	11/1/2016
143 North Mountain View	American Fork	$ 314,250	$ 304,000	4/13/2016	8/5/2016
29 East Oak Circle	American Fork	$ 555,000	$ 500,000	4/13/2016	10/15/2016
8030 Steeplechase Drive	Alpine	$ 450,000	$ 382,500	4/15/2016	6/30/2016
2006 Cutwater Court	Alpine	$ 345,000	$ 339,999	4/18/2016	6/23/2016
9876 South Sunset Avenue	Eagle Mountain	$ 300,000	$ 300,000	4/18/2016	4/30/2016
321 West Walnut Grove	American Fork	$ 425,000	$ 415,000	4/18/2016	6/30/2016
4081 Lybyer Avenue	Alpine	$ 325,000	$ 308,750	4/19/2016	7/2/2016
1900 Glenview Road	Lehi	$ 325,000	$ 302,250	4/28/2016	6/1/2016
9000 South Sunset Avenue	Eagle Mountain	$ 325,000	$ 320,000	4/28/2016	5/28/2016
8432 South Sunset Avenue	Eagle Mountain	$ 285,750	$ 300,000	4/28/2016	6/3/2016
831 South Weber	American Fork	$ 425,815	$ 400,000	4/28/2016	9/15/2016
714 Timp View Lane	American Fork	$ 250,000	$ 232,000	4/29/2016	9/1/2016
224 Rockaway Street	Alpine	$ 400,000	$ 375,000	4/30/2016	6/19/2016
260 East Jefferson Way	Eagle Mountain	$ 450,000	$ 400,000	4/30/2016	10/1/2016
260 East Jefferson Way	Eagle Mountain	$ 450,000	$ 400,000	4/30/2016	10/1/2016
8307 S Indian River Drive	Lehi	$ 250,000	$ 255,000	5/1/2016	6/18/2016
123 Ivy Lane	Eagle Mountain	$ 375,500	$ 375,500	5/1/2016	5/5/2016
245 Ivy Lane	Eagle Mountain	$ 395,000	$ 375,000	5/1/2016	7/16/2016
11971 SW 269 Terrace	Cedar Hills	$ 410,000	$ 397,700	5/1/2016	6/26/2016
1857 Pine Drive	Cedar Hills	$ 560,700	$ 550,000	5/1/2016	5/30/2016
3461 East Lindon Way	American Fork	$ 450,000	$ 400,000	5/2/2016	9/30/2016
16235 Orange Boulevard	Cedar Hills	$ 395,000	$ 380,000	5/5/2016	7/16/2016
614 Lincoln Drive	Eagle Mountain	$ 475,000	$ 425,250	5/5/2016	9/23/2016
421 Ivy Lane	Eagle Mountain	$ 500,000	$ 425,000	5/5/2016	11/15/2016
765 East Sheridan Lane	Eagle Mountain	$ 460,750	$ 435,500	5/5/2016	11/1/2016
1414 N Sheridan Road	Alpine	$ 1,250,000	$ 1,225,000	5/12/2016	6/6/2016
487 Blue Skies Drive	American Fork	$ 365,750	$ 355,000	5/13/2016	8/15/2016
10700 Lake Shore Lane	Cedar Hills	$ 650,000	$ 598,000	5/15/2016	6/9/2016
73 East Oak Street	Eagle Mountain	$ 325,000	$ 325,000	5/18/2016	5/31/2016
73 East Oak Street	Eagle Mountain	$ 325,000	$ 325,000	5/18/2016	5/31/2016
3412 Kilmer Street	Lehi	$ 180,000	$ 175,000	5/23/2016	7/15/2016
77 East Oak Street	Eagle Mountain	$ 345,000	$ 335,000	5/23/2016	6/23/2016
1629 NW 43rd Street	Cedar Hills	$ 475,000	$ 450,000	5/31/2016	8/19/2016
3400 North Sunset Lane	Eagle Mountain	$ 400,000	$ 375,000	5/31/2016	7/15/2016
876 South California Way	American Fork	$ 316,000	$ 316,000	5/31/2016	6/30/2016