Shelly Cashman Excel 365 | Module 7: End of Module Project 2 City of Franklin

GETTING STARTED

1. Save the file SC_EX365_EOM7-2_FirstLastName_1.xlsx as SC_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. To complete this Project, you will also need the following files:

Support_EX365_EOM7-2_Managers.csv

3. With the file SC_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. Noelle Molina is an assistant to the city council of Franklin, a mid-sized city in Maryland. She is preparing a draft of a workbook to send to residents who are interested in participating in local government. She asks for your help in importing data and adding other content to the workbook. Go to the City Managers worksheet. Apply a WordArt style to the "Franklin City Managers" text box using Fill: White; Outline: Tan, Accent color 5; Shadow to make the worksheet title more pronounced. Change the text fill color of the WordArt to Tan, Accent 5 (9th column, 1st row in the Theme Colors palette) to match the format of the title on another worksheet.

2. The worksheet should list information about the city managers, which is contained in a text file. Get data from the file Support_EX365_EOM7-2_Managers.csv. Use the Power Query Editor to transform the data by using the first row as headers. Load the data as a table in the City Managers worksheet beginning in cell H11.

3. Noelle wants you to list the manager information in the range B3:F9. The imported table separates the first and last names, but Noelle asks you to list the full name on the City Managers worksheet. In cell B3, enter a formula using the CONCAT function that displays the first name shown in cell H12 followed by a space (" ") and then the last name shown in cell I12. Fill the range B4:B9 with the formula in cell B3 to list the full names of the remaining managers.

4. Copy the Position data from the range J12:J18 and paste only the values in the range C3:C9 to list the manager's positions.

5. In cell D3, enter a formula using the PROPER function to capitalize the first letter in each word in the Department text in cell K12. Fill the range D4:D9 with the formula in cell D3 to list the names of the remaining departments.

6. In cell E3, enter a formula using the LEFT function to insert the first 2 characters on the left of cell L12 to list the district number for the first manager. Copy the formula in cell E3 to the range E4:E9 to list the district numbers for the other managers.

7. In cell F3, enter a formula using the RIGHT function to insert the last 4 characters on the right of cell M12 to insert the phone extension for the first manager. Copy the formula in cell F3 to the range F4:F9 to list the extensions for the other managers.

8. Hide rows 11 to 18 so that the worksheet does not display duplicated data.

9. Noelle already imported budget summary data on the Budget Summary worksheet but wants you to display the data in the range H3:I6 of the City Managers worksheet. Go to the Budget Summary worksheet. Copy the data in the range A1:D2, and then transpose the rows and columns as you paste the data on the City Managers worksheet starting in cell H3. Resize columns H and I to their best fit to display all the data.

10. Noelle asks you to include a graphic showing how city residents can get involved with local government. Insert the Step Up Process SmartArt from the Process section of the SmartArt gallery. Move and resize the SmartArt so that the upper-left corner is in cell B20 and the lower-right corner is in cell F34.

11. Enter Join a committee in the left shape. Enter Attend meetings in the middle shape. Enter Run for office in the right shape.

12. Change the colors of the SmartArt to Colored Fill – Accent 5 to coordinate with the rest of the worksheet.

13. Noelle also asks you to improve the appearance and accessibility of the picture on the worksheet. Add City managers by city hall as alt text to provide a description of the picture to screen readers.

14. Add a border to the picture using Tan, Accent 5, Darker 50% (9th column, 6th row of the Theme Colors palette) as the border color to match other colors on the worksheet. Apply the Offset: Top Left picture effect from the Outer section of the Shadow gallery to create the appearance of depth. Apply the Sharpen: 25% correction to the picture to make the subjects stand out.

15. Go to the Projects worksheet, which Noelle asks you to finish. Convert the data in the range B4:B11 to columns using a comma as the delimiter and a General column data format. Replace data to list the city staff data in column C.

16. Use the Quick Analysis tool to insert the total number of staff from the range E4:E11. [Mac Hint: Use the AutoSum button.]

17. Insert a 2D Clustered Bar chart to compare the Total Staff and Budgeted data for each project (the nonadjacent ranges B3:B11 and E3:F11). Move and resize the bar chart so that the upper-left corner is in cell B14 and the lower-right corner is in cell G34.

18. Use Total and Budgeted Staff as the chart title. Change the colors to Monochromatic Palette 5 to coordinate with the rest of the worksheet. Remove the legend and then add a data table to the chart to identify the values. [Mac Hint: Data Table with Legend Keys.]

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: City Managers Worksheet

Final Figure 2: Budget Summary Worksheet

Final Figure 3: Projects Worksheet