Excel for Healthcare and Hospitals: OPD, Beds, Pharmacy and TAT (With Templates)
This guide is for people in the healthcare industry who use Excel every day. You will see every common file, the formulas people actually type, and downloadable templates with made-up Indian ₹ data. Related short lessons are linked — we do not re-teach them here.
What you'll learn
- Which roles use Excel in healthcare
- Every common Excel file type for this industry
- Sample tables with realistic ₹ examples (made-up names)
- Formulas: Occupancy %, TAT hours, Reorder flag, Days to expiry and more
- Ready templates you can download and reuse
Downloads: healthcare-opd-beds-pharmacy.xlsx (OPD + beds + pharmacy + lab TAT + billing summary)
Example data only
All names, amounts, rates and dates are made up for practice. They are not real client or employer records. Follow your organisation's own rules and software for live work.
In short
One industry → many Excel files. Each file is one clear list. Yellow cells are inputs; green cells are formulas. Start with the Roles table, then open the matching template.
थोडक्यात
एक industry → अनेक Excel files. प्रत्येक file = एक स्पष्ट यादी. Yellow = input; green = formula. Roles table पासून सुरू करा, नंतर matching template उघडा.
संक्षेप में
एक industry → कई Excel files. हर file = एक साफ सूची. Yellow = input; green = formula. Roles table से शुरू करें, फिर matching template खोलें.
Related chapters (already on this site)
Why
Some topics already have a focused lesson. Use those for depth; use this guide for the full industry picture.
What
| Chapter | What it covers |
|---|---|
| Excel for HR (profession) | Staff roster ideas. |
| 22. KPI in All Industries | Service KPIs. |
| Insurance industry guide | Claims ideas if TPA-linked. |
How
- Skim the link if you need that one skill.
- Come back here for the full file list and templates.
1. Who uses Excel
Why
Real hospitals use HIS. These sheets teach Excel patterns with made-up names.
What
| Role | Typical Excel work |
|---|---|
| Front office | OPD registration list. |
| Ward in-charge | Bed board, occupancy. |
| Pharmacy | Drug stock, expiry, reorder. |
| Lab desk | Sample TAT tracking. |
How
- Download healthcare-opd-beds-pharmacy.xlsx.
- Never put real patient identifiers in practice files.
2. Every common Excel file
Why
If you only learn one dashboard, you miss the daily registers that feed it.
What
| File | What one row / sheet holds |
|---|---|
| OPD register | Token, patient (made-up), doctor, fee, wait minutes. |
| Bed occupancy | Ward, beds, occupied, occupancy %. |
| Pharmacy stock | Drug, batch, qty, expiry, reorder flag. |
| Lab TAT | Test, collect time, report time, TAT hours. |
| Billing summary | OPD fees, IP charges, total (teaching). |
How
- Pick the file that matches your role.
- Download the template that contains that sheet.
- Replace yellow sample rows with your practice data (still made-up if you share screenshots).
3. Formulas people use
Why
Typed numbers go stale. Formulas update when inputs change.
What
| Name | Formula | What it does |
|---|---|---|
| Occupancy % | =occupied/beds |
Ward fill. |
| TAT hours | =(report-collect)*24 |
Lab turnaround. |
| Reorder flag | =IF(qty<=min,"Reorder","OK") |
Pharmacy. |
| Days to expiry | =expiry-TODAY() |
Near-expiry watch. |
| Avg wait | =AVERAGE(wait_mins) |
OPD experience. |
How
- Open the Formulas used sheet inside each template.
- Change only yellow cells.
- Watch green cells recalculate.
3. OPD and beds
Why
Wait time and occupancy are manager questions every evening.
What
OPD wait + bed occupancy %.
How
- Occupancy % = Occupied ÷ Beds.
- Avg wait from Wait minutes column.
- Filter high wait doctors for staffing talk.
Figure. Lab TAT hours
4. Pharmacy and lab TAT
Why
Stockouts and slow reports become patient complaints.
What
Reorder flag, days to expiry, TAT hours.
How
- Reorder when Qty ≤ Min.
- Days to expiry highlights near-expiry batches.
- TAT hours = (Report − Collect) × 24.
5. Sheet previews
Why
See the layout before you open Excel.
What
Screenshots of the main sheets in the downloadable templates.
How
Scroll the figures, then download the matching .xlsx.
Figure. Bed occupancy
Figure. Lab TAT hours
6. Common mistakes
| Mistake | What goes wrong | Fix |
|---|---|---|
| Real patient names in shared Excel | Privacy breach. | Made-up names only in this course. |
| Expiry as text | Cannot sort near-expiry. | Real date format. |
7. FAQ
Clinical advice?
No. Excel teaching only. Follow hospital protocol and law.
8. Try at home
Try at home
- Occupancy % for Ward A?
- Which drugs show Reorder?
- TAT hours for sample Lab L1?
Recap
In short
- 4 roles use Excel in this industry.
- 5 common file types are listed above.
- 1 downloadable templates with yellow inputs and green formulas.
- Rates and rules here are teaching examples only.
- Use Related chapters for focused skills.
Quick revision cards
Quick revision
1 interview card, 1 comparison card and 1 revision card. Save on your phone or download as PNG. Numbers are made up for practice.
झटपट उजळणी
1 interview card, 1 comparison card आणि 1 revision card. Phone वर save करा किंवा PNG download करा. आकडे सरावासाठी काल्पनिक आहेत.
झटपट दोहराई
1 interview card, 1 comparison card और 1 revision card. Phone पर save करें या PNG download करें. आंकड़े अभ्यास के लिए काल्पनिक हैं.
