OmniDocExcel Formulas for Data Analysis and Data Cleaning
Learn which Excel formulas clean raw data and which summarise it, with steps, example formulas, and checks to confirm your totals are right.
Raw data rarely arrives ready to analyse. Exports from forms, scanned tables and copied web pages often contain extra spaces, numbers stored as text, duplicate rows, blank cells and inconsistent labels like "North", "north " and "N.". If you summarise that data as it is, your pivot totals and averages will quietly be wrong. Excel formulas for data analysis solve this problem in two stages: first you clean the data, then you summarise it.
This guide is for people who have a raw data set in a spreadsheet, or a table pulled from a PDF, and need reliable counts and totals without rebuilding the file from scratch. It skips financial modelling and beginner basics. Instead, it focuses on the formulas that do the cleaning and summarising work.
You will get a short list of the formulas to use, a checklist to run before you begin, a step-by-step method that works in any recent version of Excel, and a table of checks to confirm the result. Every formula here works with helper columns, so your original data stays untouched.
Which Excel formulas for data analysis fit raw data? The short answer
Use text-cleaning formulas first, then conditional summary formulas, and check each stage against the source. Keep each step in its own helper column so you can trace any number back to the original value.
| Your situation | What to do | Why |
|---|---|---|
| Names or codes have stray spaces or line breaks | Wrap the cell in TRIM and CLEAN, for example =TRIM(CLEAN(A2)) | TRIM removes extra spaces and CLEAN removes nonprinting characters, so lookups and counts match correctly. |
| Numbers look right but won't add up | Convert with VALUE, wrapped in IFERROR | Text that looks like a number is ignored by SUM, so converting it makes totals complete. |
| The same record appears more than once | Flag repeats with COUNTIF | A count above 1 shows which rows repeat, so you can review them before deleting anything. |
| Blank cells would skew averages | Mark them with IF and LEN(TRIM()) | A visible label such as "Missing" makes gaps easy to filter and count. |
| You need totals by category and condition | Use SUMIFS, COUNTIFS or AVERAGEIFS | These formulas apply several criteria at once, so you do not need separate filtered copies. |
Before you start
Check these items first. Each one prevents a common error that is hard to spot once the summary formulas are in place.
- Keep a copy of the original data. Cleaning formulas can be overwritten by accident, and a clean copy lets you compare results. Put the raw data on its own sheet and leave it alone.
- Confirm the header row and the data range. Know which row holds column names and where the last record ends. Formulas that point at a fixed range, such as A2:A500, will miss rows added below that range.
- Decide what a blank means. A blank can mean "not applicable", "unknown" or "zero". Decide this before you write averages, because blanks and zeros affect results differently.
- Check your date and decimal settings. A date like 04/05/2024 can mean April 5 or May 4, depending on regional settings. Look at a few values before you convert them.
- Check your Excel version. Functions such as XLOOKUP, UNIQUE and FILTER appear only in newer versions. If your version lacks them, use INDEX with MATCH, which works everywhere.
- If the source is a PDF, check whether it is scanned. To see whether a PDF is scanned, try selecting a line of text. If you cannot select it, the page is an image and needs OCR before you can clean it.
Cleaning and summarising data in Excel, step by step
- Copy the raw data to a new sheet. Paste it as values, so no links point back to the original. Name the sheet something like "Raw" so you do not confuse it with the working sheet.
- Clean text in helper columns. In the cell beside the first messy value, enter =TRIM(CLEAN(A2)) and fill it down the column. Use PROPER(TRIM(A2)) when names need consistent capitals, such as "north" becoming "North".
- Convert numbers and dates stored as text. Enter =IFERROR(VALUE(TRIM(C2)),"") and fill it down. The IFERROR wrapper leaves a blank instead of an error when a cell holds words rather than numbers, so you can review those rows separately.
- Flag duplicates before removing anything. In a new column, enter =COUNTIF(A:A,A2)>1 and fill it down. TRUE marks a value that appears more than once. Filter for TRUE, compare the rows, and then decide which copies to keep.
- Mark missing values. Enter =IF(LEN(TRIM(D2))=0,"Missing",D2) and fill it down. This turns empty cells into a visible label, and COUNTIF(range,"Missing") then gives you the number of gaps in that column.
- Standardise categories and build summaries. Use SUBSTITUTE to fix recurring errors, for example =SUBSTITUTE(B2,"N.","North"), then summarise with formulas such as =SUMIFS(Clean!E:E,Clean!B:B,"North") for a total, =COUNTIFS(Clean!B:B,"North",Clean!E:E,">100") for a conditional count, and =AVERAGEIFS(Clean!E:E,Clean!B:B,"North") for an average. Replace the sheet and column names with your own.
- Bring PDF tables into Excel if needed. If your data is in a PDF, run OCR on scanned pages in the PDF editor first, then export the table to Excel and start at step 1. Check the exported values, because OCR can misread digits and letters, and those errors will carry into every formula.
Check the result
Run these checks before you share the summary. They take a few minutes and catch the errors that summary formulas hide.
| Check | How to see it | If it's wrong |
|---|---|---|
| Row count | Compare COUNTA of the cleaned column with the raw column | If the cleaned count is lower, check whether you removed rows by mistake, or whether IFERROR blanked valid values. |
| Totals | Compare SUM of the converted column with a total you calculate by hand on a small sample | If the totals differ, look for numbers still stored as text, or for ranges that stop before the last row. |
| Errors | Filter the helper columns for #VALUE! or #N/A | Fix the source value or add IFERROR, then rerun the filter to confirm no errors remain. |
| Duplicates | Count TRUE values in the duplicate flag column | If the count is unexpected, check for trailing spaces that TRIM did not catch, or for IDs that differ only by case. |
| Dates | Sort a few converted dates and compare them with the source | If day and month are swapped, check your regional date settings and convert the values again. |
Machine-assisted steps such as OCR and automatic translation can introduce errors, so check any figures that matter before you rely on them.
📊 At a glance
Cleaning and summarising use different formulas, and each stage has a different job. The table below compares them so you can see where each group belongs in your workbook.
| Stage | Formulas to use | What you get |
|---|---|---|
| Cleaning | TRIM, CLEAN, PROPER, VALUE, IF, SUBSTITUTE | Consistent text and numbers in helper columns |
| Summarising | SUMIFS, COUNTIFS, AVERAGEIFS | Totals, counts and averages by category and condition |
| Checking | COUNTA, SUM, COUNTIF with "Missing" | Row counts, control totals and gap counts to compare with the source |
Keep the cleaning and summary formulas in separate columns or sheets, so a summary never depends on a raw value you have not yet checked.
❓ FAQ
1. Should I delete duplicate rows or just flag them?
Flag them first. A flag lets you see which rows repeat and decide whether they are true duplicates or two separate records with the same name. Delete only after you have reviewed the flagged rows.
2. Why does SUM ignore some of my numbers?
Those numbers are probably stored as text, often because they were imported with a leading apostrophe, a non-breaking space or a currency symbol. Convert them with VALUE after cleaning with TRIM and CLEAN, then sum the converted column.
3. Can I use one formula to clean an entire column?
You can fill a single formula down a column, but you still need to check the results. A formula cannot tell that "N/A" means missing in one column and a valid code in another, so review the output before you replace the original values.
4. What if my data has more rows next week?
Use a larger fixed range, such as A2:A5000, or convert the data to a table so ranges expand automatically. Either approach keeps the summary formulas current when new rows arrive.
📝 Summary
Clean raw data in helper columns with TRIM, CLEAN, VALUE and IF, flag duplicates with COUNTIF, and then summarise with SUMIFS, COUNTIFS and AVERAGEIFS. Keep the original data untouched and check row counts, totals and errors before you share the workbook.
