Data & Spreadsheets

Spreadsheets, data organization, and analysis workflows.

Showing 13–24 of 33 guides

Read two-way tables by separating overlapping groups, intersections, and totals in a shared-activity example.
8 min read

How to read two-way tables with overlapping groups

Suppose twelve people are invited to two activities held at different times: a reading circle and a puzzle session. You want to know how many attended either activity, yet some people attended both. To read two-way tables accurately, give each person one place in a grid defined by two questions: Did they attend the reading circle? Did they attend the puzzle session? Then add the appropriate cells for the question you are answering. Adding the two activity totals without accounting for shared attendees would count some people twice.

Survey response rates guide to defining the invited and completed counts before calculating a percentage.
9 min read

Calculate survey response rates with clearly defined counts

When a workplace questionnaire closes, three numbers may appear in the same report: people invited, questionnaires returned, and people who answered a particular question. Each number describes a different group. To calculate survey response rates without quietly changing the denominator, name the group you are counting first, then show the fraction beside any percentage. A return percentage uses invitations as its denominator; an answer percentage for an optional item can use returned questionnaires. Neither percentage explains why someone did not participate.

Reading cumulative totals in a progress report means distinguishing the running count from work completed during each interval.
9 min read

Practice reading cumulative totals in a progress report

A progress report might say that 31 handouts had been distributed by 11 a.m. That number describes everything counted up to 11 a.m., not just the work done during the preceding hour. To find an hourly count, you need an earlier observation on the same counting basis. Subtract the earlier running total from the later one, then name the interval those observations actually enclose. If an observation is missing or the counter has restarted, the report may support a wider interval or no reliable difference at all.

Median and mean guide to comparing puzzle completion times without confusing the two measures.
8 min read

Compare median and mean for uneven puzzle completion times

Six fictional puzzle sessions took 8, 9, 10, 10, 11 and 42 minutes. If you want the arithmetic average duration across these recorded sessions, calculate the mean. If you want the middle duration after putting the records in order, calculate the median. Those are different questions, even though they use the same six observations. The 42-minute session makes the answers especially different: the mean is 15 minutes and the median is 10 minutes. Neither figure says how long another session will take. The useful choice is which description fits the sentence you need to write about these six fictional sessions.

Spreadsheet category mapping that reconciles different labels for the same intended category without changing the underlying records.
8 min read

Use spreadsheet category mapping to reconcile inconsistent labels

A category column can contain labels that look different even when the person who entered them meant the same thing. It can also contain similar-looking labels that point to different uses. If you replace both kinds of variation at once, the resulting worksheet may look tidy while concealing a decision nobody actually made. Spreadsheet category mapping starts with a narrower question: what does each original label mean under the categories you want this particular report to distinguish?

Spreadsheet unit checks that identify what each measurement represents before comparing, rounding, or converting values.
8 min read

Use spreadsheet unit checks before comparing measurements

A column labeled “size” can conceal several different measurements. Imagine a fictional worksheet for display boards with entries of 120 cm, 1.4 m, 135, and 90 cm marked as a height. If the question is which board is widest, only the first two entries clearly describe widths in known units. The third has no stated unit, and the fourth describes another dimension. To compare the rows responsibly, establish what each number measures and the unit in which it was recorded. Keep the original entries visible while you make a separate, narrower list of comparable widths.

Spreadsheet transposition guide to checking the source rectangle, preserving row and column labels, and validating the rotated table.
8 min read

Plan spreadsheet transposition before swapping rows and columns

Spreadsheet transposition exchanges the positions of a table's row and column labels. If workshop names currently run down the left and session dates run across the top, the new arrangement can put dates down the left and workshops across the top. Each attendance count still belongs to the same workshop and date. Before changing the layout, draw both arrangements and identify the exact rectangle that contains the labels and counts. That small sketch makes a misplaced value easier to catch than a visual scan of numbers alone.

Spreadsheet rounding guide to checking calculations and presentation before reporting a total.
8 min read

Check spreadsheet rounding before reporting totals

A measurement report can show three entries that add up to one number and a total printed beside them that says something else. Before changing the total to make the page look tidy, find out which values were added. Adding the measurements as recorded and then rounding the result can produce a different displayed total from rounding each entry first and adding those shorter numbers. Spreadsheet rounding is easier to explain when the report says which sequence it uses and retains the original measurements.

Survey question wording revised to separate finding a reference handout from understanding its contents.
9 min read

Revise survey question wording to separate mixed answers

A short feedback form about an internal reference handout can hide a problem in an ordinary-sounding question: “Was the handout easy to find and understand?” A reader might find it quickly but struggle with an instruction. Another might search for it for ages, then understand it immediately. If each can give only one answer, the editor cannot tell which part of the experience that answer describes. Revise the question by deciding what the editor needs to learn, then give each relevant experience its own answer. For survey question wording , verify each step.