The blood drive was a SUCCESS! Thanks to your flyer and the staff training you prepared, the event ran smoothly. Now Dr. Martinez has asked you to organize all the data in Excel so the clinic can analyze the results and report back to the American Red Cross.
Your Tasks
Create an Excel workbook that tracks, formats, and analyzes the blood drive donor data. Complete all five tasks below.
Create a formatted spreadsheet tracking all 42 donors with these 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
Important: Of the 42 donors, exactly 4 were deferred (did not meet eligibility). Use Data Validation for the Blood Type and Status columns to restrict entries to valid values only.
You will need to make up realistic names and data for the 42 donors. Ensure a mix of blood types, ages (ranging from 17 to 65+), and time slots. Distribute the 4 deferred donors across different time slots.
Apply professional formatting to your spreadsheet:
- Number formatting: Dates as dates, ages as whole numbers
- Headers: Bold text, fill color, borders applied
- 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 (on the same sheet below the data, or on a separate sheet), calculate the following using Excel formulas:
- =SUM — Total units collected
- =AVERAGE — Average donor age
- =COUNT and =COUNTA — Count numeric vs. text entries as appropriate
- =COUNTIF — Count of each blood type (8 blood types total)
- =IF — Flag donors under 18 as "Minor — verify consent"
- =MIN and =MAX — Youngest and oldest donor ages
Make sure your formulas reference the actual data cells (e.g., =SUM(H2:H43)), not hard-coded numbers. Your instructor will check that the formulas are live, not just typed values.
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 (paste it on a separate sheet labeled "Filtered View")
Prepare the spreadsheet for professional printing:
- Print area: Set to include the donor data and summary
- Orientation: Landscape
- Scaling: Fit to 1 page wide
- Header: "Sunnydale Blood Drive — March 15, 2026"
- Footer: Page numbers (Page 1 of ?)
Step-by-Step Instructions
Follow these steps to build your workbook:
- Open Excel and create a new blank workbook. Rename Sheet1 to "Donor Data".
- Create headers in Row 1: Donor ID, First Name, Last Name, Age, Blood Type, Time Slot, Status, Units Collected.
- Enter data for all 42 donors. Make 4 of them "Deferred" with 0 units collected.
- Apply Data Validation to the Blood Type column (Data > Validation > List > enter the 8 types). Do the same for Status (Successful, Deferred).
- Format headers with bold, fill color, and borders. Adjust column widths.
- Apply Conditional Formatting to highlight rows where Status = "Deferred" in light red.
- Freeze the top row (View > Freeze Panes > Freeze Top Row).
- Create a Summary section below the data (or on a new sheet) with all required formulas.
- Sort by Blood Type, then Last Name. Apply AutoFilter to all columns.
- Filter for O+ donors, take a screenshot, and paste on a sheet labeled "Filtered View".
- Set up Print settings: landscape, fit to 1 page wide, header and footer text.
- Save as LastName_Week4_BloodDrive.xlsx.
Requirements Checklist
Before submitting, verify your workbook meets all requirements:
- 42 donor records entered with all 8 columns
- 4 donors marked as Deferred with 0 units collected
- Data Validation applied to Blood Type and Status columns
- Headers formatted with bold, fill color, and borders
- Conditional formatting highlights deferred donors in light red
- Header row is frozen
- Column widths adjusted appropriately
- =SUM formula calculates total units collected
- =AVERAGE formula calculates average donor age
- =COUNT or =COUNTA used appropriately
- =COUNTIF formulas count each of the 8 blood types
- =IF formula flags donors under 18
- =MIN and =MAX formulas show youngest and oldest ages
- Data sorted by Blood Type, then Last Name
- AutoFilter applied to all columns
- Filtered view screenshot included on separate sheet
- Print setup complete: landscape, fit to 1 page wide, header and footer
Grading Rubric
| Criteria | Description | Points |
|---|---|---|
| Donor Data Sheet | 42 records with all columns, realistic data, data validation on Blood Type and Status | 10 |
| Formatting | Number formatting, formatted headers, conditional formatting for deferred donors, frozen row, proper column widths | 10 |
| Formulas & Analysis | SUM, AVERAGE, COUNT/COUNTA, COUNTIF (all 8 types), IF (minor flag), MIN, MAX — all using live formulas | 15 |
| Sorting & Filtering | Multi-level sort applied, AutoFilter enabled, filtered view screenshot on separate sheet | 10 |
| Print Setup | Print area set, landscape orientation, fit to 1 page wide, proper header and footer | 5 |
| Total | 50 | |
Submission Instructions
Submit your completed workbook through the course LMS:
- Format: Excel file (.xlsx)
- File name: LastName_Week4_BloodDrive.xlsx
- Deadline: End of Week 4 (check your course calendar for exact date)
Keep your formulas live — do not paste values over them. Your instructor will check formula references.
Need Help?
Resources to guide you through this assignment:
Save this workbook carefully! In Week 5, you will use this same data to create charts, a dashboard, and an executive summary for Dr. Martinez's board presentation.