New Perspectives Excel 2019 | Module 6: End of Module Project 2 Hallas Real Estate MANAGING YOUR DATA WITH DATA TOOLS GETTING STARTED ? Open the file NP_EX19_EOM6-2_ FirstLastName _1

Subject:MS ExcelPrice:19.87 Bought4

Share With

New Perspectives Excel 2019 | Module 6: End of Module Project 2

Hallas Real Estate

MANAGING YOUR DATA WITH DATA TOOLS

GETTING STARTED

?

Open the file

NP_EX19_EOM6-2_

FirstLastName

_1.xlsx

, available for download from

the SAM website.

?

Save the file as

NP_EX19_EOM6-2_

FirstLastName

_2.xlsx

by changing the “1” to a

“2”.

?

If you do not see the

.xlsx

file extension in the Save As dialog box, do not type it. The

program will add the file extension for you automatically.

?

With the file

NP_EX19_EOM6-2_

FirstLastName

_2.xlsx

still open, ensure that your first

and last name is displayed in cell B6 of the Documentation sheet.

?

If cell B6 does not display your name, delete the file and download a new copy from the

SAM website.

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

A

B

C

D

E

F

11

M-8511

House

West

7/18/21

500,000

500,000

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.

New Perspectives Excel 2019 | Module 6: End of Module Project 2

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