← Back to ISMG-2050 Course Hub

Practice 2: Functions, AutoFill, Formatting & Sheet Management

Builds upon Practice 1 completed workbook — 100 Points

File Requirements

Input: Your completed [your initials]Practice01.xlsx from Practice 1 (Enable Editing if Protected View opens).

Output: Save the working file as [your initials]Practice02.xlsx.

Step-by-Step Instructions

Step 1: Open and Save As

  1. Open [your initials]Practice01.xlsx. Click Enable Editing if Protected View opens.
  2. Save As → [your initials]Practice02.xlsx.
  3. Click Save.

Step 2: Calculate Totals Using SUM

  1. Select cell H5, click the AutoSum button (Home tab → Editing group). Press Enter to accept =SUM(D5:G5). Result is 31.
  2. Type Total in cell H4 and press Enter. Center alignment applies automatically.
  3. Select cell D19, click AutoSum → accepts =SUM(D5:D18). Result is 63.

Step 3: Calculate Average Rental Days by Month

  1. Select cell D20, click the AutoSum dropdown → Average.
  2. In the formula bar, change the range from D5:D19 to D5:D18 (exclude the total row).
  3. Press Enter. Result is 4.5.

Step 4: Copy Functions Using the Fill Handle

  1. Select H5, double-click the Fill Handle (lower-right square). Formula fills down through H19.
  2. Select range D19:D20, drag Fill Handle across to column H (D19:H20).
  3. Verify relative references: inspect the formula bar for E19 (SUM adjusted) and E20 (AVERAGE adjusted).

Step 5: Apply Number and Label Formatting

  1. Select D20:H20, set Number Format to Number (2 decimal places).
  2. Select header range A4:H4 → Bold.
  3. Select A1:A2, increase Font Size to 16 pt.
  4. Edit cell A2 text to "First Period" (replacing "First Quarter").

Step 6: Adjust Column Widths

  1. Drag across column headings B and C. Double-click the boundary between C and D to AutoFit.
  2. Drag across columns D through H, then click Format (Home → Cells) → Column Width...
  3. Type 7 and confirm. All selected columns set to width 7.

Step 7: Adjust Row Heights

  1. Select row heading 4, hold Ctrl, select rows 19 and 20.
  2. Right-click → Row Height... → set to 24.

Step 8: Center Labels Across Selection

  1. Select range A1:H2, open Alignment Settings (Home → Alignment launcher).
  2. Set Horizontal alignment to Center Across Selection.
  3. Confirm.

Step 9: Insert a Record and Enter Data

  1. Right-click row heading 13 → Insert. Existing rows shift down.
  2. Enter property record on row 13:
    • A13: "Our Weekend Cottage"
    • B13: "Walker"
    • C13: Pick from dropdown → select MN
    • D–G13: Enter monthly rental values (Tab-separated)
  3. Verify cell H13 auto-evaluates the total via copied relative formulas.

Step 10: Apply Borders, Fill Colors, and Alignment

  1. Select A4:H21, set All Borders.
  2. Select header A4:H4, apply Fill Color → Blue, Accent 1, Lighter 80% (Column 5, Row 2).
  3. With A4 selected, click Format Painter, then drag across A20:H21.
  4. Select A20:A21 → Align Right.
  5. Select D20:H21 → Align Right.
  6. Select D21:H21, enforce consistent two-decimal formatting via Increase/Decrease Decimal buttons.

Step 11: Configure Sheet Name and Tab Color

  1. Double-click the Sheet1 tab → rename to "Rental Days".
  2. Right-click "Rental Days" tab → Tab Color → Blue, Accent 1 (Column 5, Row 1).
  3. Select cell A1.

Step 12: Duplicate and Clear Data for Next Period

  1. Right-click "Rental Days" tab → Move or Copy...
    • Before sheet: (move to end)
    • Check Create a copy
    • Confirm
  2. Rename the duplicated tab to "Next Period".
  3. Right-click "Next Period" tab → Tab Color → Gold, Accent 4 (Column 8, Row 1).
  4. Select D5:G19 on the new sheet → press Delete. Dependent formulas will display #DIV/0! or 0.
  5. Update styling accents on "Next Period":
    • A4:H4 → Fill Color: Gold, Accent 4, Lighter 80%
    • A20:H21 → Fill Color: Gold, Accent 4, Lighter 80%
  6. Press Ctrl+Home to reset view.

Step 13: Finalize and Save

  1. Switch back to the "Rental Days" sheet tab, press Ctrl+Home.
  2. Save workbook as [your initials]Practice02.xlsx.
  3. Submit via Canvas assignment portal.

Key Skills Practiced

  • SUM and AVERAGE via AutoSum tool
  • Fill Handle formula replication (relative references)
  • Column width AutoFit vs. manual sizing
  • Row height adjustment (multi-row selection)
  • Center Across Selection (vs. Center alignment)
  • Sheet duplication, tab naming, and tab color assignment
  • #DIV/0! error behavior with empty cell references
  • Borders, fill colors, Format Painter, and numeric formatting