Clean up and summarize data
Why do 00123 and 123 fail to match in Excel data?
Distinguish text codes from number formatting before joining data. Keep meaningful leading zeros and understand why changing the type cannot restore lost digits.
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 a timestamp to a daily schedule by selecting Date on both sides. Check when ignoring the time is valid and when it creates ambiguous matches.
Clean up and summarize data
Why do identical-looking codes fail to match? Check hidden spaces
Match codes with leading or trailing spaces without rewriting the originals. See what Ignore surrounding spaces fixes and what still needs source cleanup.
Clean up and summarize data
How to handle zero and blank values when calculating percentage change
Use the previous value as the percentage-change baseline. Distinguish a zero baseline, a missing current value and an actual drop to zero.
Clean up and summarize data
How to remove every occurrence of duplicate rows, not keep one
Group by the fields that define a row, count occurrences and keep only counts of one. Exclude every repeated combination without deleting source data.
Clean up and summarize data
How to total amounts by category without repeating SUMIF formulas
Group repeated categories and calculate their totals and record counts together. Check the overall sum and understand how blank amounts affect the result.