Illustrated Excel 365 | Module 2: SAM Project A Powell & Khan Consultants

GETTING STARTED

1. Save the file IL_EX365_2A_FirstLastName_1.xlsx as IL_EX365_2A_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_2A_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. Darsh Pandey is the director of Corporate Social Responsibility at Powell & Khan Consultants in Denver, Colorado. He is using an Excel workbook to track the volunteer and pro bono consulting hours employees provide to nonprofit organizations. 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 Green, Accent 6, Lighter 60% (10th column, 3rd row of the Theme Colors palette) to match the color of the Pro Bono Consulting sheet tab.

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

3. Change the width of column B to 15.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 22 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 14 point, then bold the cell contents to clarify that the text is a title for the range A7:E17.

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 A16:A17 and increase the indent of the contents to separate those row headings from the others above them.

11. In the range A16:E17, apply a Bottom Double Border to the cells, and then change the fill color to Light Gray, Background 2 (3rd column, 1st row of the Theme Colors palette) to highlight the Total and Average rows with a softer color.

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

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

14. Darsh would like to see the dollar amount of corporate match funds in a rounded format. Apply the Accounting number format to cell B20 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 Custo)

15. Hide row 22 because Darsh 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, Darsh also keeps track of the pro bono consulting hours employees donate to nonprofit organizations. Go to the Pro Bono Consulting worksheet. Find and replace all occurrences of the text "Hrs" with: Hours

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

19. Change the height of row 3 to 20.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 Green Data Bar color option so that Darsh can compare the actual hours donated to each project type more easily.

22. Move the Volunteer Hours workheet after the Pro Bono Consulting 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 Consulting Worksheet

Final Figure 2: Volunteer Hours Worksheet