Chapter 8 · 50 minutes

Last updated November 11, 2026

Cleaning, Transforming, and Validating Data

Opening scenario

An analyst opens an appointment export with 36,000 rows and sees blanks everywhere. The cancel time is empty for most rows, and the wait time is empty for thousands more. Her first instinct is that the file is broken and that someone lost data, and she is about to email the source team. Before she does, she checks one thing, and the concern resolves. The blanks are not lost data. They are the data telling her something, because you cannot record a cancel time for a visit that was never canceled. Cleaning is not the work of erasing blanks. It is the work of understanding them. This chapter is about making data trustworthy, which is a different job from making it look tidy.

A quick look at three appointments shows why a blank is not automatically an error. In Table 1, each outcome leaves a different field empty, and in every case the empty field is correct.

Table 1: Three appointments with different outcomes. A completed visit has a seen time but no cancel time; a no-show has neither; a canceled visit has a cancel time but no seen time. Each blank is correct for its outcome. Source: datasets/DS2_appointments_clean.csv.
appointment_id attendance_outcome seen_datetime cancel_datetime
APT000126 Completed 2024-01-01 08:12 (blank)
APT022207 No Show (blank) (blank)
APT027760 Early Cancel (blank) 2023-12-31 13:00

By the end of this chapter, you will be able to:

  • Distinguish structural missing data from true missing data, and choose the appropriate response to each.
  • Profile a dataset with counts and summary statistics before you analyze it.
  • Run a validation battery: row counts, key uniqueness, domain and range, and referential checks.
  • Detect outliers with the 1.5 times IQR (interquartile range) rule and decide what to do with them.
  • Transform fields safely without changing what the data means.
  • Build cleaning steps you can trust and repeat.

Key concepts

Data cleaning is the work of making a dataset reliable, not the work of making it look neat. A file can look tidy and still be incorrect, and a file full of blanks can be correct. This section builds the judgment and the checks that distinguish reliable work from work that cannot be verified, in four parts:

  • missingness, distinguishing a blank that is correct from a blank that is a gap,
  • profiling, looking at a column’s shape before computing on it,
  • validation, a short battery of checks that catches most problems,
  • outliers and transformation, handling extreme values and reshaping data without changing its meaning.

Two kinds of missing value

Data cleaning is the work of making a dataset reliable and ready for analysis. Structural missing data is blank because the value does not apply to that record. True missing data is blank when a value should exist but was not recorded.

A common mistake is to treat every blank as an error. There are two kinds of missing value, and they call for opposite responses:

  • Structural missing is correct and must stay empty. A canceled appointment has no seen-time, so seen_datetime is blank, and that blank is the correct answer.
  • True missing is a real gap you have to handle. A wait time absent for a patient who was seen is a value that should exist, so it needs a documented decision about whether to record, impute, or exclude it (Little & Rubin, 2019).

Figure 1 puts the two side by side. The rule that follows is strict: filling a structural blank with a zero is not cleaning, it introduces an error, because it invents an event that never happened.

Figure 1: Two kinds of blank call for opposite responses. On the left, a completed visit has no cancel time, so the blank is correct and should stay empty. On the right, a completed visit has no wait time, a value that should have been recorded, so the blank needs a documented decision. Source: concept schematic.

Before you touch a blank, decide which kind it is. Structural missing means “not applicable” and should stay empty. True missing means “should be here but is not” and needs a documented decision, never a silent fill.

Filling structural blanks with a zero is a serious cleaning error. A zero is a number, so it enters every average, sum, and chart as if a real event had happened. The real-world spotlight at the end of this chapter shows a wait-time average that collapsed for exactly this reason.

Profiling: look before you compute

Data profiling is the quick first pass that describes each field, its count of values, its minimum and maximum, and its center, so you learn the shape of the data before you analyze it.

Profiling is the practice of examining data before computing on it. Recall the summary functions from Chapter 6, AVERAGE, MEDIAN, MIN, MAX, and COUNTA, and add a quick frequency count with COUNTIF or a PivotTable. Run them on each numeric field first, and three kinds of problem surface:

  • An impossible value, such as an age of 200 or a negative wait time, appears as a minimum or maximum that cannot be real.
  • A heap, such as hundreds of records all recording exactly age 0, is invisible in the mean and median but appears in a frequency count as a single value that repeats far more than its neighbors.
  • A silent gap, such as a field that COUNTA shows is far shorter than the row count, appears as a count that does not match the number of rows.

Profiling does not fix anything. It identifies where further checks are needed, and it is the least costly check in this chapter. Chapter 9 builds on these same summaries to describe data for its own sake; here they are a diagnostic.

Validation is a battery of checks

Validation is the set of checks that confirm the data means what you think it means.

A few checks catch most problems, and a short, repeatable battery is the foundation of practical data cleaning (van der Loo & de Jonge, 2018). Figure 2 lays out the four checks, and none of them is advanced:

  • Row counts. Count the rows before and after every step. A join or filter that changes the count unexpectedly is a warning sign.
  • Key completeness and uniqueness. Confirm the primary key has no blanks and no duplicates, because a blank or a duplicate key distorts your counts without warning.
  • Domain and range. Confirm each field holds only allowed values: a flag that is 0 or 1 and nothing else (a domain check on an allowed set), and a wait time that is never negative (a range check on an interval).
  • Referential integrity. Confirm every foreign key still resolves to its dimension, the idea introduced in Chapter 4, so no facility or payer code points at nothing.
Figure 2: The validation battery: four checks that catch most problems, each with the kind of error it is designed to surface. Source: concept schematic.

A dataset that passes every check does so because the analyst deliberately asked the questions a flawed dataset would fail. Run the same battery every time, and a new problem is detected rather than overlooked.

Outliers: unusually distant values

The battery so far asks yes-or-no questions. Outliers require a different approach, because an outlier may be a valid extreme or a data error, and a range check flags neither: each value, taken on its own, can fall within the allowed range.

An outlier is a value far from the bulk of the data. The quartiles split sorted values into four equal parts: the first quartile (Q1) is the 25th percentile and the third quartile (Q3) is the 75th. The interquartile range (IQR) is Q3 minus Q1, the span of the middle half. A value is an outlier under the 1.5 times IQR rule if it sits below Q1 minus 1.5 times IQR or above Q3 plus 1.5 times IQR.

A box plot draws this rule as a picture (Tukey, 1977). Figure 3 labels every part: the box spans Q1 to Q3, the line inside is the median, the whiskers reach the farthest ordinary values, and the dashed line is the upper fence at Q3 plus 1.5 times IQR. Anything past a fence is an outlier.

Figure 3: Anatomy of a box plot, with the 1.5 times IQR rule labeled on total visit length. The box holds the middle half of visits (Q1 = 13 to Q3 = 30 minutes), the median is 21, and the upper fence sits at 55.5 minutes. The 71 points beyond it are outliers. Source: datasets/DS2_appointments_clean.csv.

The fence is a short calculation you can run in Excel.

Compute the quartiles, then the fence, from the visit-length column: =QUARTILE.INC(Appointments[total_visit_minutes], 1) returns 13 (Q1) and =QUARTILE.INC(Appointments[total_visit_minutes], 3) returns 30 (Q3). The upper fence is Q3 + 1.5 * (Q3 - Q1) = 30 + 1.5 * 17 = 55.5, and =COUNTIF(Appointments[total_visit_minutes], ">"&55.5) returns 71 outliers.

Detecting an outlier is straightforward. Deciding how to handle it is the more difficult task, and it requires judgment rather than an automatic response. Figure 4 lays out the four defensible choices:

  • Investigate and correct when the value is a data error, such as a typo or a unit mix-up.
  • Keep it when the value is real, and report it with a summary that resists outliers, such as the median and the IQR, rather than the mean.
  • Cap at the fence only under a rule you state in advance, replacing a beyond-fence value with the fence value while keeping the raw value in place. (Replacing values beyond chosen percentiles with those percentile values is the related technique of winsorizing.)
  • Exclude and document when the value falls outside the defined scope of the analysis, recording the rule and why you applied it.
Figure 4: What to do with an outlier: four defensible choices by situation, with silent deletion flagged as the one to avoid. Source: concept schematic.

The one action to avoid by default is deleting an outlier silently. It changes the result and leaves no trace, so no reviewer, including you, can tell what the number would have been. A valid observation should not be altered merely because it moves an average; when the skew is genuine, prefer a summary that resists outliers, such as the median, and keep the original value. If a value must go, exclude it on purpose and write down why.

Transforming without distorting

Transformation is reshaping data into the form analysis needs, such as standardizing dates, mapping messy codes to clean ones, or deriving a new column like a length of stay from two timestamps.

The rule is strict: a transformation may change the shape of the data, but it must not change its meaning. Figure 5 shows the safe pattern, mapping a messy status column to one standard value in a new column while the raw column stays untouched. Recall from Chapter 7 that a status field should hold one consistent value everywhere, a basic principle of well-organized spreadsheet data (Broman & Woo, 2018); making that true is a transformation. Silently rounding, recoding, or dropping rows in a way that shifts a result is not.

Figure 5: A safe transformation maps a messy status column to one standard value in a new column, while the raw column stays in place so every value traces back to its source. Source: concept schematic.

Keep the raw data untouched and transform into new columns or sheets. Then a cleaned value can be traced back to the value it came from, which is what makes the cleaning defensible.

Open datasets/DS2_appointments_clean.csv and look at the cancel_datetime column for three rows: one Completed, one No Show, and one Early Cancel. For each, decide whether a blank cancel_datetime is structural or true missing, and say in one sentence what you would do with it. Check your reasoning against Table 1.

Walkthrough: build an appointment data-quality report

This build turns the raw appointment file into the one-page data-quality report in Figure 6. Every number on it is computed by a formula that reads from the Appointments Table, so when the Table’s rows change, the report recalculates. To make next month’s export refresh automatically, import it with Power Query as in Chapter 7; the report then updates with Data > Refresh All. The report gathers four things in one place: a profile of the file, a read on its missingness, the validation battery, and the outlier count.

Figure 6: A stylized view of the finished appointment data-quality report: a profile, a missingness read, the validation battery, and the outlier count, all driven by formulas over the Appointments Table. Your worksheet will carry the same numbers; the exact layout may differ. Source: datasets/DS2_appointments_clean.csv.

Before you start: open datasets/DS2_appointments_clean.csv, and immediately save a working copy as an Excel workbook so the original stays untouched. Work only in the copy. These teaching files hold no real patient information; real patient data must stay in approved, secure storage, and you should make a local working copy only when you are authorized to.

Part A. Set up the data. Convert the raw file into a named Table so every formula can refer to its columns by name.

  1. Open datasets/DS2_appointments_clean.csv in Excel.
  2. Choose File > Save As, pick Excel Workbook (.xlsx), and name it ch08_quality.xlsx. Work in this file from here on.
  3. Click any cell inside the data. On the Insert tab, click Table, confirm My table has headers is checked, and click OK.
  4. On the Table Design tab (named Table on macOS), set the Table Name to Appointments.
  5. Add a worksheet with the + beside the sheet tabs, double-click the new tab, type Quality, and press Enter.
  6. In A1 type Appointment Data Quality Report. Select A1:B1, choose Home > Alignment > Merge & Center, then make it Bold, size 14, with a dark blue Fill Color and white Font Color.

Part B. Profile the file. A handful of summary statistics describe the visit-length column before you trust it. These are the Chapter 6 functions used as a diagnostic.

  1. In A3 type Measure and in B3 type Value; select A3:B3 and click Bold.
  2. In A4 type Total rows; in B4 enter =ROWS(Appointments). Cell B4 shows 36000.
  3. In A5 type Mean visit minutes; in B5 enter =AVERAGE(Appointments[total_visit_minutes]). It shows about 22.1. The function ignores blanks, so this is the mean among visits that have a recorded length.
  4. In A6 type Median visit minutes; in B6 enter =MEDIAN(Appointments[total_visit_minutes]). It shows 21.
  5. In A7 type Shortest visit; in B7 enter =MIN(Appointments[total_visit_minutes]). It shows 8.
  6. In A8 type Longest visit; in B8 enter =MAX(Appointments[total_visit_minutes]). It shows 78. Neither extreme is impossible, so the profile signals no problem; the longest visit is one to examine later, not to delete.

Part C. Measure missingness. Count the blanks, then test whether they are structural. Figure 7 shows the same read as one picture: the cancel time is blank in 84.9 percent of rows (most visits were never canceled), the visit times in 28.7 percent (the not-completed visits), and the copay in 38.7 percent (the field to review).

Figure 7: Share of missing values in each appointment field that has any. Most gaps are structural, not errors: cancel times for visits never canceled, visit times for visits never completed. The copay gap matches no outcome group, so it is the one to review. Source: datasets/DS2_appointments_clean.csv.
  1. In A10 type Missing visit times; in B10 enter =COUNTBLANK(Appointments[seen_datetime]). It shows 10342.
  2. In A11 type Not completed; in B11 enter =COUNTIFS(Appointments[attendance_outcome], "<>Completed"). It also shows 10342. Matching totals are a hint, not a proof, because two different groups can share a count, so confirm the records line up. In A12 type Visit-time mismatches; in B12 enter
    =COUNTIFS(Appointments[attendance_outcome], "Completed", Appointments[seen_datetime], "") + COUNTIFS(Appointments[attendance_outcome], "<>Completed", Appointments[seen_datetime], "<>")
    It returns 0: no completed visit lacks a seen-time, and no incomplete visit has one. The blank visit times are therefore structural.
  3. In A13 type Missing copay; in B13 enter =COUNTBLANK(Appointments[copay_usd]). It shows 13923, about 38.7 percent. This one matches no outcome group, so flag it for review rather than assuming it is acceptable; the Try It at the end of the chapter tracks down its cause.

Part D. Run the validation battery. Each check is a short formula, and each is expected to come back clean.

  1. Check the key for completeness and uniqueness. In A14 type Blank appointment IDs; in B14 enter =COUNTBLANK(Appointments[appointment_id]), which returns 0. In A15 type Distinct appointment IDs; in B15 enter =ROWS(UNIQUE(Appointments[appointment_id])), which returns 36000, equal to B4, so no key is blank and none is duplicated. (On Excel 2019 or earlier, which has no UNIQUE, use =SUMPRODUCT(1/COUNTIF(Appointments[appointment_id], Appointments[appointment_id])) instead, but expect it to be slow on 36,000 rows.)
  2. In A16 type Invalid no_show_flag values; in B16 enter
    =COUNTIFS(Appointments[no_show_flag], "<>0", Appointments[no_show_flag], "<>1")
    It shows 0, so every flag is a valid 0 or 1 (a domain check).
  3. In A17 type Negative wait times; in B17 enter =COUNTIF(Appointments[wait_to_room_minutes], "<0"). It shows 0, so no impossible wait time is present (a range check).
  4. In A18 type Facilities not in dimension. To check referential integrity you need the facility list in the same workbook, so import it with Power Query as in Chapter 7: choose Data > Get Data > From Text/CSV, select datasets/DS1_facility_dim.csv, and click Load. Excel adds it on a new sheet as a Table; rename the sheet Facilities and the Table FacilityDim. Then in B18 enter
    =SUMPRODUCT(--(COUNTIF(FacilityDim[facility_id], Appointments[facility_id])=0))
    It shows 0, so every appointment’s facility code resolves to a real facility.

Part E. Flag the outliers. Compute the fence from the quartiles, then count the values beyond it.

  1. In A19 type Q1 visit minutes; in B19 enter =QUARTILE.INC(Appointments[total_visit_minutes], 1). It shows 13.
  2. In A20 type Q3 visit minutes; in B20 enter =QUARTILE.INC(Appointments[total_visit_minutes], 3). It shows 30.
  3. In A21 type Upper fence; in B21 enter =B20 + 1.5*(B20-B19). It shows 55.5.
  4. In A22 type Outliers beyond fence; in B22 enter =COUNTIF(Appointments[total_visit_minutes], ">"&B21). It shows 71, the 71 points past the dashed fence in Figure 3.

Part F. Add a safe transformation. Record the outlier test as a new column, leaving the raw visit length untouched.

  1. Click the first empty column header to the right of the Table and type visit_over_fence; the Table extends to include it.
  2. In the first cell of the new column, enter
    =IF([@total_visit_minutes]="", "", IF([@total_visit_minutes]>Quality!$B$21, 1, 0))
    Excel fills the formula down the whole column. Each row now carries a 1 when the visit is beyond the fence, a 0 when it is not, and a blank when the visit length itself is blank. The raw total_visit_minutes column is unchanged, so every flag traces back to its source.

Part G. Format and finish. A last pass makes the numbers readable.

  1. Select B5:B6, apply Home > Number and one decimal. Select B4, then add B10:B13, B15, and B22 to the selection, apply Comma Style, and remove the decimals so the counts read 36,000 and 13,923.
  2. Select A3:B22, apply Home > Font > Borders > All Borders, click the Select All button, and choose Home > Format > AutoFit Column Width.
  3. Save with Ctrl+S (Cmd+S on macOS). Your sheet should resemble Figure 6.

Every number on the sheet traces back to the Table, and no calculated result was typed by hand. When the appointment data are updated, the whole report recalculates.

Practice on your own

These exercises reuse the walkthrough’s techniques on new questions. The first is fully guided, like the walkthrough. The second fades the support: it names the functions and gives a value to check against, but leaves you to assemble the formulas.

Practice A: validate the emergency-department file

Run the same profile, missingness read, validation battery, and outlier check on a different file, to confirm that the method transfers.

  1. Open datasets/DS3_ed_throughput_clean.csv, then choose File > Save As and save a copy named ch08_ed_quality.xlsx.
  2. Click any cell inside the data, choose Insert > Table, confirm My table has headers, and click OK. On the Table Design tab, set the Table Name to ED.
  3. Add a worksheet named Quality. In A1 type ED Data Quality Report, then merge A1:B1, and format it bold, size 14, with a dark fill and white font.
  4. In A3 type Measure and in B3 type Value; make A3:B3 Bold.
  5. In A4 type Total rows; in B4 enter =ROWS(ED). It shows 18000.
  6. In A5 type Mean LOS (min); in B5 enter =AVERAGE(ED[length_of_stay_minutes]). It shows about 210, the mean length of stay (LOS). In A6 type Median LOS; in B6 enter =MEDIAN(ED[length_of_stay_minutes]). It shows 190; the mean above the median is consistent with right skew.
  7. In A7 type Shortest stay; in B7 enter =MIN(ED[length_of_stay_minutes]) (shows 74). In A8 type Longest stay; in B8 enter =MAX(ED[length_of_stay_minutes]) (shows 1225).
  8. In A10 type Missing LOS; in B10 enter =COUNTBLANK(ED[length_of_stay_minutes]) (shows 584). In A11 type Left without being seen; in B11 enter =COUNTIFS(ED[left_without_being_seen_flag], 1) (also 584). To confirm the same records, in A12 type LOS mismatches; in B12 enter
    =COUNTIFS(ED[left_without_being_seen_flag], 1, ED[length_of_stay_minutes], "<>") + COUNTIFS(ED[left_without_being_seen_flag], 0, ED[length_of_stay_minutes], "")
    It returns 0, so every missing length of stay belongs to a left-without-being-seen encounter: the missingness is structural.
  9. In A13 type Distinct encounter IDs; in B13 enter =ROWS(UNIQUE(ED[encounter_id])). It shows 18000, equal to the row count, and =COUNTBLANK(ED[encounter_id]) returns 0, so the key is complete and unique.
  10. In A14 type Invalid triage levels; in B14 enter =COUNTIFS(ED[triage_acuity], "<1") + COUNTIFS(ED[triage_acuity], ">5"). It shows 0, so every triage level is a valid 1 through 5 (a domain check). In A15 type Negative LOS; in B15 enter =COUNTIF(ED[length_of_stay_minutes], "<0"). It shows 0 (a range check).
  11. In A16:A19 type Q1 LOS, Q3 LOS, Upper fence, and Outliers beyond fence. In B16 enter =QUARTILE.INC(ED[length_of_stay_minutes], 1) (shows 165), in B17 =QUARTILE.INC(ED[length_of_stay_minutes], 3) (shows 227), in B18 =B17 + 1.5*(B17-B16) (the fence, 320), and in B19 =COUNTIF(ED[length_of_stay_minutes], ">"&B18) (shows 780).
  12. Apply All Borders, click the Select All button, choose AutoFit Column Width, and save.

Practice B: flag the long ED stays and find their source

Return to ch08_ed_quality.xlsx, record the outlier test as a new column, then break the long stays down by disposition, with less guidance. This exercise names the functions and the cells and gives you values to check against, but leaves the formulas for you to build.

Goal: a flag column, a total of the long stays, a by-disposition breakdown, data bars, and a chart.

  1. Click the first empty column header to the right of the ED Table and type los_over_fence.
  2. In the first cell of the new column, build an =IF(...) that returns a blank when length_of_stay_minutes is empty, a 1 when it is greater than the fence you computed in B18, and a 0 otherwise.
    • IF takes a test, then the value when true, then the value when false, and the tests can nest: first test for the blank, then test against the fence.
    • Lock the fence cell with an absolute reference (Quality!$B$18) so every row compares to the same value.
  3. On the Quality sheet, in A21 type Long stays flagged; in B21 total the column with =COUNTIFS(ED[los_over_fence], 1).
    • Check your work: it should total 780, matching the outlier count from Practice A, step 11. If it totals far more than 780, nearly every stay, the fence reference was not locked, so check for Quality!$B$18.
  4. Break the long stays down by disposition. In A23 type Disposition and in B23 type Long stays; select A23:B23 and click Bold. In A24:A28 type Admit, Observe, Transfer, Home, and Eloped, one per row.
  5. In B24 enter =COUNTIFS(ED[disposition], A24, ED[los_over_fence], 1), then drag the fill handle down to B28.
    • Keep A24 relative (no dollar signs) so each row counts its own disposition.
  6. Select any cell in A23:B28 and sort by Long stays from largest to smallest (Data > Sort).
  7. Select B24:B28 and apply Home > Conditional Formatting > Data Bars. Then select A24:A28, hold Ctrl (Cmd on macOS), also select B24:B28, choose Insert > Charts > Clustered Bar, and title it Long stays by disposition.
  8. Apply All Borders, click the Select All button, choose AutoFit Column Width, and save.
    • Check your work: Admit tops the list with 589 long stays, followed by Observe (135) and Transfer (56); Home and Eloped show 0, so the five add to 780. In this teaching file, every long ED stay ends in an admission, an observation stay, or a transfer, and none in a discharge home. That pattern reflects boarding, patients held in the emergency department while they wait for an inpatient bed, so these long stays are a real operational signal rather than data errors. Read the clean split as a feature of this dataset, not a rule that holds in every hospital.

In datasets/DS2_appointments_clean.csv, the copay_usd field is blank in 38.7 percent of rows. You suspect the blanks are structural because telehealth visits may carry no copay. Propose a test of that idea, state exactly what you would count, and say what each result would tell you. If the telehealth test does not explain the blanks, name one other field you would profile next, and decide what to do with the blanks in each case. The answer key is at the end of the chapter.

Going deeper

The 1.5 times IQR rule is not the only way to flag outliers. A second method uses the z-score, the number of standard deviations a value sits from the mean: label anything beyond about three standard deviations an outlier. The two methods usually agree, but they diverge on skewed data. The IQR rule is built from quartiles, which a long right tail barely moves, so it stays stable. The z-score is built from the mean and standard deviation, which a long tail pulls outward, so on right-skewed healthcare data like length of stay or cost the z-score method flags too few high values. Chapter 10 develops the normal distribution and the standard deviation that the z-score depends on; until a distribution is roughly symmetric, prefer the IQR rule. Whichever method is used, retain the Chapter 7 practice: write cleaning as repeatable steps, in Power Query or in formulas, so next month’s export is a refresh rather than a rebuild.

Real-world spotlight

The following is an illustrative scenario, not a specific organization. A team reported that average patient wait time had dropped sharply, and leadership celebrated. An analyst traced the improvement to its cause. During cleaning, someone had filled the blank wait times with zeros to “get rid of the blanks.” Those blanks were the no-shows and cancellations, patients who never waited at all, and adding thousands of false zeros dragged the average down. The wait had not improved; the data had been corrupted by the fill. Once the structural blanks were restored to empty, the real average returned, and it was higher than the number in the celebrated report.

  • Cleaning makes data reliable, not merely tidy; a neat file can still be wrong, and a file full of blanks can be correct.
  • Structural missing means “not applicable” and must stay blank; true missing is a real gap that needs a documented decision, never a silent fill.
  • Profile each field with counts and summary statistics first, to surface impossible values, heaps, and gaps before you compute.
  • A short validation battery, row counts, key completeness and uniqueness, domain and range, and referential checks, catches most problems; the 1.5 times IQR rule flags outliers, which sit far from the rest and may be valid or erroneous.
  • Transform the shape of data, never its meaning; keep the raw data traceable and write cleaning as repeatable steps.
  1. Give one healthcare example of structural missing data and one of true missing data, and state how you would handle each.
  2. Name two things profiling a numeric column can reveal that a row count alone cannot, and give the Excel function you would use for each.
  3. Using Figure 7, explain why the 28.7 percent missingness in the visit-time fields is not a data-quality problem.
  4. Write the domain check, in words or an Excel formula, that would catch a no_show_flag value of 2.
  5. The visit-length column has Q1 = 13 and Q3 = 30 minutes. Compute the upper fence under the 1.5 times IQR rule, and state how many minutes a visit must exceed to be flagged an outlier.
  6. A join grows a table from 8,500 to 9,140 rows. Name the two validation checks you would run first and what each would reveal.
  7. Explain why filling blank wait_to_room_minutes with 0 changes the meaning of the data, using the real-world spotlight as your example.

Try It (copay). First test the telehealth idea. Count the blank copays for telehealth and in-person visits, for example =COUNTIFS(Appointments[telehealth_flag], 1, Appointments[copay_usd], "") for telehealth and the matching count for telehealth_flag of 0, then divide each by its group size. Both rates are about 39 percent, essentially equal, so the test rules out telehealth as the explanation. Ruling out one cause does not make the blanks true missing, so profile them by payer_id next, for example =COUNTIFS(Appointments[payer_id], "PAY03", Appointments[copay_usd], "") or a PivotTable of blank copays by payer. Three payers are blank on every row (PAY03 Traditional Medicare, PAY05 Managed Medicaid, and PAY08 Workers Compensation and Other, together 12,687 rows, or 91 percent of the blanks), and self-pay PAY07 is blank on 43 percent. The missingness is therefore largely structural, tied to payer and benefit design rather than to a recording failure, because those plans carry coinsurance or no fixed copay. Leave the structural blanks empty and exclude them from any average copay; treat only the partial PAY07 pattern as a true gap to document and confirm with a data steward.

Practice A (ED quality). The file holds 18000 encounters, and the encounter_id key is complete and unique. The mean length of stay is about 210 minutes and the median is 190, consistent with right skew; the shortest stay is 74 minutes and the longest 1225. The 584 missing lengths of stay match the 584 left-without-being-seen encounters, and the mismatch check returns 0, so that missingness is structural. Triage levels are all valid 1 through 5 and no stay is negative. With Q1 = 165 and Q3 = 227, the fence is 320 minutes, and 780 stays run past it.

Practice B (long stays). The los_over_fence column totals 780. By disposition, Admit has 589 long stays, Observe 135, and Transfer 56, while Home and Eloped have 0; the five add to 780. If the flag totals far more than 780, the fence cell was not locked with Quality!$B$18.

End-of-chapter exercises.

  1. Structural: a cancel_datetime for a completed visit, left blank, which stays blank. True: a blood pressure that should have been taken at a visit but is missing, which needs a documented decision.
  2. Profiling can reveal an impossible minimum or maximum (MIN, MAX) and a silent gap where the recorded-value count is short (COUNTA against the row count); a frequency count (COUNTIF or a PivotTable) can also reveal a heap.
  3. The visit times are blank only for the 10,342 appointments that were not completed, which have no visit to time, so the blank is structural and correct.
  4. =COUNTIFS(Appointments[no_show_flag], "<>0", Appointments[no_show_flag], "<>1"), which counts flags that are neither 0 nor 1 and would return a non-zero result if a 2 were present.
  5. The fence is 30 + 1.5 * (30 - 13) = 55.5, so a visit must exceed 55.5 minutes, that is, run 56 minutes or longer, to be flagged.
  6. Row counts, which reveal that the join added 640 rows, and key completeness and uniqueness, which would reveal whether a blank or duplicated key multiplied the matches.
  7. The blank wait times belong to patients who never waited, so replacing them with 0 adds thousands of false zeros that pull the average wait down, reporting an improvement that did not happen.
  • Broman, K. W., & Woo, K. H. (2018). Data organization in spreadsheets. The American Statistician, 72(1), 2-10. https://doi.org/10.1080/00031305.2017.1375989
  • Little, R. J. A., & Rubin, D. B. (2019). Statistical analysis with missing data (3rd ed.). Wiley.
  • Tukey, J. W. (1977). Exploratory data analysis. Addison-Wesley.
  • van der Loo, M., & de Jonge, E. (2018). Statistical data cleaning with applications in R. Wiley.
Data cleaning
The work of making a dataset reliable and ready for analysis.
Structural missing
A blank that is correct because the value does not apply to that record.
True missing
A blank where a value should exist but was not recorded.
Data profiling
A quick first pass that describes each field’s count, range, and center before analysis.
Validation
The set of checks that confirm the data means what the analyst thinks it means.
Range check
A validation that confirms a field’s values fall within an allowed interval, such as a wait time of zero or more.
Domain check
A validation that confirms a field holds only values from an allowed set, such as a flag of 0 or 1.
Referential integrity
The property that every foreign key resolves to a record in its dimension table.
Outlier
A value far from the bulk of the data, which may be a valid extreme or an error.
Quartile
A value that splits sorted data into quarters; Q1 is the 25th percentile and Q3 the 75th.
Interquartile range (IQR)
The span of the middle half of the data, Q3 minus Q1.
Box plot
A chart that shows the median, the interquartile box, the whiskers, and outliers beyond the fence.
Capping
Replacing a value beyond a fence with the fence value, under a rule stated in advance, while keeping the raw value.
Winsorize
To replace values beyond chosen percentiles with those percentile values, reducing the influence of extremes.
Z-score
The number of standard deviations a value sits from the mean.
Transformation
Reshaping data into the form analysis needs without changing its meaning.
Imputation
Filling a true missing value with an estimated one, as a documented and deliberate choice.
Reproducibility
The property that a cleaning process can be rerun to produce the same result.
Cite this chapter
@incollection{sher2026ch08,
  author = {Sher, Hoshiar J.},
  title = {Cleaning, Transforming, and Validating Data},
  booktitle = {Introduction to Healthcare Data Analytics Using Excel},
  year = {2026},
  doi = {10.5281/zenodo.23327956},
  url = {https://yar177.github.io/intro-to-HDA-site/ch08.html}
}