$1.$ of the Perez table. Edit the Data Bars conditional formatting rules for the range E$3$:F$13$ to use a Gradient Fill with the Gold, Accent $5$ fill color $(9$th column, $1$st row in the Theme colors palette$).[$Mac hint: Only change the positive value.$]$

$2.$ 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

$3.$ worksheet is scrolled.

$4.$ Nico wants to calculate the totals for the agent data and the difference between the asking prices and sales prices. In cell J$3,$ use the COUNTA function with a structured reference to count the values in the $[$Listing ID$]$ column of the Agents table. In cell J$4,$ use the SUM function with a structured reference to total the 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.

$5.$ 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.

$6.$ 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.

$7.$ Switch to the Han worksheet, which contains listing information for Suki Han. Format the range A$2$:F$10$ as an Excel table with headers using the Olive Green, Table Style Medium $12$ table style. Enter Han as the name of the table.

$8.$ 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

A B C D E F

$11$ M$-8511$ House West $7/18/21500,000500,000$

$9.$ 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.$

$10.$ 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 A$2$:F$12,$ 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.

$11.$ 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.

$12.$ 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.

$13.$ The data bars in the last two columns of the table make some numbers hard to read and could coordinate better with the formatting values in the $[$Asking Price$]$ column of the Agents table. In cell J$5,$ use the SUM function with a structured reference to total the values in the $[$Sales Price$]$ column of the Agents table.

$14.$ 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 G$3,$ enter a formula using structured references but no function to subtract the value in the $[$Sales Price$]$ column $($cell F$3)$ from the value in the $[$Asking Price$]$ column $($cell E$3).$ Fill the range G$4$:G$39$ with the formula in cell G$3$ 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.

$$

Final Figure $1$: All Agents Worksheet

Final Figure $2$: Dahl Worksheet

Final Figure $3$: Han Worksheet

Final Figure $4$: Karis Worksheet

Final Figure $5$: Perez Worksheet