Clean up and summarize data
Should uppercase and lowercase product codes match?
Decide whether ABC and abc identify the same item before joining. Compare case-sensitive matching with Ignore letter case while preserving original 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
How to count unique customers by region without counting repeat orders
Group by region and customer ID, then count those combinations by region. Separate order counts, regional customer counts and company-wide unique customers.
Clean up and summarize data
How to count how many distinct values appear more than once
Count repeated codes once each, rather than counting duplicate rows. Summarize by code, filter counts above one, then count the remaining codes.
Clean up and summarize data
Why does joining two Excel tables create extra rows?
Two orders and three addresses for one customer produce six matching pairs. Check repeated join keys before deleting rows that may contain valid information.
Clean up and summarize data
How to filter categories by their total, not individual amounts
Summarize amounts before applying a total threshold. Keep accounts totaling at least 100000 even when every individual transaction is smaller.
Clean up and summarize data
How to calculate minimum, maximum and average by group
Put a team's minimum, maximum and average processing times in one summary. Understand why blank records do not count as zero in the average.