# Homework Solution: A coworker developed a spreadsheet listing houses listed and sold during the past several months. She includ…

Excel Help 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.
2 In the Sales Data worksheet, freeze the top row.
3 In the Sales Data worksheet, convert the data to a table and apply Table Style Medium 17.
4 In the Sales Data worksheet, remove duplicate records in the table.
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 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.
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.
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.
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.
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.
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.
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.
13 Display the Filtered Data worksheet. Convert the table to a range of data.
14 Filter the data to display the cities of AlpineCedar Hills, and Eagle Mountain.
15 Use a custom AutoFilter to display records for houses that were on the market 30 days or more.
16 Apply the 3 Arrows (Colored) icon set to the Days on Market field values. Do not include the field name in the range.
17 Apply the Light Blue Data Bar conditional formatting in the Gradient Fill section to the selling prices.
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.
19 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Sales DataFiltered Data.

Achieve Help

A coworker familiar a spreadshuffle registering houses registered and sold during the late different months. She intervening appendresses, subsidence, register compensation, selling compensation, registering time, and time sold. You want to transform the postulates to a board. You get administer the liberal worksheet, make-ready the workshuffle ce printing, description and percolate the board, grasp calculations, and cemat the board.

## Expert Confutation

Step 2:

The aftercitedcited steps are used to benumb the extreme method in the Sales Postulates worksheet:

• Excellent the integral method using shortcleave explanation Shift + Space or click on the method reckon 1 using mouse pointer.
• Go to View menu and achievelent the benumb extreme method option.

Step 3:

The aftercitedcited steps are used to transform the postulates to a board and devote legend fashion Fashion Medium 17 in Sales Postulates worksheet:

• Excellent the enjoin of cells.
• Click on the Design menu.
• Select Fashion Medium 17 in the Board fashion liberty.

Step 4: The aftercitedcited steps are used to transport the likeness chronicles in the boards, offer in the

Sales Postulates worksheet:

• Excellent the enjoin of cells.
• Click on the Data menu.
• Select Transport Likenesss option.

• Transport Likenesss dialog pummel get spread-outs.
• Click on Excellent Full button and click ok.

Step 5:

The aftercitedcited steps are used to inoculate a stenjoin opportunity to the straight of the Selling Compensation opportunity in the Sales Postulatesworksheet:

• Excellent the Listing Time field.
• Click on the Home menu.
• Select insert percolate down armethod and achievelent Inoculate Board Shaft on the left option.

The aftercitedcited steps are used to transmute the opportunity designate:

• Excellent the stenjoin opportunity.
• Click on Formulas delay and Transmute the designate from column1 to Percent of Register Compensation.

Step 6:

The aftercitedcited steps are used to cause cemula in cell E2 to estimate the percent of the register compensation in the Sales Postulates worksheet:

• Excellent the shaft E up to enjoin from E2 to E43.
• Excellent Percentage (%) liberty ce percentage fashion.
• Excellent individual decimal establish liberty.

Excellent the cell E2 and transcribe the percentage cemula.

=[@[Selling Compensation]]/[@[Register Compensation]]

i.e, =D2-C2

Step 7:

The aftercitedcited steps are used to cause a stenjoin opportunity to the straight of the Sale Time opportunity using cemula:

• Select H2 cell.
• Enter the cemula,
• =G2-F2
• i.e, Sales Time – Registering Time
• The stenjoin shaft automatically causes with the completion reckon of days betwixt Listing Time and Sale Time.

Sample Shadeshot ce cause a stenjoin opportunity:

Sample Shadeshot aftercited entering the Cemula:

• Excellent the H1 shaft and transmute the opportunity designate as Days on Trade in the cemula delay.

Sample output shadeshot to transmute the opportunity designate of H1:

Step 8:

Enter the citation Mediocre in a A44 cell.

The aftercitedcited steps are used to experience the mediocre percentage of Register Compensation:

• Excellent the cell E44.
• The cemula ce estimate the mediocre percentage in achieve is demonstration below:
• < >Here function_num is 101 indicates AVERAGE and ref1ref2, … are the shaft enjoin.
• Use the aftercitedcited cemula to transcribe in a cell E44:
• =SUBTOTAL(101,[Percent of Register Compensation])
• Here [Percent of Register Compensation] is defines the enjoin E2 to E43.

Sample output shadeshot of the mediocre percentage of register compensation:

The aftercitedcited steps are used to experience the mediocre reckon of days on trade:

• Excellent the cell E44.
• The cemula ce estimate the mediocre percentage in achieve is demonstration below:
• < >Here function_num is 101 indicates AVERAGE and ref1ref2, … are the shaft enjoin.
• Use the aftercitedcited cemula to transcribe in a cell E44:
• Here [Days on Trade] is defines the enjoin E2 to E43.

Step 9:

The aftercitedcited steps are used to description the board by city in alphabetical enjoin:

• Excellent the cell enjoin from B2 to B43 of the city opportunity.
• Click the Data menu and achievelent the sort option.

The aftercitedcited steps are used to description the board by Days on Trade, the liberalst at the extreme with each city:

• Excellent the cell enjoin from H2 to H43 of the Days on Trade field.
• Click the Data menu and achievelent the sort option.

Step10:

The aftercitedcited steps are used to fixed a shaft width of 11.00 to the Listing Time and Sale Time fields.

• Excellent the opportunitys Listing Time and Sale Time.
• Click the Format dropdown icon and achievelent the Shaft Width option.
• Enter the width prize 11.00 in the citationpummel and click ok.

The aftercitedcited steps are used to fold the shaft labels in the enjoin E1:H1

• Excellent the opportunitys from the enjoin E1 to H1.
• Excellent the Fold Citation option.

Step 11:

The aftercitedcited steps are used to reproduce the opportunity designates on full pages in the Sales Postulates worksheet.

• Click the Page Layout tab menu.
• Excellent the Print Titles charge.

Screenshot:

• The Page Fixedup dialog pummel get show.
• Click the Sheet tab.
• Enter \$1:\$1 in a Rows to reproduce at extreme opportunity and click ok.

Screenshot:

• The cursor get behove a smfull achievelention armethod and the Page Fixedup dialog pummel get be contracted.

Step 12:

The aftercitedcited steps are used to agitate the page shiver to betide betwixt methods 26 and 27:

• Click on the Page Shiver Preview icon underneathneath the View tab.
• Arenjoin the page shiver method in betwixt methods 26 and 27.

Screenshot:

The aftercitedcited steps are used to transmute end to the usual apprehension:

• Go to the Apprehension tab.
• Click Normal icon in the Workbook Apprehensions group.

Screenshot:

Step 13:

The aftercitedcited steps are used spread-out the Percolateed Postulates Worksheet:

• Excellent any cell in the board.
• Excellent the Data tab, and then click the Percolate charge. A percolate-down armethod get show in the header cell ce each shaft.

Screenshot:

The aftercitedcited steps are used transform the board to a enjoin of postulates:

• Excellent any cell in the board, the Design tab get show.
• Excellent the Design tab, and then click Transform to Enjoin liberty in the Tools group.
• A dialog pummel get show, click ok.

Screenshot:

Step 14:

The aftercitedcited steps are used to percolate the postulates to spread-out the cities of Alpine, Cedar Hills, and Eagle Mountain:

• Click the percolate down armethod icon on the city field.
• Deexcellent the achievelent full liberty.
• Excellent Alpine, Cedar Hills, and Eagle Mountain cities,
• Click ok.

Screenshot:

Step 15:

The aftercitedcited steps are used to spread-out chronicles ce houses that were on the trade 30 days or over:

• Click the percolatedown icon of the Days on Trade opportunity to percolate the postulates.
• Deexcellent the checkboxes which is less than 30.
• Click ok.

Screenshot:

Step 16:

The aftercitedcited steps are used to devote the 3 arrows (Colored) icon to the days on trade prizes:

• Excellent the enjoin of methods of the Days on Trade field.
• Click the Home tab.
• Excellent the Biased Cematting percolate down icon.
• Establish the mouse pointer on Icon Fixeds sub menu.
• An icon menu get open; achievelent the 3 Armethod (Colored) option.

Screenshot:

Step 17:

The aftercitedcited steps are used to devote the Light Blue Postulates Delay biased cematting in the gradient content exception to the Selling Compensations:

• Excellent the enjoin of cells of the Selling Compensations field.
• On the Home tab, click the Biased Cematting percolate down icon.
• Establish the mouse pointer on the Postulates Delays submenu, achievelent the Light Blue Postulates Delay liberty on the on the Gradient Content section.

Screenshot:

Step 18:

The aftercitedcited steps are used to cause a stenjoin biased cemat that applies yellow content and daring font to the prizes that are loftier than 98% ce the Percent of Register Compensation column:

• Excellent the enjoin of the cells in the Percent of Register Compensation column.
• On the Home menu tab, click the Biased Cematting percolate down icon.
• Establish the mouse pointer on the submenu of Highlight Cell Methods, the stenjoin menu get show and achievelent the Greater Than option.

Screenshot:

• The Greater Than dialog pummel get show on the shade.
• Transmute the percentage prize from 95.0% to 98.0%.
• Click the percolate down icon of the garbling combo pummel. Achievelent the Use Cemat option.

Screenshot:

• Format Cells dialog pummel get show.
• Click the Fill tab.
• Excellent the yellow garbling which is fourth garbling in the ground method.

Screenshot:

• Click the Font tab and achievelent the Bold liberty in the Font fashion register pummel.

Screenshot:

• Click ok.