Collecting Data from the Blood Drive

Dr. Elena Martinez, Clinic Director at Sunnydale Family Health Clinic, has great news: the blood drive was a SUCCESS!

Dr. Martinez needs you to organize all the donor data in Microsoft Excel so the clinic can analyze the results and report back to the American Red Cross.

Create a formatted spreadsheet tracking all 42 donors with these 8 columns:

• Donor ID: BD-001 through BD-042
• First Name
• Last Name
• Age
• Blood Type: A+, A−, B+, B−, AB+, AB−, O+, O−
• Time Slot: Morning (9–11), Midday (11–1), Afternoon (1–3)
• Status: Successful or Deferred
• Units Collected: 1 or 0

Of the 42 donors, exactly 4 were deferred (did not meet eligibility). Use Data Validation on the Blood Type and Status columns to restrict entries to valid values only.

Apply professional formatting to your spreadsheet:

• Number formatting: Dates as dates, ages as whole numbers
• Headers: Bold text with fill color and borders
• Conditional formatting: Highlight deferred donors in light red
• Freeze panes: Freeze the header row so it stays visible when scrolling
• Column widths: Adjusted to fit content neatly (no truncated text)

In a Summary section (below the data or on a separate sheet), calculate the following using live Excel formulas:

• =SUM — Total units collected
• =AVERAGE — Average donor age
• =COUNT and =COUNTA — Count numeric vs. text entries
• =COUNTIF — Count of each blood type (all 8 types)
• =IF — Flag donors under 18 as "Minor — verify consent"
• =MIN and =MAX — Youngest and oldest donor ages

Demonstrate your ability to organize data:

• Sort the data by Blood Type (A–Z), then by Last Name (A–Z) as a secondary sort
• Apply AutoFilter to all columns (Data > Filter)
• Take a screenshot of a filtered view showing only O+ donors and paste it on a separate sheet labeled "Filtered View"

Prepare the spreadsheet for professional printing:

• Orientation: Landscape
• Scaling: Fit to 1 page wide
• Header: "Sunnydale Blood Drive — March 15, 2026"
• Footer: Page numbers (Page 1 of ?)

1. Open Excel and create a new blank workbook. Rename Sheet1 to "Donor Data".
2. Create headers in Row 1: Donor ID, First Name, Last Name, Age, Blood Type, Time Slot, Status, Units Collected.
3. Enter data for all 42 donors. Make 4 of them "Deferred" with 0 units collected.
4. Apply Data Validation to the Blood Type column (Data > Validation > List > enter the 8 types). Do the same for Status (Successful, Deferred).
5. Format headers with bold, fill color, and borders. Adjust column widths.
6. Apply Conditional Formatting to highlight rows where Status = "Deferred" in light red.
7. Freeze the top row (View > Freeze Panes > Freeze Top Row).
8. Create a Summary section below the data (or on a new sheet) with all required formulas.
9. Sort by Blood Type, then Last Name. Apply AutoFilter to all columns.
10. Filter for O+ donors, take a screenshot, and paste on a sheet labeled "Filtered View".
11. Set up Print settings: landscape, fit to 1 page wide, header and footer text.
12. Save as LastName_Week4_BloodDrive.xlsx.