New Perspectives Excel 365 | Module 4: SAM Project B Pacific Crest College

GETTING STARTED

1. Save the file NP_EX365_4B_FirstLastName_1.xlsx as NP_EX365_4B_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_4B_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. Jalinda Larsen is an administrator at Pacific Crest College in Redding, California. She is compiling data about student enrollment for the college's Marketing Department and wants to use Excel to create charts to illustrate the data and emphasize trends. Go to the Enrollment worksheet. This worksheet shows the number of students enrolled in programs in five areas of study over four years. Jalinda wants to compare the number of students enrolled in 2028 in each area of study. In the range F5:F9, add Conditional Formatting to show Solid Fill Orange Data Bars.

2. Jalinda also wants to provide a general impression of the annual trend for each area of study and for the total students enrolled over four years. In the range G5:G10, add Line sparklines based on the data in the range C5:F10.

3. Format the sparklines as follows to make them more prominent:

a. Apply the Orange, Accent 6, Darker 50% (10th column, 6th row in the Theme Colors palette) sparkline color to the range G5:G10.

b. Show the High Point and Low Point data markers.

4. Jalinda has created a pie chart representing the percentage of students enrolled in each area of study in 2028. She wants to allow more room for the plot area of the chart. Modify the chart in the range I2:O17 as follows:

a. Change the position of the data labels to Inside End.

b. Move the legend to the Right of the plot area.

5. Jalinda wants a similar pie chart showing the percentage of students enrolled in each area of study in 2027. Create the chart as follows:

a. Create a 2-D Pie chart based on the data in the range E5:E9 and using the category labels in the range B5:B9.

b. Resize and reposition the chart so that the upper-left corner is located within cell I19, and the lower-right corner is located within cell O34 to show that the two pie charts are related.

6. Customize the pie chart you just added as follows to match the appearance of the Enrollment by Area of Study: 2028 pie chart:

a. Enter Enrollment by Area of Study: 2027 as the chart title.

b. Apply Style 4 to the chart.

c. Change the colors to Monochromatic Palette 6.

d. Change the position of the data labels to Inside End.

e. Change the data label content to show a Percentage rather than a value.

f. Move the legend to the Right of the plot area.

7. Jalinda wants to insert a column chart that combines the enrollment data in a single column for each area of study. Create the chart as follows:

a. Create a Stacked Column chart based on the data in the range B5:F9. Add each series name (2025, 2026, etc.) based on the labels in cells C4:F4.

b. Resize and reposition the chart so that the upper-left corner is located within cell B12, and the lower-right corner is located within cell G34.

8. Customize the stacked column chart as follows to coordinate with the other charts and to make the data easier to interpret:

a. Enter Enrollment by Area of Study as the chart title.

b. Change the Maximum value to 4,000 on the vertical axis.

c. Change the colors to Monochromatic Palette 6.

9. Jalinda also wants a chart that compares the yearly and total enrollment of the first three areas of study. Create the chart as follows:

a. Create a Clustered Column – Line combination chart based on the data in the nonadjacent range B5:F7, B10:F10. Edit the Horizontal Axis labels to use the range C4:F4.

b. Move the chart to a new worksheet and use Top Areas of Study as the sheet name.

10. Update the combination chart as follows to make it more meaningful:

a. Change the chart type to show the Education, Health & Human Development course data as a Clustered Column chart.

b. Add a secondary axis for the Total series. [Mac Hint: Select the Total series and use the Format Data Series option to add the axis.]

c. Add Primary Vertical and Secondary Vertical axis titles to the chart.

d. Enter Enrollment as the left vertical axis title.

e. Enter Total students enrolled as the right vertical axis title.

f. Enter Top Areas of Study as the chart title.

g. Change the outline color of the line chart to Orange, Accent 6 (10th column, 1st row in the Theme Colors palette).

11. Go to the Marketing worksheet. Jalinda wants to determine how closely spending on marketing is related to the number of students enrolled per month. Create a chart to provide this information as follows:

a. Create a Scatter chart based on the data in the range C4:D16.

b. Change the chart title to Marketing and Students Enrolled to accurately reflect the chart's contents.

c. Resize and reposition the chart so that the upper-left corner is located within cell B19, and the lower-right corner is located within cell D36.

12. Modify the scatter chart to make it easier to interpret as follows:

a. Change the Minimum value to 9,000 on the horizontal axis.

b. Change the Minimum value to 15 on the vertical axis.

c. Add a Linear Trendline to clarify the relationship between marketing expenditures and student enrollment.

13. Jalinda also wants to compare the amount of money spent on marketing each month and call attention to the month with the most spending. Insert a chart that provides this information as follows:

a. Create a Clustered Column chart based on the values in the range B4:C16.

b. Resize and reposition the chart so that the upper-left corner is located within cell F3, and the lower-right corner is located within cell N23.

c. Set the gap width between the columns to 30% to widen the columns.

d. Display a Data Label only for May, the month with the highest marketing expenditures.

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: Top Areas of Study 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: Enrollment 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: Marketing 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.