Comprehensive Project: Animal Life Donations

Overview

Animal Life, NFP, maintains donation data in an Excel workbook. In this comprehensive project, you create a workbook from scratch for the current month, enter and format category data, apply AutoSum and statistical functions, set page layout options across grouped sheets, and duplicate and reset the sheet for the upcoming month.

Step-by-Step Instructions

1. Create and Save Workbook

1. Create a new Blank workbook in Microsoft Excel.
2. Save the workbook as [your initials] AnimalLife.xlsx.

2. Rename Sheet & Set Tab Color

1. Double-click the Sheet1 tab, type April, and press Enter.
2. Right-click the April tab → select Tab Color → choose Dark-Blue, Text 2 (Column 4).

3. Enter Header Labels and Initial Data

1. In cell A1, type Animal Life NFP.
2. In cell A2, type Donations by Category.
3. In cell A4, type Source.
4. Enter row labels in A5:A9: Web site, Mail-in, Visitor, Telemarketing, and Corporate.
5. In cell A11, type Total.
6. Enter initial donation counts across B5:E9:
   • Web site (B5:E5): 50, 52, 150, 105
   • Mail-in (B6:E6): 125, 82, 145, 48
   • Visitor (B7:E7): 25, 28, 145, 50
   • Telemarketing (B8:E8): 75, 74, 99, 70
   • Corporate (B9:E9): 100, 88, 137, 106

4. Use Fill Handle for Series Labels

1. Select cell B4, type Week 1, and press Enter.
2. Select B4, point to its Fill Handle, and drag across to cell E4 (generates Week 2, Week 3, Week 4).
3. In cell F4, type Total.

5. Insert AutoSum Functions

1. Select cell F5 and double-click AutoSum (Home tab → Editing group) to sum B5:E5 (Result: 357).
2. Select cell B11 and click AutoSum.
3. Correct the suggested argument range from B5:B10 to B5:B9 and press Enter (Result: 375).

6. Copy Formulas with AutoFill

1. Select cell F5 and double-click the Fill Handle to copy the formula down through row 9.
2. Select cell B11 and drag the Fill Handle across to cell F11 to calculate all weekly column totals and the grand total (1,754 in F11).

7. Calculate Maximum Donation

1. In cell A12, type Biggest Donation.
2. Select cell B12, click the AutoSum dropdown arrow, and choose Max.
3. Drag across range B5:E9 to set formula =MAX(B5:E9) and press Enter (Result: 150).

8. Apply Number Formatting

1. Select cells B5:F9, hold Ctrl, select B11:F11, and select B12.
2. Click Accounting Number Format ($) on the Home tab → Number group.
3. Click Decrease Decimal two times to show zero decimal positions.

9. Alignment & Column Widths

1. Select cells A1:F2 → click the Alignment launcher → set Horizontal to Center Across Selection → OK.
2. Select cells A4:F4 and click Center.
3. Select column headings B:F, right-click → Column Width... → set to 10.
4. Double-click the boundary between column headers A and B to AutoFit column A.

10. Adjust Row Heights & Font Sizes

1. Select cells A1:A2 and change Font Size to 16 pt.
2. Select row headings 1:2, right-click → Row Height... → set to 22.

11. Apply Cell Styles & Borders

1. Select cells A4:F4 → open Cell Styles → apply Heading 3.
2. Select cells A11:F11 → open Cell Styles → apply Total.
3. Right-click row heading 5 and select Insert (inserts a blank row 5).
4. Select cells A6:F10 → open the Borders menu → choose All Borders.
5. Select cell A1.

12. Duplicate and Reset for May

1. Right-click the April tab → select Move or Copy... → select (move to end) → check Create a copy → OK.
2. Rename the duplicated tab to May.
3. Set the May tab color to Green, Accent 6 (Column 10).
4. Select data cells B6:E10 and press Delete. (Formulas will display formatted zero dashes $ -).
5. Press Ctrl+Home.

13. Group Sheets & Page Setup

1. Hold Ctrl and click the April tab to group both sheets.
2. Go to Page Layout tab → Page Setup launcher:
   • Page tab: Select Landscape orientation.
   • Margins tab: Check Horizontally under Center on page.
   • Header/Footer tab: Select workbook name in Header; select sheet name in Footer.
3. Click OK. Right-click the May tab and choose Ungroup Sheets.

14. Zoom & Freeze Panes

1. Click the April tab and select cell B6.
2. Click Zoom In on the Status bar five times (or set zoom to 150%).
3. Go to View tab → Window group → Freeze Panes → Freeze Panes.
4. Scroll horizontally so column F sits directly adjacent to column A.
5. Select cell A1.

15. Save and Submit

1. Save your workbook as [your initials] AnimalLife.xlsx.
2. Submit the completed file via Canvas.