Dr. Martinez has been invited to present the blood drive results to the Sunnydale Clinic Board of Directors at their quarterly meeting. She needs a data-driven summary showing what happened, how the community responded, and why the clinic should make this an annual event.
She is counting on YOUR Excel visualization skills to turn raw data into a compelling story. Charts, formulas, and a polished dashboard — this is where it all comes together!
Your Tasks
Using the data from your Week 4 spreadsheet, create charts, lookup tools, advanced formulas, and a professional dashboard. Complete all six tasks below.
Open your Week 4 workbook (LastName_Week4_BloodDrive.xlsx) and continue working in the same file. If you did not complete Week 4, you can create the donor data now, but you must have all 42 records to build charts from.
Create a pie chart that shows the distribution of blood types among the 42 donors:
- Chart title: "Donor Blood Type Distribution"
- Show each blood type (A+, A-, B+, B-, AB+, AB-, O+, O-) as a slice
- Include data labels with percentages
- Use a professional color scheme (avoid the default rainbow)
- Move to its own chart sheet OR embed it prominently on the Dashboard sheet
First create a small summary table using COUNTIF for each blood type. Then select that summary table to build your pie chart. Use Chart Design > Add Chart Element > Data Labels > Percentage.
Create a clustered column chart comparing donations across the three time slots:
- Compare Morning (9–11), Midday (11–1), and Afternoon (1–3) sessions
- Show Successful vs. Deferred side-by-side (clustered columns)
- Include axis labels, chart title, and legend
- Professional formatting with clear, readable values
Create one more chart that tells an interesting story from the data. The chart type and content are your choice. Here are some ideas:
- Bar chart showing donations by age group (17–25, 26–35, 36–45, 46–55, 56+)
- Line chart showing cumulative donations throughout the day
- Doughnut chart comparing successful vs. deferred overall
- Stacked bar chart showing blood type distribution by time slot
The chart must have a title, labels, and tell a clear visual story.
Add these advanced formulas to your workbook (in your Summary section or Dashboard sheet):
- =SUMIF — Total units collected by each time slot (Morning, Midday, Afternoon)
- =COUNTIF — Count of donors by each blood type (if not done in Week 4)
- =AVERAGEIF — Average age of successful donors vs. average age of deferred donors
- Sparklines — Add sparklines in your summary section showing mini-trends (e.g., donations by time slot or by blood type). Insert > Sparklines > Line or Column.
Create a separate "Lookup" sheet in your workbook. On this sheet, build two sections:
Part A — Blood Type Compatibility Reference Table
- Build a reference table listing all 8 blood types (A+, A-, B+, B-, AB+, AB-, O+, O-)
- For each type, include who it can donate to and who it can receive from
- This table serves as the lookup source for Part B and as a quick-reference for Dr. Martinez's board presentation
Part B — Donor Lookup Tool
- Create a "Donor Lookup" section where entering any Donor ID automatically retrieves that donor's name, blood type, and donation status from the main Donor Data sheet
- Use =VLOOKUP or =XLOOKUP formulas to pull each piece of information
- Test the lookup with at least 3 different Donor IDs — show each test in its own row so the results are visible when grading
VLOOKUP syntax: =VLOOKUP(lookup_value, table_array, col_index, FALSE). The last argument FALSE means exact match. If your Donor ID is in column A of the Donor Data sheet, set that as the first column of your table_array, then use col_index 2 for Name, 3 for Blood Type, etc.
Create a dedicated "Dashboard" sheet that Dr. Martinez can present to the Board of Directors. It should include:
(Blood Types)
(By Time Slot)
Your dashboard must include:
- Key metrics at the top: Total donors, total units collected, success rate percentage
- Summary table with blood type breakdown (type, count, percentage)
- At least 2 embedded charts (copy/move from your other sheets)
- Clean, professional formatting suitable for a board meeting
The workbook as a whole should also demonstrate overall professionalism:
- Sheet tabs named clearly: "Donor Data", "Summary", "Lookup", "Dashboard", etc.
- Consistent formatting throughout all sheets (fonts, colors, number formats)
- No spelling errors in labels, titles, or notes
- Data integrity: All formulas reference actual data; charts update if data changes
Hide gridlines on the Dashboard sheet (View > uncheck Gridlines) for a cleaner look. Use cell borders, fill colors, and merged cells to create a polished layout. Think of it like a one-page infographic built entirely in Excel.
Step-by-Step Instructions
Follow these steps to complete your final assignment:
- Open your Week 4 workbook (LastName_Week4_BloodDrive.xlsx).
- If you do not already have a blood type summary (COUNTIF for each type), create one now on a Summary sheet.
- Select the blood type summary data and insert a Pie Chart (Insert > Chart > Pie). Add data labels with percentages.
- Create a small table summarizing Successful vs. Deferred counts by time slot (use COUNTIFS or manual tallies). Insert a Clustered Column Chart.
- Create your third chart of choice — find an interesting angle in the data.
- Add SUMIF, AVERAGEIF, and Sparklines to your Summary section.
- Create a new sheet named "Lookup". Build a blood type compatibility reference table for all 8 types, then create a Donor Lookup section using VLOOKUP or XLOOKUP to retrieve donor info by ID. Test with at least 3 Donor IDs.
- Create a new sheet named "Dashboard". Hide gridlines for a clean look.
- Build your dashboard: key metrics at top, summary table, embedded charts. Use merged cells, fill colors, and borders.
- Name all sheet tabs clearly. Review for spelling, formatting consistency, and data integrity.
- Save as LastName_Week5_BloodDrive.xlsx.
Requirements Checklist
Before submitting, verify your workbook meets all requirements:
- Pie chart shows all 8 blood types with percentage data labels
- Pie chart has a title and professional color scheme
- Clustered column chart compares Successful vs. Deferred by time slot
- Column chart has axis labels, title, and legend
- Third chart tells an interesting data story with title and labels
- =SUMIF calculates units by each time slot
- =COUNTIF counts donors by blood type
- =AVERAGEIF shows average age for successful vs. deferred donors
- Sparklines are included in the summary section
- Lookup sheet has a blood type compatibility reference table for all 8 types
- VLOOKUP or XLOOKUP retrieves donor name, blood type, and status from Donor Data
- Lookup is tested with at least 3 different Donor IDs (results visible)
- Dashboard sheet has key metrics at the top (donors, units, success rate)
- Dashboard includes a blood type summary table
- Dashboard has at least 2 embedded charts
- Dashboard is clean and professional (no gridlines, polished layout)
- Sheet tabs are named clearly
- Consistent formatting throughout the workbook
- No spelling errors in labels or titles
- All formulas reference live data (not hard-coded values)
Grading Rubric
| Criteria | Description | Points |
|---|---|---|
| Pie Chart | All 8 blood types shown with percentages, professional colors, proper title | 10 |
| Column Chart | Clustered columns for Successful vs. Deferred by time slot, axis labels, title, legend | 10 |
| Additional Chart | Creative chart choice that reveals an interesting insight from the data, with title and labels | 5 |
| Advanced Formulas | SUMIF, COUNTIF, AVERAGEIF used correctly; Sparklines included in summary | 10 |
| Donor Lookup | VLOOKUP/XLOOKUP correctly retrieves donor name, blood type, and status from reference table; blood type compatibility table included; tested with 3+ Donor IDs | 5 |
| Executive Dashboard | Key metrics, summary table, 2+ embedded charts, clean professional layout; clear sheet names, consistent formatting, no spelling errors, live data integrity | 10 |
| Total | 50 | |
Submission Instructions
Submit your completed workbook through the course LMS:
- Format: Excel file (.xlsx) with all charts and the Dashboard sheet
- File name: LastName_Week5_BloodDrive.xlsx
- Deadline: End of Week 5 (check your course calendar for exact date)
Your workbook should contain multiple sheets: Donor Data, Summary, Lookup, Dashboard, Filtered View, and any chart sheets.
Need Help?
Resources to help you finish strong:
The skills you demonstrated this week — data visualization, lookup formulas, conditional analysis, and professional dashboards — are among the most sought-after in any workplace. You are ready to apply these skills in your career!