Back

Session 13: Conditional Formatting & Data Quality

divider

Session 13

Conditional Formatting & Data Quality

Last Session's Question

Which weight's trials did you think were least consistent?

Today, let's stop guessing and make Sheets tell us.

One More Value: Spread

Max minus Min, for a row.

One More Value: Spread

How much your three trials disagreed with each other — a single number you can actually compare across rows.

Worked Example

If Max is in F2 and Min is in E2:

Worked Example

=F2-E2

Conditional Formatting

A rule that changes how a cell looks, automatically, based on its value.

Setting Up the Rule

  1. Select your Spread column
  2. Format → Conditional formatting
  3. Format cells if… Greater than
  4. Enter your threshold value

Choosing a Threshold

This is a judgment call — the same kind you made back in Session 9 with tolerance.

Too tight, and normal variation gets flagged constantly. Too loose, and real problems slip through.

What a Flag Means

Not automatically bad data.

What a Flag Means

A prompt to look closer — the same "diagnostic, not a verdict" idea you'll see again with R² next session.

Two Things to Remember

A flag is a question, not a verdict.

Look at the flagged row and decide: measurement noise, or something else?

Two Things to Remember

Conditional formatting only works if the formula feeding it is right.

A wrong Spread formula will confidently highlight the wrong things.

Next Time

Session 14: Charting & Trendlines

Time to turn all this structured, checked data into a plot.

'F' → Fullscreen

divider

Build

Step 1: Add a Spread Column

Using your Session 12 Min and Max columns, add a new column that calculates Max − Min for each row.

Step 2: Choose a Threshold

Decide, as a class or with your instructor, what counts as "too much" spread for this experiment. Write down the number you land on and one sentence for why.

Step 3: Apply Conditional Formatting

Select your Spread column and set up a rule that highlights any value greater than your threshold.

[GIF PLACEHOLDER — Setting Up a Conditional Formatting Rule]

Step 4: Investigate Any Flagged Row

For any row your rule highlights, write one sentence: do you think this is measurement noise, or something real about that weight? If nothing gets flagged, write one sentence on whether that surprises you.

Stretch (Optional): Flag the Actual Trial

Instead of flagging the whole row, try a custom formulaconditional formatting rule that highlights just the one trial cell furthest from that row's average — ask your instructor if you want to attempt this.

divider

Checkpoint

Before you move on, confirm all of the following:

  • Your Spread column uses a formula, not typed-in numbers
  • Your conditional formatting rule is actually applied and visibly working
  • Any flagged row has a written note explaining your read on it
divider

Reflection

Answer the following questions before submitting your work.

  1. What threshold did you land on for "too much spread," and why that number?
  2. For any row that got flagged, do you think it's measurement noise or something real? How would you actually find out for sure?
  3. Session 14 turns this data into a plot. Do you expect a flagged row to stand out as an outlier on the scatter plot too?
divider

Submit

Submit your Spread column, your conditional formatting rule, and your reflection.

Activity Complete