New Perspectives Excel 365 | Module 7: End of Module Project 2 Blackstone Landscaping
SUMMARIZING YOUR DATA WITH PIVOTTABLES
· Excel Projects Help
GETTING STARTED
1. Save the file NP_EX365_EOM7-2_FirstLastName_1.xlsx as NP_EX365_EOM7-2_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_EOM7-2_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. Vanessa Perkins is a project manager for Blackstone Landscaping, a company based in Providence, Rhode Island, that provides a range of landscaping design, maintenance, and construction services for residential and commercial clients. Vanessa is compiling data on the company's employees and recent projects, and she asks for your help completing the workbook and analyzing the data. Switch to the Employees worksheet, where Vanessa created a formula with the VLOOKUP function to look up employee names by their Employee ID. Other project managers will use this workbook, and she wants to alert them when they enter an incorrect ID number. In cell B4, nest the existing VLOOKUP function in an IFERROR function. If the VLOOKUP function returns an error result, display "Invalid Employee ID" as the error text.
2. Vanessa needs to complete the Employees table in the range A7:I27. First, determine whether each employee can perform Property Maintenance services for the company's clients. In cell G8, enter a formula using the IF function that uses a structured reference to the Years column ([@Years]) to determine if the value is greater than 1. The formula returns the text Yes if true and No if false. Fill the formula into the range G9:G27, if necessary.
3. Employees who can provide Landscaping Construction services must have at least 3 years of experience or an average evaluation rating from Blackstone managers of 7 or more. In cell H8, enter a formula using the IF and OR functions to determine whether the value in the Years column is greater than or equal to 3 OR whether the value in the Avg Eval column is greater than or equal to 7. Use a structured reference to both columns ([@Years] and [@[Avg Eval]]). The formula returns the text Yes if an employee meets one or both of the criteria, and it returns No if an employee meets neither criteria. Fill the formula into the range H9:H27, if necessary.
4. Employees can take Garden Design projects if they have at least 4 years of experience and a customer score of at least 7. In cell I8, enter a formula using the IF and AND functions to determine whether the value in the Years column is greater than or equal to 4 AND whether the value in the Avg Score column is greater than or equal to 7. Use a structured reference to both columns ([@Years] and [@[Avg Score]]). The formula returns the text Yes if an employee meets both criteria or the text No if an employee meets none or only one of the criteria. Fill the formula into the range I9:I27, if necessary.
5. Switch to the Landscaping PivotTable worksheet. It contains the LandscapingProjects PivotTable, which is based on the Projects table on the Landscaping Projects worksheet. Display the PivotTable Field List, and then remove the Average of Total Projects field from the Values area. Move the Region field so that it appears as the second field in the Rows area to make the PivotTable easier to interpret.
6. Vanessa wants another way to compare the landscaping projects by building type, but not by region. Collapse the outline in the LandscapingProjects PivotTable to display the Building Type names and to hide the Regions. Insert a PivotChart based on the LandscapingProjects PivotTable using the Clustered Column chart type. Resize and reposition the PivotChart so that the upper-left corner is located within cell A12 and the lower-right corner is located within cell F25. Change the PivotChart colors to Monochromatic Palette 12 to coordinate with the PivotTable.
7. Vanessa needs to concentrate on Property Maintenance and Landscaping Construction projects. Use the Service slicer to filter the PivotTable and PivotChart to display only Property Maintenance and Landscaping Construction projects.
8. Switch to the Recreation & Hospitality worksheet, which includes the RecreationHospitality table listing project data for Recreation and Hospitality projects, such as hotels, community centers, and parks. Vanessa wants to display statistics in cells B15 and B17. In cell B15, use the INDEX function to display the value in the first row and first column of the RecreationHospitality table.
9. In cell B17, use the SUMIF function and structured references to display the total number of projects completed in each region with the Garden Design service.
10. Vanessa wants to compare the Recreation & Hospitality data for July by service provided. She asks you to create a PivotTable to better manipulate and filter the data. On a new worksheet, create a recommended PivotTable based on the RecreationHospitality table that shows the Sum of July by Service. [Mac Hint: Insert a new PivotTable on a new worksheet, adding the Service field to the Rows area and the July field to the Values area.] Use Recreation & Hospitality Pivot for the name of the worksheet containing this PivotTable. Apply Light Green, Pivot Style Medium 13 to match the style of the other tables in the workbook.
11. Vanessa asks you to customize the new PivotTable to show more details and to provide a filter. Add the Total Projects field to the Values area below the Sum of July field. Add the Region field to the Rows area below the Service field. Add the Project Type field to the Filters area. Change the display of subtotals to Show all Subtotals at Bottom of Group.
12. Vanessa also wants to focus on data for new projects only and to display the records for Garden Design. Filter the PivotTable to show only records with a New project type. Drill down into cell C7 to show all records for Garden Design projects with a New project type on a new worksheet. Use New Garden Design Projects as the name of the new worksheet. Apply Dark Green, Table Style Medium 6 to the table.
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: Employees Worksheet
Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 2: Landscaping PivotTable Worksheet
Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 3: Recreation & Hospitality Worksheet
Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 4: New Garden Design Projects Worksheet
Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 5: Recreation & Hospitality Pivot Worksheet
Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.