Homework answers / question archive / MOS Excel 2019 | Module 6: SAM Project 1a City of Honu Point CREATE, SORT, AND QUERY TABLES GETTING STARTED Open the file SAM_EX19_MOS_6a_FirstLastName_1

MOS Excel 2019 | Module 6: SAM Project 1a City of Honu Point CREATE, SORT, AND QUERY TABLES GETTING STARTED Open the file SAM_EX19_MOS_6a_FirstLastName_1

MS Excel

Share With

---

MOS Excel 2019 | Module 6: SAM Project 1a

City of Honu Point

CREATE, SORT, AND QUERY TABLES

GETTING STARTED

• Open the file SAM_EX19_MOS_6a_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SAM_EX19_MOS_6a_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.
• To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
  • Support_SAM_EX19_MOS_6a_Contacts.txt
• With the file SAM_EX19_MOS_6a_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. Dean Yamaguchi is a development officer for the city of Honu Point in Hawaii. Dean is analyzing city development projects that have been completed, are in progress, and have been proposed. He asks for your help in using Excel tables to complete the analysis.
   
   Go to the Home Page worksheet. Dean wants to include a list of links to resources his managers are likely to need. However, the first link he added is unnecessary.
   
   Remove the hyperlink from the text in cell C4.
2. Dean wants to make it easy for users to navigate to the other worksheets. Add links to other places in the workbook as follows:
   1. In cell C5, insert a link to cell A1 of the Completed Projects worksheet.
   2. In cell C6, insert a link to cell A1 of the Current Projects worksheet.
   3. In cell C7, insert a link to cell A1 of the Proposed Projects worksheet.
   4. In cell C8, insert a link to cell A1 of the Funding Totals worksheet.
   5. In cell C9, insert a link to cell A1 of the Lookup worksheet.
   6. In cell C10, insert a link to cell A1 of the Contacts worksheet.
   7. In cell C11, insert a link to cell A1 of the Households worksheet.
3. Go to the Completed Projects worksheet, which lists the development projects that were completed in Honu Point in 2021. Dean wants to summarize and filter the data and display projects with the highest funding amounts, so create a table as follows:
   1. Format the completed projects data (range A1:F12) as a table with headers using Brown, Table Style Medium 7.
   2. Use CompletedProjects as the name of the table.
   3. Filter the table using a custom AutoFilter to display projects with a Funding amount greater than $10,000.
4. Go to the Current Projects worksheet, which contains the CurrentProjects table listing city development projects that are under review or in development. The city recently received a proposal for a new development project.
   
   Add a row to the end of the CurrentProjects table for a new record containing the data shown in Table 1.

 

Project Name	Orchid Drive Zoning
Start Date	11/20/2021
Number of Days	30
Project Type	Public
Funding Type	Loan
Funding	$2,000
Approved?	No
In Development?	No

 

5. Sort the CurrentProjects table in ascending order by funding amount so that Dean can quickly identify the projects by funding amount.
6. Dean wants to list the projects that are in development in a separate part of the worksheet. Use an advanced filter to list these projects in a new range as follows:
   1. In cell H17, type Yes as the value to filter on in the criteria range.
   2. Create an advanced filter using the CurrentProjects table (range A1:H12) as the List range.
   3. Use the range A16:H17 as the Criteria range.
   4. Copy the results to another location, starting in the range A19:H19.
7. As a contrast, Dean also wants to list the projects that are not in development.
   
   In the CurrentProjects table, use the filter arrows to limit the table display to projects that are not in development.
8. Go to the Proposed Projects worksheet, which lists projects that were proposed in 2021. Dean suspects the ProposedProjects table has a duplicate record. Identify the duplicate as follows:
   1. Clear the filter from the ProposedProjects table to display all the records.
   2. In the range A2:A14, create a conditional formatting Highlight Cells Rule that displays cells with duplicate values using Light Red Fill with Dark Red Text.
   3. Delete the second instance of the duplicate record so that you can summarize the data accurately.
9. The city of Honu Point wants to fast-track mixed-use development projects that use loans for funding.
   
   Add a column to the ProposedProjects table, and determine which projects meet the criteria as follows:
   1. In cell G1, type Fast Track as the column heading.
   2. In cell G2, enter a formula using the AND function that includes structured references to display TRUE if a project has a [Project Type] of "Mixed Use" and a [Funding Type] of "Loan". Fill the range G3:G13 with the formula in cell G2 if Excel does not do so automatically.
10. Add a Total Row to the ProposedProjects table, which automatically counts the number of Fast Track values.
    
    Using the total row, display the sum of the funding amounts.
11. Dean asks you to identify the projects that require 120 days or more to complete, those that require 60 days or more to complete, and those that require less than 60 days to complete.
    1. In the Number of Days column (range C2:C13), create a new Icon Set conditional formatting rule using the 3 Signs icons.
    2. Reverse the icon order.
    3. Display the red diamond icon in cells with a Number type value greater than or equal to 120.
    4. Display the yellow triangle icon in cells with a Number type value greater than or equal to 60.
    5. Display the green circle icon in cells with a Number type value less than 60.
12. Add alt text to the "Number of Days to Complete" chart that reads as follows:
    A 2-D Clustered Bar Chart showing the names of all of the projects and bars representing the number of days to complete the projects.
13. Dean has applied a conditional formatting rule to the range F2:F13 using Orange Gradient Fill data bars to compare the funding amounts visually. He doesn't feel that the visual adds much to the presentation.
    
    Remove the conditional formatting from the range F2:F13.
14. Wrap the text in cell J1 to display the complete contents of the cell.
15. In the range I8:L12, Dean needs to insert a summary of the city development projects from the previous year. Insert this data as a table as follows:
    1. Insert a table in the range I8:L12, specifying that the data has headers.
    2. In the new table, enter the data shown in Table 2.
    3. AutoFit the contents of columns I:L to display the complete cell contents.
    4. Apply Brown, Table Style Medium 7 to the new table to match the formatting of the ProposedProjects table.

 

Project Type	Started	Completed	Funding
Commercial	5	3	45,500
Mixed Use	4	2	57,800
Public	4	3	33,750
Residential	3	3	41,325

 

16. Go to the Funding Totals worksheet, which lists all the current and proposed development projects. Dean wants to display the data by funding type and then list the projects by start date.
    
    Sort the data in the table in ascending order first by funding type and then by start date.
17. Dean also wants to calculate subtotals for each funding type:
    1. Convert the table to a range.
    2. Insert a subtotal at each change in the Funding Type value.
    3. Use the SUM function to calculate the subtotals.
    4. Add subtotals to the Funding values only.
    5. Include a summary below the data.
    6. Collapse the outline to display only the subtotals for each funding type and the grand total.
18. Go to the Contacts worksheet, which lists partial information about the Honu Point contacts, which is contained in a text file. Copy data from the webpage as follows:
    1. Import the data from the Support_SAM_EX19_MOS_6a_Contacts.txt file in cell G12.
    2. Format the imported data in the range G12:M18 as a table using Brown, Table Style Medium 7, if necessary.
19. Dean wants to list the contacts in the range A5:E10. The text file table separated the first and last names, but Dean wants to list the full name on the Contacts worksheet. List the first and last names of each team member in a single cell as follows:
    1. In cell A5, enter a formula using the CONCATENATE function that displays the first name shown in cell H13 followed by a space (" ") and then the last name shown in cell I13.
    2. Fill the range A6:A10 with the formula in cell A5 to list the full names of the remaining team members.
20. Incorporate the imported data in the range B5:E10 as follows:
    1. Copy the Position data from the range J13:J18 and paste only the values in the range B5:B10.
    2. In cell C5, enter a formula using the PROPER function to capitalize the first letter in each word in the Specialty text in cell K13.
    3. Fill the range C6:C10 with the formula in cell C5 to list the specialties of the remaining team members.
    4. In cell D5, enter a formula using the LEFT function to insert the first 2 characters on the left of cell L13. Copy the formula in cell D5 to the range D6:D10.
    5. In cell E5, enter a formula using the RIGHT function to insert the last 2 characters on the right of cell M13. Copy the formula in cell E5 to the range E6:E10.
    6. Resize columns A:D to their best fit, and resize row 4 to 43.5.
    7. Hide rows 12 to 18 so that the worksheet does not display duplicated data.
21. Go to the Households worksheet, which lists populations, households, and persons per household in different counties. Dean wants you to make some final edits and then explore the completed analysis as follows:
    1. Freeze the top row (row 1) of the worksheet.
    2. Add the following document properties to the workbook: Title: City of Honu Point Status: In progress
    3. Display formulas; go to the Proposed Projects worksheet; set the print area to the range A1:H15; choose Landscape Orientation, and Fit Sheet on One Page.
    4. Preview the Print Settings without printing.
    5. Click Show Formulas again the hide the formulas.
    6. Inspect Workbook for issues, scroll through the results in the Document Inspector, and then close the Document Inspector withtout making any changes.

• Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.