Microsoft Excel is a spreadsheet package for organising data, performing calculations, analysing patterns and presenting results. Emergency medical technicians can use it for equipment inventories, duty rosters, training registers, audit summaries, stock monitoring, response-time analysis, service statistics and educational exercises. Excel is powerful, but a spreadsheet is only as safe as its design, data quality, formulas, access controls and clinical oversight.
Learning outcomes
- Explain workbooks, worksheets, cells, ranges, formulas and functions.
- Enter, format and validate data consistently.
- Use relative and absolute references in common EMT calculations.
- Create tables, sort and filter records without separating related data.
- Build clear charts and summaries for stock, training and quality-improvement work.
- Use conditional formatting, data validation and protection appropriately.
- Recognise spreadsheet errors, verify results and protect confidential information.
1. Excel terminology
| Term | Meaning | Emergency-care example |
|---|---|---|
| Workbook | The Excel file containing one or more worksheets. | Quarterly ambulance-equipment workbook. |
| Worksheet | A grid of rows and columns within a workbook. | One sheet for stock, another for training attendance. |
| Cell | One intersection of a column and row. | B7 contains the quantity of oxygen masks. |
| Range | A selected group of cells. | B2:F20 contains monthly response data. |
| Row/column | Horizontal/vertical series identified by numbers/letters. | Row 5 is one item; column C is expiry date. |
| Formula | An expression beginning with = that calculates a result. | =C2-D2 for stock remaining. |
| Function | A named built-in calculation. | SUM, AVERAGE, COUNTIF or IF. |
| Workbook protection | Controls changes to sheets or structure. | Locks formulas in a monitoring template. |
2. A safe spreadsheet design
Begin with the question the spreadsheet must answer. Define the data dictionary before entering rows: what each column means, the unit, allowed values, date format, responsible person and source. Keep raw data separate from calculations and reports.
| Sheet | Purpose | Example |
|---|---|---|
| Read me / instructions | Explains scope, owner, definitions, version and update date. | “Counts are physical stock at 08:00; expiry dates use YYYY-MM-DD.” |
| Raw data | Stores observations without manual totals mixed into the list. | One row per ambulance check. |
| Calculations | Contains formulas and controlled lookup values. | Days to expiry and reorder flags. |
| Dashboard/report | Presents reviewed summaries for decisions. | Monthly stock-out rate and response-time chart. |
| Data dictionary | Defines fields, units and permitted values. | “Response time = minutes from dispatch to arrival.” |
3. Entering data accurately
- Give every column one meaning and every row one observation or item.
- Use one header row; avoid blank rows inside a dataset.
- Do not merge cells in a data table.
- Enter numbers as numbers, not text containing units; place the unit in the header.
- Use real dates rather than typed text such as “next Monday.”
- Use a controlled list for categories such as “serviceable,” “repair,” or “retire.”
- Record missing data explicitly according to the agreed code; do not turn an unknown into zero.
- Keep an audit trail for corrections and never silently replace original observations.
4. Formatting without changing meaning
Number formatting changes how a value appears, not the underlying value. A date, percentage, time and decimal can look similar while being calculated differently. Confirm the formula bar and format before interpreting a result.
| Data | Recommended display | Common mistake |
|---|---|---|
| Count | Whole number | Typing “12 masks” into a numeric cell. |
| Percentage | Percentage with a stated denominator | Calling 0.8 “80” without formatting. |
| Time interval | Minutes or hh:mm, clearly labelled | Mixing clock time and elapsed time. |
| Temperature or weight | Number with unit in the header | Mixing °C and °F in one column. |
| Date | Consistent unambiguous format | Confusing day/month and month/day. |
5. Formulas and cell references
Every formula starts with =. Basic operators are +, -, *, / and ^. Parentheses control order. A cell reference such as B2 changes when copied; this is a relative reference. A reference such as $B$2 stays fixed; this is an absolute reference.
| Formula | Meaning | EMT example |
|---|---|---|
| =C2-D2 | Subtracts D2 from C2. | Opening stock minus issued stock. |
| =B2/C2 | Divides B2 by C2. | Successful handovers divided by total handovers. |
| =SUM(B2:B20) | Adds a range. | Total items received. |
| =AVERAGE(C2:C20) | Calculates the arithmetic mean. | Average response time, if appropriate. |
| =$B$2*C5 | Uses a fixed rate in B2. | Calculates a cost using a fixed unit price. |
6. Useful functions
| Function | Purpose | Example use |
|---|---|---|
| SUM | Adds numbers. | Total consumables used. |
| AVERAGE | Mean value. | Mean training score. |
| MIN / MAX | Smallest/largest value. | Range of response times. |
| COUNT / COUNTA | Counts numbers / non-empty cells. | Number of observations entered. |
| COUNTIF / COUNTIFS | Counts values meeting criteria. | Items below reorder level. |
| SUMIF / SUMIFS | Adds values meeting criteria. | Monthly cost for oxygen supplies. |
| IF | Returns one result when true and another when false. | Flag “REORDER” when stock is low. |
| AND / OR | Combines logical tests. | Flag an item that is low and near expiry. |
| ROUND | Rounds a result to a stated number of places. | Display a rate to one decimal. |
| XLOOKUP or VLOOKUP | Finds related information in a reference table. | Retrieve unit price from an approved catalogue. |
7. Examples of EMT calculations
7.1 Stock balance
If opening stock is in B2, receipts in C2, issues in D2 and losses in E2, balance in F2 may be =B2+C2-D2-E2. Confirm that all figures use the same unit and period.
7.2 Reorder flag
If current stock is F2 and minimum level is G2, a simple flag is =IF(F2<=G2,"REORDER","OK"). Add a separate review for expiry and supplier lead time; a quantity above minimum can still be unusable.
7.3 Percentage
For completed equipment checks in B2 and scheduled checks in C2, the completion rate is =IFERROR(B2/C2,0). Report the denominator and explain if there were cancelled shifts or missing forms.
7.4 Time intervals
Store dispatch and arrival as date-time values, then subtract: =(Arrival-Dispatch)*1440 gives minutes. Check time zones, midnight crossings and whether the recorded events were entered retrospectively.
8. Excel tables
Convert a clean data range into an Excel Table. Tables provide named columns, automatic filter buttons, structured references and formulas that extend as rows are added.
- Ensure one header row and no blank rows or columns in the data.
- Select the range and choose Insert > Table.
- Confirm “My table has headers.”
- Give the table a descriptive name, such as EquipmentChecks.
- Check that calculated columns and formats extend correctly.
- Test filtering and sorting on a copy before using the original operational file.
9. Sorting and filtering safely
Sorting changes the display order; filtering hides rows that do not meet a condition. Always select the complete table or use an Excel Table. Sorting one column alone can separate a patient identifier from the corresponding observation.
| Task | Example | Safety check |
|---|---|---|
| Sort | Order equipment by expiry date. | Expand the selection to all related columns. |
| Filter | Show only “repair” items. | Check the filter indicator and clear it before reporting totals. |
| Custom sort | Sort by station then item type. | Use a stable secondary sort and preserve the original order in a copy. |
| Filter by date | Show incidents in one month. | Confirm start/end dates and time components. |
10. Data validation
Data validation restricts what can be entered and can provide a drop-down list, number range, date range or warning message. It prevents many typing errors but can be bypassed by pasted data, formulas or other tools, so quality checks remain necessary.
- Use lists for status, station, shift and equipment category.
- Set minimum and maximum values only when clinically or operationally justified.
- Write an input message that explains the expected unit and format.
- Use an error alert that explains how to correct the value.
- Review copied or imported data because validation may not catch invalid values.
- Protect validation settings in shared templates.
11. Conditional formatting
Conditional formatting highlights a cell when a rule is true. It can flag expired stock, low quantities, missing observations or response times beyond a target. Do not use colour as the only communication method; include text flags and a legend.
| Rule | Useful display | Interpretation caution |
|---|---|---|
| Expiry date less than today | Red background plus “EXPIRED” text. | Check whether the date is correct and the item is quarantined. |
| Stock below minimum | Amber “REORDER” flag. | Consider supplier lead time and substitute availability. |
| Missing required value | Visible “MISSING” marker. | Do not treat missing as normal or zero. |
| Response time over target | Icon or label with minutes. | Investigate context rather than blaming a person. |
12. Charts and visual summaries
A chart should answer a defined question. Choose a simple chart, label axes and units, show the time period and avoid decorative 3-D effects that distort comparisons.
| Chart | Best use | Example |
|---|---|---|
| Column/bar | Compare categories. | Stock-outs by station. |
| Line | Show trend over time. | Monthly ambulance response time. |
| Pie/doughnut | Simple composition with few categories. | Disposition categories in a small sample. |
| Scatter | Explore relationship between two numeric variables. | Call volume and response time. |
| Combo | Compare measures with caution. | Calls and staffing; label axes clearly. |
Do not infer causation from a chart. Mark small samples, missing months, changes in definitions and data-quality limitations.
13. PivotTables and summaries
A PivotTable groups and summarises a structured dataset without rewriting the raw rows. It can count incidents by station, sum consumables by month or compare training attendance by cohort. Before building one, ensure the source has unique headers, one record per row and consistent categories.
- Select the clean table and insert a PivotTable on a new worksheet.
- Place a meaningful category in Rows, a time or group in Columns and a measure in Values.
- Confirm whether Values are counted, summed or averaged.
- Filter responsibly and show the selected period and denominator.
- Refresh after data changes and check that the source range includes new rows.
- Review the summary against a manual sample before using it for decisions.
14. What-if analysis and forecasting
Goal Seek, scenarios and forecasts can explore planning questions such as staffing or stock requirements. They are estimates, not guarantees. Document assumptions, time period, data source and uncertainty, and obtain managerial approval before acting on a model.
15. Quality-improvement and EMT applications
| Application | Suggested fields | Quality controls |
|---|---|---|
| Equipment inventory | Asset ID, item, location, quantity, condition, expiry, checker, date. | Unique IDs, controlled status list, physical verification. |
| Stock monitoring | Opening, received, issued, lost, balance, minimum, reorder flag. | Same units, period and authorised reviewer. |
| Training register | Learner, module, date, attendance, assessment, retraining due. | Use approved scores and protect personal data. |
| Response-time audit | Call, dispatch, arrival, handover, location, priority. | Define each timestamp and check missing events. |
| Incident trend | Date, type, location, contributing factors, action, status. | Use objective language and restrict access. |
| Service dashboard | Calls, transports, refusals, delays, staffing and data-quality notes. | Show denominators, definitions and caveats. |
16. Protecting formulas and workbooks
- Lock formula cells and leave only intended input cells editable.
- Protect worksheet structure to prevent accidental deletion or renaming.
- Use strong, managed passwords where protection is appropriate.
- Keep a master template separate from working copies.
- Do not assume worksheet protection is encryption or a complete privacy control.
- Store sensitive workbooks only in approved locations and limit sharing permissions.
- Keep a version date, owner, reviewer and change log.
17. Spreadsheet errors and checking
| Error | Meaning or cause | Response |
|---|---|---|
| #DIV/0! | Formula divides by zero or a blank denominator. | Check denominator and use IFERROR only when the interpretation is documented. |
| #VALUE! | Wrong data type or text in a calculation. | Inspect cells, units and imported values. |
| #REF! | Formula refers to a deleted or invalid cell. | Restore or rebuild the reference; do not ignore it. |
| #NAME? | Misspelled function or undefined name. | Check function spelling and named ranges. |
| #### | Column is too narrow or a negative date/time is displayed. | Widen the column and inspect the underlying value. |
| Wrong total | Filter, range, unit or formula selection is wrong. | Recalculate independently and inspect the source rows. |
18. Privacy and safe use of patient-related data
- Do not copy identifiable patient data into a personal workbook unless explicitly authorised.
- Use de-identified or coded data for teaching and quality-improvement where possible.
- Limit columns to the minimum necessary information.
- Protect screens, printed reports, email attachments and cloud links.
- Check hidden sheets, comments, formulas and file properties before sharing.
- Remove temporary exports and USB copies according to retention policy.
- Do not use an online conversion or AI service with confidential data unless approved.
19. Scenarios
20. Practical checklist
21. Revision questions
- Differentiate a workbook, worksheet, cell, range, formula and function.
- Why should every row represent one observation or item?
- Explain relative and absolute references with an EMT example.
- Write a formula for stock balance and identify three assumptions it makes.
- How can data validation improve but not guarantee data quality?
- Why is sorting one column alone dangerous?
- Choose a suitable chart for a monthly response-time trend and explain your choice.
- What checks should precede a PivotTable report?
- Why is worksheet protection not the same as confidentiality?
- Describe a safe response to a spreadsheet showing #REF! in a critical total.
Key takeaways
- Design the data structure before decorating the worksheet.
- Use formulas, tables, validation and protection to reduce avoidable errors.
- Sort and filter complete datasets so related fields stay together.
- Charts and PivotTables summarise data; they do not prove causation or replace clinical judgement.
- Always verify units, denominators, date ranges, missing values and source records.
- Keep patient information minimal, authorised, secure and auditable.
Further reading: Microsoft Excel Help and Learning, official guidance on formulas, tables, sorting, data validation, accessibility and worksheet protection, plus local data-governance and quality-improvement procedures.