New Perspectives Excel 365 | Module 6: SAM Project B Charlotte Development

GETTING STARTED

1. Save the file NP_EX365_6B_FirstLastName_1.xlsx as NP_EX365_6B_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

2. With the file NP_EX365_6B_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

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

PROJECT STEPS

1. Teresa Rosales is a project assistant for Charlotte Development, a real estate firm specializing in commercial property in North Carolina. She tracks the real estate agents' properties and sales in an Excel workbook and asks for your help in updating and analyzing the data. Go to the Carter Properties worksheet, which lists sales data for Derrick Carter. Teresa asks you to format the data as follows to match the data for the other agents:

a. Format the range A2:F12 as an Excel table with headers using the Olive Green, Table Style Medium 5 table style.

b. Use Carter as the name of the table.

2. Teresa wants you to add a new property for Derrick Carter. Add the record shown in Table 1 to the end of the Carter table.

Table 1: New Record for the Carter Table

| A | B | C | D | E | F 13 | PP-5694 | Office | Class A | Greensboro | 1,379,200 | 1,379,200

3. She also wants you to focus on Derrick Carter's properties with listing amounts over $1,500,000. Use a custom Number filter to display only properties with listing amounts greater than 1,500,000.

4. Go to the Quinn Properties worksheet, which contains a table named Quinn for Helena Quinn's properties. Unfreeze the panes in the worksheet because it is not long or wide enough to scroll.

5. Teresa asks you to organize the data by property type and listing amount and to display the average amounts of listings and sales. Modify the Quinn table as follows:

a. Remove the filter from the Quinn table to display all of the data.

b. Sort the data in ascending order first by the Property Type field and then in descending order by the Listing Amount field.

c. Insert a Total row in the Quinn table.

d. Use the Total row to calculate the average listing amount and average sales amount.

e. In cell A11, change the text to Average to indicate what the amounts represent.

6. Go to the Cortez Properties worksheet. Edgar Cortez has sold more properties than any other agent. Teresa wants you to summarize the Cortez property data using subtotals to show the value of the properties of each type. Modify the Cortez data as follows:

a. Sort the Cortez table in ascending order by the Property Type field.

b. Convert the table to a normal range.

c. Insert subtotals into the range A2:F17, with the subtotals appearing at each change in the Property Type column value.

d. Add subtotals using the SUM function to the Listing Amount and Sales Amount columns.

7. Go to the Mikos Properties worksheet. The Mikos table on this worksheet lists commercial properties for Constantine Mikos. Format the table to match others in the workbook as follows:

a. Apply the Olive Green, Table Style Medium 5 table style to the Mikos table.

b. Display the filter buttons.

8. Teresa knows that the Mikos table includes a duplicate record and asks you to remove it. Use a table tool to remove the duplicate record based on the values in all the columns in the table.

9. Go to the All Agents worksheet, which contains a table named Agents listing data for all Charlotte Development real estate agents. Freeze the first two rows of the worksheet to display the worksheet title and table column headings when the worksheet is scrolled.

10. Teresa asks you to calculate the difference between the listing amounts and sales amounts. Modify the Agents table as follows:

a. Add a table column to the end of the Agents table.

b. Use Difference as the column heading.

c. Apply a middle vertical alignment to the column heading and use AutoFit to adjust the width of the column.

d. In cell G3, enter a formula using structured references and no function to subtract the value in the [Listing Amount] column from the value in the [Sales Amount] column.

e. Fill the range G4:G39 with the formula in cell G3 if Excel does not automatically do so.

11. She also wants you to make the following calculations in the range J3:J5:

a. In cell J3, use the COUNTA function with a structured reference to count the values in the [Property ID] column of the Agents table.

b. In cell J4, use the SUM function with a structured reference to total the values in the [Listing Amount] column of the Agents table.

c. In cell J5, use the SUM function with a structured reference to total the values in the [Sales Amount] column of the Agents table.

12. Teresa wants you to make it easy to filter the Agents table based on the property type and city. Add slicers to the table as follows:

a. Insert two slicers, one based on the Property Type field and the other based on the City field in the Agents table.

b. Apply the slicer style Light Green, Slicer Style Light 4 to the two slicers.

c. Resize and reposition the Property Type slicer so it covers the range I7:J13.

d. Resize and reposition the City slicer so it covers the range I15:J22.

13. Remove the filter buttons from the Agents table because you can now use the slicers to filter the data.

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 website to submit your completed project.

Final Figure 1: All Agents Worksheet

Final Figure 2: Carter Properties Worksheet

Final Figure 3: Quinn Properties Worksheet

Final Figure 4: Cortez Properties Worksheet

Final Figure 5: Mikos Properties Worksheet