🏥 Hospital Emergency Room Dashboard – MIS Project

Built using Excel tools like Pivot Tables, Sparklines, Conditional Formatting, and Charts to track patient count, wait times, referrals, and more. A hands-on project completed during my data analytics learning journey.

---

📑 Table of Contents

• 📌 Project Overview
• 🏢 Project Context
• 🎯 Project Objectives
• 🧩 Key Dashboard Features
• 📂 Dataset Used
• 🛠 Tools & Excel Techniques Used
• 🔄 Project Workflow
• 📈 Project Learnings
• 💡 Business Impact & Insights
• 🚀 Future Scope
• 📦 How to Use This Project
• 📬 Connect with Me
• 🖼️ Dashboard Snapshots

---

📌 Project Overview

This project is my first end-to-end Excel MIS Dashboard, built during the foundational phase of my journey into MIS and Data Analytics.

The dashboard focuses on real-time Emergency Room (ER) data for a hospital and showcases how Excel can be a powerful tool for data-driven decision-making in the healthcare domain.

It enables healthcare administrators to:

• Visualize key performance metrics
• Reduce service delays
• Make timely operational improvements
• Use intuitive, interactive reports for strategic decisions

---

🏢 Project Context

Hospitals often face challenges in managing emergency room (ER) operations efficiently.
Without a centralized reporting system, decision-makers struggle to identify peak load periods, track wait times, and improve patient satisfaction.

This project addresses those challenges by:

• Consolidating ER performance data in one place
• Providing quick, visual insights to hospital management
• Highlighting operational bottlenecks and improvement areas

---

🎯 Project Objectives

The core aim of this dashboard is to empower hospital stakeholders with real-time visibility into Emergency Room performance.

By presenting critical metrics in a visually engaging format, this dashboard enables decision-makers to:

✅ Identify peak traffic periods
✅ Monitor patient service and wait times
✅ Analyze patient demographics and satisfaction
✅ Evaluate department-level referrals
✅ Improve overall emergency operations

---

🧩 Key Dashboard Features

The dashboard offers a consolidated view of essential metrics through a combination of charts, conditional formatting, and dynamic Excel features:

• 👥 Patient Count Trends – Visualized using sparklines to detect peak days and patient flow patterns
• ⏱ Average Waiting Time – Tracks delays before treatment and helps in optimizing staff efficiency
• 💬 Satisfaction Score – Reflects patient feedback on ER experience and service quality
• 🛏 Admission Status – Shows % of patients admitted vs. not admitted
• 📊 Age Group Distribution – Classifies patients into age brackets for demographic insight
• ✅ Timeliness of Service – % of patients seen within 30 minutes
• 👫 Gender Analysis – Compares male vs. female patient visits
• 🏥 Department Referrals – Reveals departments receiving the most ER referrals (e.g., Orthopedics, Physician, etc.)

---

📂 Dataset Used

• Source: Self-created dataset for learning purposes

• Records: Contains patient-level ER visit information for a defined time period

• Key Fields:

  • Patient ID
  • Date of Visit
  • Waiting Time (Minutes)
  • Satisfaction Score
  • Age Group
  • Gender
  • Admission Status
  • Referral Department

---

🛠 Tools & Excel Techniques Used

This dashboard was built entirely in Microsoft Excel, utilizing:

• 📁 Data Cleaning & Structuring – Removed null values, corrected formatting, ensured proper tabular structure

• 📊 Pivot Tables – Aggregated ER data by categories for dynamic summaries

• 📈 Charts – Bar, Pie, Doughnut, and Column Charts used for impactful visuals

• 🌟 Sparklines – Trend indicators for daily ER visits

• 🎨 Conditional Formatting – Highlighted abnormal patterns or KPIs visually

• 🧠 Excel Formulas – Used:

  • IF(), AVERAGEIF(), COUNTIF()
  • INDEX-MATCH(), SUMIFS()
  • Logical and aggregation functions for automation

---

🔄 Project Workflow

1. Requirement Understanding – Identify KPIs relevant to hospital ER performance
2. Data Preparation – Clean, format, and organize the dataset
3. Pivot Table Creation – Summarize key metrics
4. Chart Development – Build visuals for each KPI
5. Dashboard Design – Combine elements into a single interactive sheet
6. Testing & Refinement – Validate metrics and improve layout

---

📈 Project Learnings

This dashboard gave me real-world exposure to how Excel functions as a Business Intelligence (BI) tool.
It also helped me understand the end-to-end workflow of data analysis and reporting in a critical industry like healthcare.

Key Takeaways:

• ✔ Importance of clean, structured data for analysis
• ✔ Role of visual storytelling in decision-making
• ✔ Power of Excel tools like Pivot Tables and Charts
• ✔ Confidence in creating independent Excel MIS projects from scratch

---

💡 Business Impact & Insights

From the dashboard, hospital administrators can:

• Identify busiest days and prepare staff accordingly
• Reduce average waiting times by adjusting shifts
• Focus on departments with highest referrals to balance workloads
• Enhance patient satisfaction through timely care

---

🚀 Future Scope

This project lays a strong foundation but also opens doors for further innovation:

• 🔁 Connect with Power Query for automated refreshes
• 📡 Link to external sources like CSV, Access, or SQL databases
• 📊 Rebuild using Power BI for enhanced interactivity
• 📉 Integrate time-series forecasting (e.g., for patient inflow)

---

📦 How to Use This Project

1. Download/Clone Repository
2. Open the .xlsx file in Microsoft Excel
3. Explore the dashboard sheet for interactive visuals
4. Modify the dataset to see updated results instantly

---

📬 Connect with Me

• 📧 Email: [email protected]
• 💻 GitHub: github.com/rajeevgit8055hub
• 🔗 LinkedIn: linkedin.com/in/rajeev-tiwari123
• 🌐 Website: rajeevgit8055hub.github.io/rajeevtiwari.github.io

🤝 Thanks for visiting my profile!

---

🖼️ Dashboard Snapshots

🔹 Version 1 – Final Hospital Emergency Room Dashboard (Refined)

🔹 Version 2 – Initial Hospital ER Image

---