04 Aug Remove the filters from the Dahl table to display all of the data.

PROJECT STEPS

1.Nico Hallas runs Hallas Realty, a small but growing real estate agency in Columbia, South Carolina. He keeps track of the listings of his four agents in an Excel workbook, and asks for your help in updating and analyzing the data. Switch to the Dahl worksheet, which contains a table named Dahl for Louisa Dahl’s listings. Unfreeze the top row of the worksheet because the worksheet is not long enough to scroll.

2.Remove the filters from the Dahl table to display all of the data. Sort the data in ascending order first by the Listing Type field and then in descending order by the Sales Price field to make it easy to track her listings.

3.Nico wants to compare the average asking prices and average sales prices for the Dahl listings. Insert a Total Row in the Dahl table, and then use the Total row to calculate the average of the values in the Asking Price and Sales Price columns.

4.Switch to the Han worksheet, which contains listing information for Suki Han. Format the range A2:F10 as an Excel table with headers using the Olive Green, Table Style Medium 12 table style. Enter Han as the name of the table.

5.Nico needs to add a listing for Suki Han. Add the record shown in Table 1 to the end of the Han table.

Table 1: New Record for the Han Table

6.He also wants to focus on Suki Han’s sales of $500,000 or more. Use a custom Number filter to display only listings with sales prices greater than or equal to 500,000.

7.Real estate agent Mike Karis sells more condos than any other agent. Nico wants to summarize the Karis listings data using subtotals to show the value of each property type, especially condos. Switch to the Karis worksheet and then sort the table in ascending order by the Listing Type field. Convert the table to a normal range. Insert subtotals into the range A2:F12, with the subtotals appearing at each change in the Listing Type column value. The subtotals should use the SUM function and include subtotals for the Asking Price and Sales Price fields.

8.Switch to the Perez worksheet, which contains a table named Perez that lists data for real estate agent Arturo Perez. Apply the Olive Green, Table Style Medium 12 table style to the Perez table, and then display the filter buttons to match the formatting of the other tables in the workbook.

9.Nico noticed that the Perez table includes a duplicate record. Use a table tool to remove the duplicate record based on the values in the Listing ID and Listing Date columns.

10.The data bars in the last two columns of the table make some numbers hard to read and could coordinate better with the formatting of the Perez table. Edit the Data Bars conditional formatting rules for the range E3:F13 to use a Gradient Fill with the Gold, Accent 5 fill color (9th column, 1st row in the Theme colors palette). [Mac hint: Only change the positive value.]

11.Switch to the All Agents worksheet, which contains a table named Agents listing data for all of the Hallas Realty agents. Freeze the first two rows of the worksheet to display the worksheet title and column headings when the worksheet is scrolled.

12.Nico wants to calculate the totals for the agent data and the difference between the asking prices and sales prices. In cell J3, use the COUNTA function with a structured reference to count the values in the [Listing ID] column of the Agents table. In cell J4, use the SUM function with a structured reference to total the values in the [Asking Price] column of the Agents table. In cell J5, use the SUM function with a structured reference to total the values in the [Sales Price] column of the Agents table.

13.Nico added a table column to the end of the Agents table to calculate the difference between the asking prices and sales prices. Use Difference as the column heading. In cell G3, enter a formula using structured references but no function to subtract the value in the [Sales Price] column (cell F3) from the value in the [Asking Price] column (cell E3). Fill the range G4:G39 with the formula in cell G3 if Excel does not automatically do so.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.