Week 4 Assignment Guide

Open Microsoft Excel and create a new blank workbook.

• Right-click the Sheet1 tab at the bottom and rename it to "Donor Data"
• In Row 1, type your 8 column headers across cells A1 through H1:
  1. Donor ID
  2. First Name
  3. Last Name
  4. Age
  5. Blood Type
  6. Time Slot
  7. Status
  8. Units Collected

You need 42 Donor IDs in column A, from BD-001 to BD-042.

• Type BD-001 in cell A2
• Type BD-002 in cell A3
• Select both cells (A2:A3), then use the fill handle (the small square at the bottom-right corner of the selection) — drag it down to row 43
• Excel should recognize the pattern and auto-fill BD-003, BD-004, and so on through BD-042

You need to create realistic data for all 42 donors. Here are the rules to follow:

• First Name & Last Name: Make up realistic names — use a variety
• Age: Range from 17 to 65+. Include at least 1–2 donors under 18 (you will need them for the IF formula in Task 3)
• Blood Type: Use all 8 types (A+, A−, B+, B−, AB+, AB−, O+, O−). Distribute them realistically — O+ and A+ are the most common
• Time Slot: Morning (9–11), Midday (11–1), or Afternoon (1–3). Spread donors across all three
• Status: 38 donors are "Successful" and exactly 4 are "Deferred". Spread the deferred donors across different time slots
• Units Collected: Enter 1 for successful donors, 0 for deferred donors

Data Validation restricts what values can be entered in a cell. You need it on two columns:

For the Blood Type column:

1. Select the entire Blood Type data range (E2:E43)
2. Go to the Data tab on the ribbon
3. Click Data Validation
4. Under "Allow," choose List
5. In the "Source" field, type the 8 blood types separated by commas: A+,A-,B+,B-,AB+,AB-,O+,O-
6. Click OK

For the Status column: Repeat the same process on the Status data range, but use Successful,Deferred as your list source.

Select the entire header row (A1:H1) and apply these formats:

• Bold the text (Ctrl+B)
• Apply a fill color — choose a professional color like dark blue, navy, or teal from the paint bucket icon on the Home tab
• If you use a dark fill, change the font color to white so the text is readable
• Add borders — select the header cells, click the Borders dropdown on the Home tab, and choose "All Borders" or "Bottom Border"

Make sure your data displays correctly:

• Age column: Should display as whole numbers (no decimals). Select the Age cells, right-click, choose Format Cells, then Number with 0 decimal places
• Units Collected: Same — whole numbers (0 or 1)
• Column widths: Double-click the border between column headers (the letters at the top) to auto-fit, or drag to manually adjust. No text should be cut off or truncated

You need to make deferred donor rows visually stand out with a light red highlight:

1. Select the entire data range (A2:H43)
2. Go to Home tab → Conditional Formatting → New Rule
3. Choose "Use a formula to determine which cells to format"
4. Think about which column contains the Status value and write a formula that checks if that cell equals "Deferred." Remember: when you write the formula, the cell reference for the Status column should use a $ sign on the column letter (absolute column reference) but NOT on the row number, so the rule applies correctly to every row
5. Click Format, go to the Fill tab, and pick a light red color
6. Click OK twice to apply

Freezing the header row keeps it visible while you scroll through 42 rows of data:

• Click on cell A2 (the first data cell, just below the headers)
• Go to View tab → Freeze Panes → Freeze Top Row
• Scroll down to verify — row 1 should stay pinned at the top

If it did not freeze correctly, go back to View → Freeze Panes → Unfreeze Panes and try again.

Decide where to put your summary. You have two good options:

• Option A: Below the data on the same sheet — skip a row after row 43, then start your labels in column A and formulas in column B
• Option B: On a new sheet — right-click the sheet tab, choose "Insert," and name the new sheet "Summary"

Either way, set up a clean layout with labels in one column (describing what the formula calculates) and formulas in the next column. For example:

• Row: "Total Units Collected" → [formula here]
• Row: "Average Donor Age" → [formula here]
• And so on for each required calculation

Start with the straightforward aggregate formulas. For each one, think about:

• Which column contains the data you need to calculate?
• What is the range? Your data starts in row 2 and ends in row 43

You need these calculations:

1. Total units collected — Which function adds up a range of numbers? Apply it to the Units Collected column
2. Average donor age — Which function calculates the mean? Apply it to the Age column
3. Youngest donor — Which function finds the smallest value? Apply it to the Age column
4. Oldest donor — Which function finds the largest value? Apply it to the Age column

These two functions work differently — understanding the difference is part of what you are being graded on:

• COUNT counts cells that contain numbers only
• COUNTA counts cells that contain any non-empty value (text or numbers)

Think about which columns contain numbers and which contain text. Apply each function to an appropriate column:

• Use COUNT on a numeric column (like Age or Units Collected)
• Use COUNTA on a text column (like First Name, Blood Type, or Status)

Label each one clearly so your instructor knows you understand the difference.

You need 8 separate COUNTIF formulas — one for each blood type. Think about what COUNTIF needs:

• First argument: The range to search (the Blood Type column)
• Second argument: The value to count (the specific blood type, in quotes)

Set up a clean layout, for example:

• A+ count → [formula]
• A− count → [formula]
• B+ count → [formula]
• ... and so on for all 8 types

The IF function checks a condition and returns one value if true and another if false. You need it to flag donors under 18.

Think about how IF works:

• First argument (test): A comparison that checks if the age is below 18
• Second argument (if true): The text "Minor — verify consent"
• Third argument (if false): Leave it blank or use "" (empty text)

Where should you put this formula?

• You could add a new column (column I) next to the data with a header like "Consent Flag"
• Write the formula in the first data row, then copy it down to all 42 rows using the fill handle

A multi-level sort organizes data by one column first, then by a second column within each group:

1. Click anywhere inside your data
2. Go to Data tab → Sort (the button with the A-Z icon)
3. In the Sort dialog, set the first level:
   • "Sort by" → Blood Type
   • "Order" → A to Z
4. Click Add Level to add a second sort:
   • "Then by" → Last Name
   • "Order" → A to Z
5. Make sure "My data has headers" is checked at the top
6. Click OK

After sorting, your data should be grouped by blood type (A−, A+, AB−, AB+, B−, B+, O−, O+), and within each group, last names should be alphabetical.

AutoFilter adds dropdown arrows to your headers, allowing you to filter the view by any column value:

• Click anywhere inside your data table
• Go to Data tab → click Filter
• You should see small dropdown arrows appear on each header cell in row 1

That is it! AutoFilter is now active. You will use it in the next step.

Now use AutoFilter to show only the O+ donors:

1. Click the dropdown arrow on the Blood Type header
2. Uncheck "Select All" first (this clears all checkboxes)
3. Then check only "O+"
4. Click OK — the spreadsheet should now show only O+ donors, with the other rows hidden

Now capture and paste the screenshot:

1. Press Windows + Shift + S to open the Snipping Tool
2. Select the area showing your filtered data (make sure the headers and all O+ rows are visible)
3. Create a new sheet — right-click a sheet tab → Insert → name it "Filtered View"
4. Click on cell A1 in the new sheet and press Ctrl+V to paste the screenshot

The print area tells Excel exactly which cells to print:

• Select the range that includes your headers, all 42 data rows, and the summary section
• Go to Page Layout tab → Print Area → Set Print Area

This prevents Excel from printing blank columns or rows outside your data.

With 8+ columns, your data is wider than it is tall. Landscape orientation is the right choice:

• Go to Page Layout tab → Orientation → Landscape
• For scaling: Page Layout tab → in the "Scale to Fit" group, set Width to 1 page
• Leave Height as "Automatic" — let Excel use as many pages as needed vertically

Headers and footers print at the top and bottom of every page:

1. Go to Page Layout tab → click the small arrow in the bottom-right corner of the Page Setup group to open the dialog
2. Click the Header/Footer tab
3. Click Custom Header — in the center section, type: Sunnydale Blood Drive — March 15, 2026
4. Click OK, then click Custom Footer
5. For page numbers in the footer, click in the center section and use the Insert Page Number button followed by text like "of" and the Insert Number of Pages button. This creates the "Page 1 of 2" format automatically
6. Click OK twice to close

Before saving, verify every requirement is met:

• 42 donor records with all 8 columns filled in — no blank cells
• Exactly 4 deferred donors with 0 units
• Data Validation dropdowns appear on Blood Type and Status columns
• Headers are bold with fill color and borders
• Conditional formatting highlights deferred rows in light red
• Header row is frozen (scroll down to test)
• All formulas are live — click on each formula cell and verify the formula bar shows a formula, not a plain number
• All 8 blood type COUNTIF results are present
• IF formula flags minors correctly
• Data is sorted by Blood Type, then Last Name
• AutoFilter dropdowns visible on headers
• "Filtered View" sheet has the O+ screenshot
• Print settings: landscape, 1 page wide, header text, page numbers in footer

Save and submit your completed workbook:

1. Press Ctrl+S to save
2. Name the file LastName_Week4_BloodDrive.xlsx
3. Make sure the file format is .xlsx (Excel Workbook), not .csv or .xls
4. Upload through the LMS before the deadline