Project excel

Steps to complete This Project Mark the steps as checked when you complete them. Open the PlacerHills-04 start file. If the workbook opens in Protected View, click the Enable Editing button so you can modify it. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it. Apply conditional formatting with a formula. Select cells E5:E26 on the Listings sheet. Build a conditional formatting formula to display the address cell in light tan for frame construction. The formula is =k5=“frame” and light tan is the third column in the color palette. Pivot fields in a PivotTable and complete formatting. Select the PivotTable sheet. Deselect the City field in the PivotTable Fields pane. Show the Agent ID field in the Rows area. Show the Sale Price field two times in the Values area. Format the first occurrence of the Sale Price field with the custom name Total Sales and Currency with zero decimals. Format the second occurrence of the Sale Price field with the name Average Sale, choose Average as the calculation, and use Currency with zero decimals. Apply Light Gray, Pivot Style Light 15. Apply All Borders to cells A3:C9. Add a calculated field to a table. Select the Table sheet. Type Days in cell O4 and press Enter. In cell O5, build an IF formula to display the number of days on the market. Define a logical_test to calculate if the sale date is greater than the list date. The value_if_true argument is the sale date minus the list date. For the value_if_false argument, press Spacebar once in the entry box in the Function Arguments dialog box; this displays a space in the cell when no sale date is available. (If you type the formula, enclose a space in quotation marks for the value_if_false argument.) Build a PivotTable. Select cell A4 and create a PivotTable in a new worksheet. Confirm that the Add this data to the Data Model box near the bottom of the dialog box is not selected. (If you select this box, you will not be able to create a calculated field in step 6.) Name the sheet PivotTable 2. Place the Listing ID field in the Rows area and the List Price and Sale Price fields in the Values area. Edit the labels in row 3 to Listing, List, and Sale. Format the list and sale prices as Currency with no decimals. Insert a calculated field in a PivotTable. Insert a calculated field named Difference with a formula that subtracts the sale price from the list price. Change the display label for the calculated field to Variance. Format the Variance values to show negative numbers in red with parentheses. Select all the cells in the PivotTable and apply All Borders. Save and close the workbook (Figure 4-125). Excel 4-7 completed Figure 4-125 Excel 4-7 completed Upload and save your project file. Submit file for grading.