Clean up and summarize data
Sort ISO timestamps with offsets in actual time order
Put events from different time zones in chronological order. Convert timestamps with explicit offsets to Date & time and sort the UTC values.
Clean up and summarize data
Match customer lists, find missing values, and combine spreadsheets. See how to solve everyday Excel problems with worked examples in JOIN SHEET.
Clean up and summarize data
Why does 10 sort before 2? Convert numeric text before sorting
Set quantity columns to Number so values sort as 1, 2, 10 instead of 1, 10, 2. Keep codes and identifiers as Text.
Clean up and summarize data
How do you calculate 20% off followed by another 10%?
Calculate one discount after another using the reduced price at each step. Check why 20% off followed by 10% off is not the same as 30% off.
Clean up and summarize data
Add up group averages with two summary steps
Calculate each machine’s average load for each day, then add those averages to get a daily total without counting one machine’s readings repeatedly.
Clean up and summarize data
Find daily debit and credit differences when overall totals agree
Find dates whose debit and credit totals differ, even when the overall ledger balances. Sum by date, subtract and filter out zero differences.
Clean up and summarize data
Calculate variance within each group
Join each group's mean back to its observations and summarize squared deviations. Compare population and sample variance, including the one-observation case.