By Ankit Srivastava, Data Analytics Trainer at SlideScope Institute
Healthcare analytics has become one of the most impactful applications of data visualization. In this project, I created an Excel Dashboard that analyzes patient treatment and recovery performance using real-world-style data. The goal was to uncover insights like – Which department has the best recovery score? Do higher treatment costs mean better recovery? How does patient age or gender affect outcomes?
This project was built entirely in Microsoft Excel, using pivot tables, calculated columns, slicers, and charts. It is a perfect example of how even non-programming professionals can perform meaningful analytics with Excel.
🧾 Step 1: Understanding the Dataset
The dataset contains the following columns:
| Column | Type | Description |
|---|---|---|
| Patient ID | Identifier | Unique ID for each patient (e.g., P1001, P1002) |
| Department | Categorical | Medical department (Cardiology, Orthopedics, etc.) |
| Treatment Type | Categorical | Surgery, Medication, Therapy, or Observation |
| Doctor Name | Categorical | Name of the consulting doctor |
| Gender | Categorical | Male, Female, or Other |
| Age | Numerical | Age of the patient in years |
| Treatment Cost | Numerical | Total cost incurred (₹ or $) |
| Hospital Stay (Days) | Numerical | Number of days admitted |
| Recovery Score | Numerical | Scale 0–100, measuring recovery quality |
These columns provide an excellent mix of categorical and numerical data — ideal for building an analytical dashboard.
Get the Hospital Patient Treatment Dataset by Slidescope
💡 Step 2: Adding Calculated Fields
To make the analysis deeper, I added two calculated fields using Excel formulas:
(A) Cost Category
=IF([@Treatment Cost]>75000, "High Cost", "Low Cost")
This formula divides patients into two spending categories — High Cost and Low Cost. It helps identify whether higher costs are linked to better recovery.
(B) Age Group
=IF(A2<=5,"Infant / Toddler",
IF(A2<=12,"Child",
IF(A2<=19,"Teenager",
IF(A2<=29,"Young Adult",
IF(A2<=44,"Adult",
IF(A2<=59,"Middle-aged",
IF(A2<=74,"Senior","Elderly")))))))
This creates 8 dynamic age brackets, allowing us to study recovery across different life stages.
📊 Step 3: Creating Pivot Tables
Each insight in the dashboard is powered by Pivot Tables. I created several pivot tables on a separate sheet (pivot_table) to aggregate and summarize the data.
- Average Recovery Score by Department – to see which department drives faster recovery.
- Average Recovery Score by Doctor – to compare doctor-wise outcomes.
- Average Recovery Score by Treatment Type – to understand if surgery or therapy is more effective.
- Total and Average Treatment Cost by Gender – to observe gender-based patterns.
- Average Recovery by Age Group – to explore trends across different age brackets.
- Cost Category and Recovery Comparison – to analyze the relationship between expenditure and success rate.
Each of these tables forms the foundation for the charts we’ll add next.
🎨 Step 4: Designing the Dashboard Layout
I created a new sheet called Dashboard and divided it into sections:
- Top Row KPIs
- Total Patients Count
- Average Treatment Cost
- Average Patient Age
- Average Hospital Stay
- Average Recovery Score
=COUNTA(A2:A1000) =AVERAGE([Treatment Cost]) =AVERAGE([Age]) =AVERAGE([Hospital Stay (Days)]) =AVERAGE([Recovery Score])I formatted them using large icons and shapes to give a professional Power BI–like look.
📈 Step 5: Adding Visuals and Charts
I used a variety of Excel charts to visualize the data in an engaging and analytical way.
1. Clustered Column Chart
Title: Avg Recovery Score by Department
This chart compares different departments — Pediatrics, Oncology, Neurology, Gastroenterology — and shows how each performs in terms of recovery.
2. Bar Chart
Title: Avg Recovery Score by Doctor Name
This highlights which doctors have the best patient outcomes.
3. Doughnut Chart
Title: Avg Recovery Score by Gender
Displays the proportion of recovery performance among Male, Female, and Other genders.
4. Bar Chart
Title: Avg Recovery Score by Treatment Type
Compares Medication, Surgery, Therapy, and Observation.
5. Pie Chart
Title: Cost Category by Recovery
Shows whether High Cost or Low Cost treatments correlate with better recovery outcomes.
6. Treemap
Title: Avg Recovery Score by Age Group
Visualizes multiple age segments in one compact chart, making patterns across life stages easy to read.
🧭 Step 6: Adding Interactive Filters (Slicers)
To make the dashboard interactive, I added Slicers for:
- Gender
- Doctor Name
- Treatment Type
- Department
Users can click these buttons to instantly filter all charts — allowing focused analysis, for example:
- Compare recovery trends for a specific doctor or department.
- Check if male or female patients recover faster.
- Study which treatment type leads to the best outcomes within a department.
Slicers are linked to multiple pivot tables using the “Report Connections” option, ensuring synchronized filtering across visuals.
🧩 Step 7: Formatting and Dashboard Aesthetics
A visually clean layout makes analytics easier to interpret.
Here’s what I did to make the dashboard appealing:
- Used consistent green and white color themes (representing healthcare).
- Added icons beside KPIs (patients, cost, calendar, heart, etc.) for better readability.
- Applied rounded chart corners and shadows for depth.
- Ensured slicers had uniform button styles and aligned neatly.
- Added the SlideScope Institute logo for professional branding.
- Used bold section titles and subtle separators to divide analysis blocks.
💬 Step 8: Interpreting the Insights
Once the dashboard was ready, I explored the data to extract insights:
- Departments like Pediatrics and Gastroenterology had higher recovery scores, suggesting effective care processes.
- Shorter hospital stays often correlated with higher recovery scores, indicating efficient treatment.
- Treatment Type: Surgery and Therapy showed stronger results than medication-only treatments.
- High-cost treatments didn’t always guarantee higher recovery — suggesting cost-effectiveness varies by department.
- Doctors’ performance differed slightly, which can be useful for internal benchmarking.
- Age and Recovery: Middle-aged and young adults recovered faster, while elderly patients had lower average scores.
This kind of insight helps healthcare managers allocate resources, design better treatment plans, and evaluate hospital efficiency.
🧠 Step 9: Why Excel Dashboards Are Powerful
Many people underestimate Excel’s capabilities. But with pivot tables, slicers, and calculated fields, Excel can rival professional BI tools like Power BI or Tableau for small- to medium-sized datasets.
This dashboard proves how data professionals and healthcare analysts can:
- Build interactive reports without coding.
- Combine multiple KPIs in one page.
- Empower decision-makers with visual insights.
🧩 Step 10: Summary – Key Learnings
By the end of this project, learners understand how to:
- Clean and organize raw hospital data.
- Create meaningful calculated columns.
- Build pivot tables for summarized analysis.
- Design professional Excel dashboards.
- Use slicers for interactivity.
- Interpret insights and communicate findings effectively.
This project is part of the Data Analytics & BI course at SlideScope Institute, where we train learners to analyze real-world datasets using Excel, Power BI, SQL, and Python.