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.
Make spreadsheet work simpler
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 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.
Clean up and summarize data
How to calculate a weighted average price when quantities differ
Find what you paid per unit when purchase quantities and prices differ. Calculate each product's total cost and divide it by the quantity purchased.