Clean up and summarize data
How to keep 00123 and 123 as separate product codes
Keep product codes such as 00123 separate from 123 when the leading zeros matter. Check the stored values and choose the right type before joining files.
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 match dates when one Excel column also contains a time
Join time-stamped activity records to a daily schedule. Match on the date alone when everyone working that day shares the same assignment.
Clean up and summarize data
Why do identical-looking codes fail to match? Check hidden spaces
Join codes that fail to match because of spaces before or after the value. Keep the original text and check any remaining mismatches.
Clean up and summarize data
How to handle zero and blank values when calculating percentage change
Calculate percentage increases and decreases, and check why a blank result differs from no change or a drop to zero.
Clean up and summarize data
How to remove every occurrence of duplicate rows, not keep one
Keep only records that appear once, removing every copy of a repeated row from the result. Count matching combinations and filter for a count of one.
Clean up and summarize data
How to total amounts by category without repeating SUMIF formulas
Turn a detailed list into totals by customer, project or category. Calculate each group's amount and record count together, then check them against the source.