Shelly Cashman Excel 365 | Module 2: End of Module Project 1 Front Street Marketing
Format worksheets
· Excel Projects Help
GETTING STARTED
1. Save the file SC_EX365_EOM2-1_FirstLastName_1.xlsx as SC_EX365_EOM2-1_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 SC_EX365_EOM2-1_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. June Kobalis is the senior director of projects for Front Street Marketing in Aneheim, California. The company writes content for clients' marketing materials. June has started to create an Excel workbook to track estimated and actual hours and billing amounts for each project. She asks you to format the workbook to make the information clearer and easier to interpret. Go to the Employees for Projects worksheet. Change the worksheet theme to Facet.
2. In cell A1, increase the font size to 20 point and change the fill color to Dark Green, Accent 2, Lighter 80%.
3. Center the values in the range B3:B9.
4. Add All Borders to the range A1:C9.
5. Change the color of the Project Tracking worksheet tab to Green, Accent 1.
6. Go to the Project Tracking worksheet. Change the date format of cell B3 to Short Date.
7. Display the values in the range E5:E14 with one decimal place.
8. In cell F5, insert a formula without using a function that multiplies Amirah Kamish's estimated hours (cell E5) and his pay rate (cell D5). Fill the range F6:F14 with the formula in cell F5.
9. Resize column F to its best fit.
10. Apply the Currency number format to the range F5:F14 using a dollar sign ($) and two decimal places.
11. Display the values in the range K5:K14 as percentages with a percent (%) sign and one decimal place. Use Conditional Formatting Highlight Cells Rules to format cells containing a value greater than 5% with Light Red Fill with Dark Red Text.
12. In the range H5:H14, use Conditional Formatting to create a Data Bars rule with the Gradient Fill Orange Data Bar color option.
13. In cell G15, insert a formula using the AVERAGE function that calculates the average amount of actual hours worked (range G5:G14).
14. In cell G16, insert a formula using the MIN function that calculates the minimum amount of actual hours worked (range G5:G14).
15. In cell G17, insert a formula using the MAX function that calculates the maximum amount of actual hours worked (range G5:G14).
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 for Projects
Final Figure 2: Project Tracking