Validate, sort, filter, flag

Constrain an input cell so a wrong entry is refused with a reason, sort and filter without separating a record from itself, and build an error-check panel whose lines are measured and can be shown to fail.

Everything in this module so far has been repair. This lesson is about the three things that stop the same repair being needed again next month, and about the one that is most often skipped.

Validation is cheap and immediate: a cell that only accepts a site name will never again contain a typo that costs an afternoon in Module 2 lesson 5. Sorting and filtering are where a clean sheet gets damaged fastest, by an action that takes one click and shows no error. And a check panel is the difference between a sheet that is correct today and a sheet that will tell you when it stops being correct.

The skipped one is the panel, because it is the only one that has no visible payoff on the day you build it. Every line in it reads "fine". That is what a working smoke alarm sounds like too.

Validation constrains an entry before it becomes data. A cell can be limited to a list of allowed values, to a numeric range, or to whole numbers. The point is not that it prevents every mistake — it prevents the specific mistake of putting a value in a cell where that kind of value does not belong, which is the kind that spreads.

Two things make a validation rule useful rather than annoying. It should carry a message that says what is allowed, not just that the entry was refused. And it should be attached to the cells a person types into, not to derived cells, which nobody types into anyway.

Be careful about what a rule implies. A rule that a booked count must be at least 1 also says a session can never run empty, and that is a claim about the world that will be false one day.

Sorting sorts what you select. If you select one column and sort it, that column is reordered and every other column stays where it was, so the names in one column now sit beside somebody else's numbers. Nothing errors. The sheet looks tidier than it did. This is the single most destructive routine action in a spreadsheet, and the defence is to select the whole block, or to use a sort that offers to expand the selection and accept the offer.

Filtering hides rows. It does not remove them, and it does not change any formula. A total over F2:F9 is a total over F2:F9 whether or not four of those rows are on screen. This surprises people constantly, in both directions: they expect a filtered total to shrink and it does not, or they filter, read a total, and report it as a per-site figure when it covers every site.

If you want a per-site figure, ask for one. SUMIF and COUNTIF take the condition as an argument, so the answer is a formula rather than a state of the screen, and it is still right after somebody clears the filter.

A check panel is a small block of cells whose job is to be boring. Each line measures something that ought to be true, and the panel earns its place only if at least one line could report a problem that is not there today. A panel of lines that are true by construction — "rows in the table equals rows in the table" — is decoration.

Useful lines fall into four kinds:

• Counts of a condition you care about: sessions over capacity, rows with no site, negative amounts.
• Reconciliations: a figure computed a second way, minus the first, expected to be zero.
• Coverage: does the panel cover every row, or has the table grown past its ranges?
• Freshness: what date is this data as of?

The coverage line matters more than it looks. Every other line reports on a range, and a range that stopped one row short reports confidently on the wrong data. A coverage line is the one that notices.

Conditional formatting flags a row without changing it. Use it to draw attention, never to carry meaning on its own: a reader who cannot distinguish the colours, or who prints in black and white, gets nothing. In this grid every conditional-format rule writes a short text badge into the cell as well as tinting it, so the flag survives being read aloud, printed, or looked at by somebody who does not know what amber was supposed to mean.

The autumn plan, with two derived columns:

        D          E        F              G
      Capacity   Booked   Spaces left    Status
CR-201   24        18          6          on target
CR-203   24        26         -2          over capacity
CR-206   20         0         20          no bookings

The status column has to test in a particular order, and the order is the whole lesson.

Over capacity is tested first. CR-203 has 26 people in 24 places. Its fill rate is 26 divided by 24, or about 1.08 — comfortably above any target you would set. Test the fill rate first and this session is reported as doing well.

An empty session is tested next. CR-206 has a fill rate of exactly zero, which is genuinely below target, so a formula that goes straight to the comparison reports "below target" and is not wrong. It is just useless: below target sounds like a session that half-filled, and this one had nobody in it.

What is left is worth comparing. Only after those two have been dealt with does the fill rate mean what its name suggests.

Notice also that spaces left is -2, not 0. It would be easy to write =MAX(0,D4-E4) and never see a negative number again. That would also make the over-capacity session invisible in the total, which is precisely the information the total exists to carry.

The eight sessions are on screen. J15 holds =SUM(F2:F9) and reads 40. You now apply a filter that shows only the Cedar sessions, leaving three rows visible.

Predict three things before revealing them.

=SUM(F2:F9)          after filtering to Cedar
=COUNTIF(B2:B9,"Cedar")
=SUMIF(B2:B9,"Cedar",F2:F9)

A first attempt at the status column:

=IF(E2/D2>=$J$2,"on target","below target")

Two sessions are misreported by it, and neither reports an error.

Add the tests in front of it, in the order that matters:

=IF(E2>D2,"over capacity",
   IF(E2=0,"no bookings",
      IF(E2/D2>=$J$2,"on target","below target")))

Read the nesting as a sequence of questions, each asked only if the previous one said no. Is it over capacity? No. Is it empty? No. Then, and only then, is the fill rate worth comparing.

Two details in that formula are worth naming.

>= rather than >. A session at exactly the target has met it. This is the boundary case from Module 3's first lesson arriving early, and it is worth checking on purpose: 18 out of 24 is exactly 0.75, so if your formula reports it as below target, the comparison is one character wrong.

$J$2 rather than 0.75. The target is an assumption, so it lives in a cell. Type it into the formula and it lives in eight cells, and raising the target next term means finding all eight.

Every line of a colleague's check panel reads zero. The panel says there are no problems. There is a session with 26 people booked into 24 places, and the panel has never mentioned it.

Work out why a panel of green lines is not evidence.

1. Read what each line actually measures. A line labelled "sessions over capacity" is only about capacity if its formula compares booked against capacity. If it counts a status column, it is about whatever that column says.
2. Check the ranges before the logic. The commonest cause is a panel written when the table had seven rows. It still covers seven, and the problem is in the eighth. This is what a coverage line is for, and its absence is why nobody noticed.
3. Make a line fail on purpose. Change one figure so that a check should trip, and watch. A line that does not trip is not a check; it is a label. Change it back.
4. Look for a formula that cannot report a problem. =MAX(0,D2-E2) never returns a negative number, so a panel counting negatives over that column will always find none. The check is fine. The column has been made incapable of triggering it.
5. Add the coverage line. Compare the number of rows the panel covers against the number of rows in the table. It is the one line that fails when the table grows, and it fails before anybody uses the number.

The general shape: an alarm that has never gone off is indistinguishable from an alarm that cannot. The only way to tell them apart is to set one off deliberately.

Complete the session in row 9, build the two derived columns, and construct the check panel.

Before you enter the row properly, try entering something the sheet should refuse: a site called Lakeside, a capacity of 200, or a target fill rate of 75 instead of 0.75. Read what comes back. A rule that refuses an entry without saying what is allowed is only half a rule.

Design a check panel for something you actually keep in a spreadsheet: a budget, a reading list, a set of timesheets, a stock count.

Write five lines. For each one, state the formula in words and, more importantly, what would have to go wrong for it to trip. If you cannot answer that second part for a line, cut it: it is decoration.

At least one line must be a coverage check, and at least one must be a reconciliation — a figure computed a second way and subtracted, expected to be zero.

Then do the part everybody skips: break something on a copy, and confirm the panel notices. If it does not, you have learned that today rather than in six months.

Sign in to track your progress on this exercise.

This module is finished when you can do all of these.

• Give one validation rule you would add to a sheet you already keep, and state the claim about the world that rule would be making.
• Explain what happens to the other columns when one column is sorted on its own, and describe how to avoid it.
• Say what a filter does to a SUM over the filtered rows, and name the function you would use to get a per-category total instead.
• Describe the four kinds of line worth putting in a check panel, and say which one notices that the table has grown.
• Explain why a formula that never returns a negative number can disable a check that looks for negatives.
• Say why a conditional-format colour is not enough on its own.

Sign in to track your progress on this exercise.

Constrain the cells people type into, sort and filter whole records rather than columns, ask for a per-category figure instead of reading one off a filtered screen, and build a panel whose lines you have watched fail at least once.

That completes the second module. The data is now trustworthy enough to answer questions with — which is where the next difficulty starts, because most of the wrong answers from here on are not wrong arithmetic. They are the right arithmetic over the wrong group.