Live tool
Excel Rescue (open)
Clean spreadsheets: remove duplicates and empty rows, trim spaces, find and replace, reorder columns, compare two files.
Excel
Find help by problem
Type a problem above, or pick one below.
My total is too small or zero
Numbers stored as text are skipped by SUM. Convert them, or check for apostrophes and spaces.
I get #N/A in a lookup
The value is not in the list, or has an extra space. Wrap with IFERROR, and TRIM the data.
I get #DIV/0!
You divided by zero or an empty cell. Use IFERROR or an IF that checks the divisor.
I get #VALUE!
A formula received text where it needed a number, or ranges of different sizes.
I get #NAME?
The function name is misspelled, or your Excel does not have it (for example XLOOKUP in Excel 2016).
Names have extra spaces
TRIM removes leading, trailing and doubled spaces.
Names are in capitals or all small letters
PROPER, UPPER and LOWER change the case.
Split first and last name into two columns
FIND the space, then LEFT and MID.
Count how many times something appears
COUNTIF counts matches. COUNTIFS uses several conditions.
Add up numbers only when a condition is true
SUMIF for one condition, SUMIFS for more.
Find a price from a code
VLOOKUP or XLOOKUP with an exact match.
Check if an item from one list is in another list
MATCH with ISNUMBER, or use the MatchFlow tool for whole files.
Calculate age or years of service
DATEDIF between two dates.
Add months to a date
EDATE moves a date by whole months.
Count working days between dates
NETWORKDAYS skips weekends.
Pass or fail, grades or bands
IF for two outcomes, IFS for several.
Round a number properly
ROUND changes the value, not only how it looks.
Rank people or items
RANK gives a position; LARGE and SMALL give the nth value.
Join text from several cells
The & sign, CONCAT or TEXTJOIN.
Put a date or number inside a sentence
TEXT formats it first.
Work out a loan payment
PMT calculates a regular payment.
Find the highest value that meets a condition
MAXIFS (Excel 2019+), or INDEX with MATCH on the largest value.
No match for that wording.
Try fewer words, such as “duplicates” or “date”, or browse the formula library.
Browse 51 formulas Let’s TalkExcel tools in your browser
Open a file, fix it and download the result. Files are processed on your device.
Live toolNew
CSV and Excel Converter (open)
Convert Excel to CSV and CSV to Excel. Pick the worksheet, separator and UTF-8 marker.
Excel
Live toolNew
Merge Excel Files (open)
Stack several Excel or CSV files into one table. Columns are matched by name and mismatches are reported.
Excel
Live tool
Sheet2App (open)
Turn a spreadsheet into a searchable, editable mini app with filters, summary cards and local storage.
Excel
Live tool
MatchFlow (open)
Compare purchase orders, invoices and goods received lists: missing items, extra items, quantity and price differences.
Business
Live toolNew
Text and List Cleaner (open)
Remove duplicate and blank lines, trim spaces, sort, change case, join or split lists.
Utilities
Formula library
Browse all 51 formulasSUMIFVLOOKUPXLOOKUPCOUNTIFSUMIFTRIMINDEXMATCHDATEDIFIFERRORTEXTJOIN
Learn step by step
All 17 tutorialsBeginner: first formulas
Totals, averages, simple decisions and tidy text. Start here if formulas still feel new.
- Add up a column and find the average
- Mark pass or fail and give a grade
- Total sales by product and region
- Compare your budget with what you spent
- Clean up names and emails pasted in
Intermediate: lookups, dates and summaries
Find things in tables, work with dates, and summarise by group.
- Look up a price from a price list
- Work out commission from sales tiers
- Rank students and find the top scores
- Summarise salaries by department
- Work out years of service and probation dates
Advanced: combine functions
Two-way lookups and nested formulas. Comfortable with lookups first.
- Pick a delivery price from a two-way grid
- Check which items from one list are in another
- Measure marketing campaign results
Examples for your kind of work
HR
- Split a full name into first and last nameLesson. Separate “First Last” into two columns without retyping.
- Work out years of service and probation datesLesson. Calculate how long someone has worked here and when a probation period ends.
- Summarise salaries by departmentLesson. Headcount, average and highest salary for one department.
- AVERAGE: Average salary across the teamFormula. Finds the average (the mean) of the numbers in a range.
- MEDIAN: A typical salary that one very high pay does not skewFormula. Finds the middle value.
- COUNTA: Headcount: how many names are on the listFormula. Counts how many cells are not empty, whatever they contain.
- COUNTIF: How many people work in SalesFormula. Counts the cells that meet one condition, such as equal to a word or bigger than a number.
Finance and accounting
- Find due dates and overdue invoicesLesson. Add payment terms to an invoice date and flag what is late, as of a chosen date.
- Compare loan paymentsLesson. Find the monthly payment on a loan and how much interest you pay in total.
- MIN: Find the smallest invoiceFormula. Returns the smallest number in a range.
- MAX: Find the largest invoiceFormula. Returns the largest number in a range.
- ROUND: Round a calculated amount to 2 decimals before you add it to an invoiceFormula. Rounds a number to the number of decimal places you choose.
- SUMPRODUCT: Value of all stock on hand at costFormula. Multiplies matching cells in two or more ranges, then adds the results.
Sales
- Total sales by product and regionLesson. Add up quantities for one product, one region, and both at once.
- Look up a price from a price listLesson. Type a product code and get the price back, with a friendly message when the code is wrong.
- Work out commission from sales tiersLesson. Find the right commission rate for a sales total using a sorted table, then calculate the amount.
- MAX: Find the biggest single order quantityFormula. Returns the largest number in a range.
- SUMPRODUCT: Total revenue without a helper columnFormula. Multiplies matching cells in two or more ranges, then adds the results.
- LARGE: The top three order sizesFormula. Returns the 1st, 2nd, 3rd.
- RANK: Rank an order by quantityFormula. Tells you the position of a number in a list: 1 for the highest, 2 for the next, and so on.
Education
- Mark pass or fail and give a gradeLesson. Turn a score into Pass or Fail, then into a letter grade.
- Rank students and find the top scoresLesson. Find who is first, who is second, and where each student stands.
- AVERAGE: Class average for ScienceFormula. Finds the average (the mean) of the numbers in a range.
- MIN: Find the weakest result in a subjectFormula. Returns the smallest number in a range.
- LARGE: Find the runner-up scoreFormula. Returns the 1st, 2nd, 3rd.
- SMALL: Find who needs support: the second lowest resultFormula. Returns the 1st, 2nd, 3rd.
Administration
- Clean up names and emails pasted inLesson. Remove extra spaces and fix capital letters in a messy contact list.
- INT: Whole weeks in a number of daysFormula. Rounds a number down to the nearest whole number.
- COUNT: How many amounts have been filled in?Formula. Counts how many cells contain numbers.
- COUNTBLANK: How many forms are still missing a value?Formula. Counts the empty cells in a range.
- LEFT: Take the first letters of a product codeFormula. Takes a number of characters from the start of some text.
Marketing
- Measure marketing campaign resultsLesson. Cost per lead, conversion rate and return for each campaign.
- IFERROR: Conversion rate when a campaign has no leads yetFormula. Shows a friendly result instead of an error such as #N/A or #DIV/0!.
- LOWER: Make a campaign tag lower caseFormula. Changes text to small letters.
Operations
- Flag stock to reorder and value your inventoryLesson. Spot items at or below their reorder level and find the value of what you hold.
- Check which items from one list are in anotherLesson. Find which ordered items arrived and which are missing.
- SUM: Count the units on hand across several productsFormula. Adds up numbers.
- INT: How many full boxes of 12 can you make from the stock?Formula. Rounds a number down to the nearest whole number.
- SMALL: The two lowest stock levelsFormula. Returns the 1st, 2nd, 3rd.
- COUNTA: How many SKUs are listedFormula. Counts how many cells are not empty, whatever they contain.
Personal budgeting
- Compare your budget with what you spentLesson. See where you are over plan and by how much.
- SUM: Add up everything you actually spent this monthFormula. Adds up numbers.
- ROUND: Show a weekly allowance as a whole numberFormula. Rounds a number to the number of decimal places you choose.
- SUMIF: Total what you spent on categories that went over planFormula. Adds up the numbers in one range when another range meets a condition.
- PMT: What a car loan will cost each monthFormula. Works out the regular payment on a loan with a fixed interest rate.
General office
- Add up a column and find the averageLesson. Total a column of quantities, then find the average order size.
- Pick a delivery price from a two-way gridLesson. Look up a price by weight and zone in one formula.
- MATCH: Find the position of a headingFormula. Tells you the position of a value in a list.
- INDEX: Two-way lookup in a price gridFormula. Returns the value at a given row (and column) of a range.
- LEN: Spot hidden spaces: compare LEN with LEN(TRIM())Formula. Counts the characters in some text, including spaces.
- TRIM: Make text match before using VLOOKUPFormula. Removes extra spaces from text: leading and trailing spaces, and doubled spaces between words.
Sample files to practise with
Every file is fictional: invented names, products and numbers. Use them to follow a lesson or test a formula.
- Office supplies sales (fictional)8 rows, 5 columnsCSV
- Team list (fictional)7 rows, 5 columnsCSV
- Class scores (fictional)5 rows, 4 columnsCSV
- Monthly budget (fictional)6 rows, 3 columnsCSV
- Price list (fictional)5 rows, 3 columnsCSV
- Contact list pasted from an email (fictional)4 rows, 2 columnsCSV
- Stock levels (fictional)5 rows, 5 columnsCSV
- Marketing campaigns (fictional)4 rows, 5 columnsCSV
- Customer invoices (fictional)5 rows, 5 columnsCSV
- Commission tiers (fictional)4 rows, 2 columnsCSV
- Delivery price by zone and weight (fictional)4 rows, 4 columnsCSV
- List A and list B (fictional)5 rows, 2 columnsCSV
Need Something Different? Let’s Talk.
Looking for different features or something built around your workflow? Share your idea with us.
About the examples: every result on these pages is calculated by BDK’s own practice calculator, which covers about 60 common functions and follows Excel’s rules for them. It is not Excel itself, so check unusual cases in Excel. Dates are shown as year-month-day.