Clean up and summarize data
How many orders contain each pair of products?
Find which products appear together in orders. Count each pair once per order, even when an order lists the same product several times.
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.
Join data
Generate every pair from two lists
Create every color and size combination by joining on a column filled with the same value. Two colors and three sizes produce six pairs.
Clean up and summarize data
How to import a semicolon-separated CSV into separate columns
Import the CSV file directly and check its field boundaries. A semicolon inside a quoted value should remain in that cell.
Clean up and summarize data
How to import a table that starts below the report title
Choose the first header cell, such as B4, to exclude titles and blank space. Check the header row, range and first data row before continuing.
Clean up and summarize data
Calculate free-shipping and return percentages for each product category
Average each feature within a category, then multiply by 100. Overlapping features do not need to add up to 100%.
Join data
Join different code systems through a mapping table
Join sales codes to a mapping table, then add product descriptions from the catalog without losing unmapped sales.