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
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
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%.
Clean up and summarize data
Find the latest purchase date for each customer
Group purchase history by customer and find each customer’s most recent purchase date without sorting every order.
Clean up and summarize data
Calculate mean squared error from predictions and actual values
Compare predictions with actual values by averaging squared errors for each model. Count only pairs with both values present.
Clean up and summarize data
Find the most common value in each group, including ties
Count each value within its group, find the highest frequency per group, and join on both fields to keep every tied mode.
Clean up and summarize data
Calculate NPS by group from survey scores of 0 to 10
Map each valid survey score to −1, 0 or 1, then average by group and multiply by 100. Keep passive responses in the denominator.