Illustrated Excel 365 | Module 2: SAM Project B The Caring Foundation

GETTING STARTED

1. Save the file IL_EX365_2B_FirstLastName_1.xlsx as IL_EX365_2B_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 IL_EX365_2B_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. Josiah Maylor is the director of fundraising for The Caring Foundation, a not-for profit agency that funds a variety of support services for children and families in Cheyenne, Wyoming. He is using an Excel workbook to track the hours the foundation's volunteers complete and pro bono hours the foundation's employees complete for fundraising events. He has asked you to format his workbook to make the information clearer and easier to understand. Go to the Volunteer worksheet. Rename the Volunteer worksheet to Volunteer Hours, and then change the tab color of the worksheet to Teal, Accent 6, Lighter 60% (10th column, 3rd row of the Theme Colors palette) to match the color of the Pro Bono Hours sheet tab.

2. Use AutoFit to resize column A to fit its contents.

3. Change the width of column B to 16.00 so that the date is completely visible in cell B4.

4. Apply the Title cell style to the range A1:E1, and then increase the font size in that range to 23 point to make the worksheet title stand out.

5. Change the font of the range A2:E2 to Calibri Light to match the font of the worksheet title.

6. Apply the Short Date number format to cell B4 to use a more common date format.

7. Merge and center the range A6:E6, increase the font size of that range to 16 point, then bold the cell contents to clarify that the text is a title for the range A7:E16.

8. Center the text in the range B7:E7 and change the font color of the range to White, Background 1 (1st column, 1st row of the Theme Colors palette) to make the column headings easier to read.

9. Use the Format Painter to apply the format from the range A12:E12 to the range A14:E14 to create consistent shading and borders in the worksheet.

10. Italicize the contents of the range A15:A16 and increase the indent of the contents to separate those row headings from the others above them.

11. In the range A15:E16, apply a Thick Bottom Border to the cells, and then change the fill color to White, Background 1, Darker 15% (1st column, 3rd row of the Theme Colors palette) to highlight the Total and Average rows with a softer color.

12. Josiah wants to know at a glance which programs employees volunteered more than 20 hours in Quarter 4. In the range E8:E14, use Conditional Formatting Highlight Cells Rules to format cells whose contents are greater than 20 with Green Fill with Dark Green Text.

13. Apply the Percentage number format to cell B18 to clarify the cell contains a percentage value and then set the number of decimal places shown to two.

14. Josiah would like to see the dollar amount of corporate match funds in a rounded format. Apply the Accounting number format to cell B19 and then decrease the number of decimal places shown to zero. (Hint: Depending on how you complete this action, the number format may appear as Custom.)

15. Hide row 21 because Josiah wants to keep the contents private when showing the worksheet to people outside the company.

16. Check the spelling in the worksheet to identify and correct any spelling errors.

17. In this workbook, Josiah also keeps track of the pro bono consulting hours employees donate to the foundation's major fundraising events. Go to the Pro Bono Hours worksheet. Find and replace all occurrences of the text "Hrs" with: Hours.

18. Change the width of columns B and C to 15.00 to allow more space for the column headings.

19. Change the height of row 3 to 15.00 to decrease the amount of space before the data in the range A4:C12.

20. For the range B5:C12, decrease the number of decimal places shown to zero.

21. In the range C5:C11, use Conditional Formatting to create a Data Bars rule, and use the Gradient Fill Orange Data Bar color option so that Josiah can compare the actual hours donated to each project type more easily.

22. Move the Volunteer Hours worksheet after the Pro Bono Hours worksheet to arrange the worksheets in alphabetic order.

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: Pro Bono Hours Tracker

Final Figure 2: Volunteer Hours Worksheet