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. 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 to the end of the Han table. all Table 1: New Record for the Han Table 4. M-8511 B House West D E F 7/18/21 500,000 500,000 11 6. 7. 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. 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 CENGAGE 8. 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. 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 3: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 33, use the COUNTA function with a structured reference to count the values in the [Listing ID] column of the Agents table. In cell 34, use the SUM function with a structured reference to total the values in the [Asking Price] column of the Agents table. In cell 35, 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 Formulas Data Review View 11 A- A- O. Wrap Text General Home Insert Page Layout X Cut Calibri (Body) L Copy Paste Format B IV L1 A x fx = Merge & Center $ 7 % ) +.0 .00 .00 .00 .0 Insc Conditional Format Cell Formatting as Table Styles A B D E F G H 1 L M N A All Agents Area Columni Multifamily Listings: Asking price: Sales price: 1 HALLAS REALTY 2 Listing ID - Listing Type - 3 M-0388 West 4 M-0810 Multifamily Downtown 5 M-1167 House North 6 M-1169 Farm South 7 M-1289 Condo East 8 M-1292 House North 9 M-1792 Condo Downtown 10 M-2043 Condo East 11 M-2153 Mobile North 12 M-2298 Condo Downtown 13 M-2388 Condo Downtown 14 M-2453 Condo North 15 M-2482 House North 16 M-2721 House North 17 M-2810 Farm South 18 M-3388 House West 19 M-3390 Condo Downtown 20 M-3477 Condo Downtown 21 M-3478 Mobile South 22 M-3479 House East 23 M-3792 House East 24 M-4321 House East 25 M-4482 House North 26 M-4694 Farm South 27 M-4810 House West 28 M-5153 Condo East 29 M-5390 Condo Downtown 30 M-5530 House East 31 M-5570 Condo West 32 M-5692 House North 33 M-5694 House West 34 M-6692 House East 35 M-7170 House West 36 M-7530 House East 37 M-7562 House North 38 M-7630 Farm South 39 M-8692 House East 40 41 Documentation All Agents Listing Date - 9/2/21 1/14/21 5/14/21 6/12/21 5/1/21 6/4/21 4/24/21 7/11/21 5/14/21 8/1/21 8/2/21 6/14/21 11/1/21 5/27/21 8/1/21 5/22/21 12/1/21 7/11/21 6/12/21 4/4/21 5/14/21 3/19/21 6/4/21 5/2/21 10/14/21 9/1/21 7/11/21 7/11/21 5/12/21 4/20/21 6/12/21 6/12/21 5/12/21 6/12/21 7/13/21 6/14/21 3/31/21 Asking Price $428,995 $859,995 $315,695 $685,995 $359,995 $495,995 $369,995 $294,995 $104,995 $472,595 $341,995 $294,995 $344,995 $414,995 $775,000 $524,995 $597,995 $429,995 $107,500 $344,995 $367,595 $314,995 $525,595 $599,995 $509,995 $317,500 $477,595 $275,995 $304,995 $247,995 $304,995 $394,995 $439,995 $474,995 $585,995 $875,995 $274,995 Sales Price - $450,000 $850,000 $315,95 $685,995 $345,000 $500,000 $375,000 $275,000 $100,000 $480,000 $340,000 $290,000 $0 $425,000 $0 $524,995 $0 $429,995 $107,500 $0 $350,000 $314,995 $520,000 $595,000 $0 $317.500 $0 $0 $304,995 $250,000 $301,000 $380,000 $425,000 $469,000 $585,000 $855,000 $270,000 Dahl Han Karis Perez + Ready Home Page Layout Formulas Data Review View Insert Cut Copy Format Calibri (Body) 11 A- A- Wrap Text General Paste BIU A Merge & Center $ % 4.0 .00 .00 1.0 Condi Forma G1 fx A B D E F H 1 K L A Dahl Listings Listing Type Multifamily House Condo Area West West Downtown Listing Date 9/2/21 10/14/21 7/11/21 Asking Price 428,995 509,995 429,995 Sales Price 450,000 509,995 429,995 1 HALLAS REALTY 2 Listing ID 6 M-0388 7 M-4810 9 M-3477 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 Documentation All Agents Dahl Han Karis Perez Home Insert Page Layout Formulas Data Review View X Cut Calibri (Body) 11 A- A 5 Wrap Text General Paste Copy Format BIU Tile Merge & Center $ % ) 4.0 .00 .00 2.0 Cc FC G1 4 x fx A B D E F G . 1 L Han Listings Nm Listing Date 6/4/21 6/12/21 6/12/21 8/2/21 1/14/21 7/11/21 4/4/21 5/14/21 Asking Price 495,995 304,995 685,995 341,995 859,995 275,995 344,995 104,995 Sales Price 500,000 301,000 685,995 340,000 850,000 100,000 HALLAS REALTY Listing ID Listing Type Area M-1292 House North 4 M-5694 House West 5 M-1169 Farm South 6 M-2388 Condo Downtown 7 M-0810 Multifamily Downtown 8 M-5530 House East 9 M-3479 House East 10 M-2153 Mobile North 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 Documentation All Agents Dahl Han Karis Perez + Ready Home Page Layout Formulas Data Review View Insert Cut Copy A- A- Wrap Text General Calibri (Body) 11 B I UN Paste A, == = = Merge & Center $ 7 % ) Format G1 4 x v fx B D E F H A Karis Listings Sales Price 375,000 380,000 425,000 524,995 Listing Type Condo Condo House House Farm House Condo Condo Condo Condo Area Downtown East West West South East North North Downtown East Listing Date 4/24/21 6/12/21 5/12/21 5/22/21 8/1/21 6/12/21 11/1/21 6/14/21 12/1/21 3/19/21 Asking Price 369,995 394,995 439,995 524,995 775,000 474,995 344,995 294,995 597,995 314,995 469,000 290,000 314,995 1 HALLAS REALTY 2 Listing ID 3 M-1792 4 M-6692 5 M-7170 6 M-3388 7 M-2810 8 M-7530 9 M-2482 10 M-2453 11 M-3390 12 M-4321 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 Documentation All Agents Dahl Han Karis Perez + Ready Home Page Layout Formulas Data Review View Insert Cut Copy 11 A- A Wrap Text General Paste ILMU Calibri (Body) BIU. fx = = = Merge & Center $ 7 % Format G1 4 x v B D E F H Perez Listings Listing Type House Farm Condo House House House Mobile Condo House House Condo Condo Area East South West East North North South Downtown North East East Downtown Listing Date 3/31/21 5/2/21 5/12/21 5/14/21 5/27/21 6/4/21 6/12/21 7/11/21 7/13/21 5/14/21 9/1/21 8/1/21 Asking Price 274,995 599,995 304,995 367,595 484,995 525,595 107,500 477.595 565,995 367,595 317,500 472.595 Sales Price 270,000 595,000 304,995 350,000 495,000 520,000 107,500 585,000 350,000 317,500 4R0.000 1 HALLAS REALTY 2 Listing ID 3 M-8692 4 M-4694 5 M-5570 6 M-3792 7 M-2721 8 M-4482 9 M-3478 10 M-5390 11 M-7562 12 M-3792 13 M-5153 14 M-2298 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 Documentation All Agents Dahl Han Karis Perez + Ready 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. 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 to the end of the Han table. all Table 1: New Record for the Han Table 4. M-8511 B House West D E F 7/18/21 500,000 500,000 11 6. 7. 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. 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 CENGAGE 8. 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. 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 3: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 33, use the COUNTA function with a structured reference to count the values in the [Listing ID] column of the Agents table. In cell 34, use the SUM function with a structured reference to total the values in the [Asking Price] column of the Agents table. In cell 35, 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 Formulas Data Review View 11 A- A- O. Wrap Text General Home Insert Page Layout X Cut Calibri (Body) L Copy Paste Format B IV L1 A x fx = Merge & Center $ 7 % ) +.0 .00 .00 .00 .0 Insc Conditional Format Cell Formatting as Table Styles A B D E F G H 1 L M N A All Agents Area Columni Multifamily Listings: Asking price: Sales price: 1 HALLAS REALTY 2 Listing ID - Listing Type - 3 M-0388 West 4 M-0810 Multifamily Downtown 5 M-1167 House North 6 M-1169 Farm South 7 M-1289 Condo East 8 M-1292 House North 9 M-1792 Condo Downtown 10 M-2043 Condo East 11 M-2153 Mobile North 12 M-2298 Condo Downtown 13 M-2388 Condo Downtown 14 M-2453 Condo North 15 M-2482 House North 16 M-2721 House North 17 M-2810 Farm South 18 M-3388 House West 19 M-3390 Condo Downtown 20 M-3477 Condo Downtown 21 M-3478 Mobile South 22 M-3479 House East 23 M-3792 House East 24 M-4321 House East 25 M-4482 House North 26 M-4694 Farm South 27 M-4810 House West 28 M-5153 Condo East 29 M-5390 Condo Downtown 30 M-5530 House East 31 M-5570 Condo West 32 M-5692 House North 33 M-5694 House West 34 M-6692 House East 35 M-7170 House West 36 M-7530 House East 37 M-7562 House North 38 M-7630 Farm South 39 M-8692 House East 40 41 Documentation All Agents Listing Date - 9/2/21 1/14/21 5/14/21 6/12/21 5/1/21 6/4/21 4/24/21 7/11/21 5/14/21 8/1/21 8/2/21 6/14/21 11/1/21 5/27/21 8/1/21 5/22/21 12/1/21 7/11/21 6/12/21 4/4/21 5/14/21 3/19/21 6/4/21 5/2/21 10/14/21 9/1/21 7/11/21 7/11/21 5/12/21 4/20/21 6/12/21 6/12/21 5/12/21 6/12/21 7/13/21 6/14/21 3/31/21 Asking Price $428,995 $859,995 $315,695 $685,995 $359,995 $495,995 $369,995 $294,995 $104,995 $472,595 $341,995 $294,995 $344,995 $414,995 $775,000 $524,995 $597,995 $429,995 $107,500 $344,995 $367,595 $314,995 $525,595 $599,995 $509,995 $317,500 $477,595 $275,995 $304,995 $247,995 $304,995 $394,995 $439,995 $474,995 $585,995 $875,995 $274,995 Sales Price - $450,000 $850,000 $315,95 $685,995 $345,000 $500,000 $375,000 $275,000 $100,000 $480,000 $340,000 $290,000 $0 $425,000 $0 $524,995 $0 $429,995 $107,500 $0 $350,000 $314,995 $520,000 $595,000 $0 $317.500 $0 $0 $304,995 $250,000 $301,000 $380,000 $425,000 $469,000 $585,000 $855,000 $270,000 Dahl Han Karis Perez + Ready Home Page Layout Formulas Data Review View Insert Cut Copy Format Calibri (Body) 11 A- A- Wrap Text General Paste BIU A Merge & Center $ % 4.0 .00 .00 1.0 Condi Forma G1 fx A B D E F H 1 K L A Dahl Listings Listing Type Multifamily House Condo Area West West Downtown Listing Date 9/2/21 10/14/21 7/11/21 Asking Price 428,995 509,995 429,995 Sales Price 450,000 509,995 429,995 1 HALLAS REALTY 2 Listing ID 6 M-0388 7 M-4810 9 M-3477 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 Documentation All Agents Dahl Han Karis Perez Home Insert Page Layout Formulas Data Review View X Cut Calibri (Body) 11 A- A 5 Wrap Text General Paste Copy Format BIU Tile Merge & Center $ % ) 4.0 .00 .00 2.0 Cc FC G1 4 x fx A B D E F G . 1 L Han Listings Nm Listing Date 6/4/21 6/12/21 6/12/21 8/2/21 1/14/21 7/11/21 4/4/21 5/14/21 Asking Price 495,995 304,995 685,995 341,995 859,995 275,995 344,995 104,995 Sales Price 500,000 301,000 685,995 340,000 850,000 100,000 HALLAS REALTY Listing ID Listing Type Area M-1292 House North 4 M-5694 House West 5 M-1169 Farm South 6 M-2388 Condo Downtown 7 M-0810 Multifamily Downtown 8 M-5530 House East 9 M-3479 House East 10 M-2153 Mobile North 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 Documentation All Agents Dahl Han Karis Perez + Ready Home Page Layout Formulas Data Review View Insert Cut Copy A- A- Wrap Text General Calibri (Body) 11 B I UN Paste A, == = = Merge & Center $ 7 % ) Format G1 4 x v fx B D E F H A Karis Listings Sales Price 375,000 380,000 425,000 524,995 Listing Type Condo Condo House House Farm House Condo Condo Condo Condo Area Downtown East West West South East North North Downtown East Listing Date 4/24/21 6/12/21 5/12/21 5/22/21 8/1/21 6/12/21 11/1/21 6/14/21 12/1/21 3/19/21 Asking Price 369,995 394,995 439,995 524,995 775,000 474,995 344,995 294,995 597,995 314,995 469,000 290,000 314,995 1 HALLAS REALTY 2 Listing ID 3 M-1792 4 M-6692 5 M-7170 6 M-3388 7 M-2810 8 M-7530 9 M-2482 10 M-2453 11 M-3390 12 M-4321 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 Documentation All Agents Dahl Han Karis Perez + Ready Home Page Layout Formulas Data Review View Insert Cut Copy 11 A- A Wrap Text General Paste ILMU Calibri (Body) BIU. fx = = = Merge & Center $ 7 % Format G1 4 x v B D E F H Perez Listings Listing Type House Farm Condo House House House Mobile Condo House House Condo Condo Area East South West East North North South Downtown North East East Downtown Listing Date 3/31/21 5/2/21 5/12/21 5/14/21 5/27/21 6/4/21 6/12/21 7/11/21 7/13/21 5/14/21 9/1/21 8/1/21 Asking Price 274,995 599,995 304,995 367,595 484,995 525,595 107,500 477.595 565,995 367,595 317,500 472.595 Sales Price 270,000 595,000 304,995 350,000 495,000 520,000 107,500 585,000 350,000 317,500 4R0.000 1 HALLAS REALTY 2 Listing ID 3 M-8692 4 M-4694 5 M-5570 6 M-3792 7 M-2721 8 M-4482 9 M-3478 10 M-5390 11 M-7562 12 M-3792 13 M-5153 14 M-2298 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 Documentation All Agents Dahl Han Karis Perez + Ready
