Financial Management and Business Data Analytics · Data Analysis and Modelling

Data Preparation and Cleaning in Business Data Analytics

Updated 10 October 2026 · Fact-checked

Data preparation is the process of collecting raw data and making it fit for analysis. Cleaning is its core step: you find and fix missing values, duplicates, errors and outliers. To solve a question, identify the problem in the data, choose a suitable treatment, apply it, then validate the result.

Understand Data Preparation and Cleaning

Raw business data is rarely ready to use. It comes from many places: accounting software, spreadsheets, surveys, bank files, websites. Each source has its own format and its own errors. If you analyse such data directly, your results will be wrong, however good your technique is. This is the idea of garbage in, garbage out.

Data preparation is the full process of turning raw data into analysis-ready data. The usual stages are: collect the data, clean it, transform it, integrate it from different sources, and validate it. You may see slightly different names for these stages, but the order of thinking stays the same.

Data cleaning deals with quality problems. The common ones are:

Missing values: blank cells, such as a customer with no age recorded.
Duplicates: the same record entered twice, such as one invoice listed two times.
Inconsistent entries: "Mumbai", "mumbai" and "Bombay" for the same city, or dates in different formats.
Errors: wrong data types, typing mistakes, impossible values such as a negative quantity sold.
Outliers: values far away from the rest, which may be genuine or may be mistakes.

Missing values can be handled in two broad ways. You can delete the record (or the column if most of it is empty), or you can impute, which means filling the gap with a reasonable value such as the mean, median or mode. Deletion is simple but loses information. Imputation keeps the record but adds an estimate. The median is safer than the mean when the data has outliers. The mode suits categories such as city or product type.

Outliers need judgement. First check whether the value is an entry error, such as ₹5,00,000 typed instead of ₹50,000. If so, correct it. If it is a genuine but extreme value, you may keep it, cap it, or analyse it separately. Do not delete an outlier only because it is large.

Transformation changes the form of data: converting text to numbers, standardising dates, grouping ages into bands, scaling values to a common range, or creating new fields such as profit = revenue − cost. Validation checks that the final data is accurate, complete and consistent, for example through range checks, format checks and totals that agree with the source.

Key rules to remember

Mean imputation: Mean = Σx ÷ n (over available values only); Divide by the count of values present, not the total number of records. Use when data has no strong outliers.
Median imputation: Median = middle value of the sorted data (average of the two middle values if n is even); Preferred when data is skewed or has outliers.
Mode imputation: Mode = most frequent value; Used for categorical data such as city or product category.
IQR rule for outliers: IQR = Q3 − Q1; lower fence = Q1 − 1.5 × IQR; upper fence = Q3 + 1.5 × IQR; A value outside the fences is flagged as a possible outlier. It is a screening rule, not proof of an error.
Min-max scaling: x′ = (x − min) ÷ (max − min); Rescales values to the range 0 to 1.
Missing-value percentage: Missing % = (Number of missing values ÷ Total records) × 100; Helps decide between deleting and imputing.

How to solve Data Preparation and Cleaning questions

Use this order for any question on preparing or cleaning data. It also gives you a structure for written answers.

1Read the data or scenario and name its source and purpose. State what the analysis is meant to do.
2Inspect the data and list the problems: missing values, duplicates, inconsistent formats, errors, outliers.
3For each problem, pick a treatment and give a reason. For example, use the median because the data has an extreme value.
4Apply the treatment. If numbers are given, show the calculation (mean, median, fences) clearly.
5Transform the data where needed: standardise formats, convert types, create or scale fields.
6Validate the cleaned data with checks such as range checks, totals agreeing with the source, and no remaining blanks or duplicates.
7Document what you changed, so the work can be repeated and checked, and state the effect on the analysis.

Quickest way: Problem, treatment, reason

When to use it: Use for theory questions and MCQs asking which technique suits a given data problem.

Match the problem to its standard treatment: blanks to delete or impute, duplicates to remove, wrong formats to standardise, extreme values to check then cap or keep.
Pick the measure by data type: numbers without outliers use the mean, skewed numbers use the median, categories use the mode.
For outlier numbers, compute Q1, Q3 and IQR, then the two fences. Compare each value to the fences.
Add one line of reason and one line of validation. This is where step marks are earned.

Common mistakes in Data Preparation and Cleaning

Deleting every record that has a missing value.
Deletion looks like the easiest fix.
Fix: Check the missing percentage first. If few records are affected, deletion is fine. If many are, impute, otherwise you lose too much data.
Using the mean to fill gaps when the data has extreme values.
The mean is the first average students learn.
Fix: Use the median for skewed data. State the reason in your answer.
Removing outliers automatically.
Students treat outliers as always wrong.
Fix: First check whether it is an entry error or a genuine value. Remove or correct it only with a reason; otherwise keep, cap or analyse separately.
Calculating the mean by dividing by total records instead of available values.
The blank cells are counted in n by habit.
Fix: Add only the values present and divide by how many there are.
Confusing data cleaning with data validation.
Both deal with data quality.
Fix: Cleaning corrects problems. Validation checks that data meets rules (range, format, completeness) and confirms the cleaning worked.
Skipping the sorting step when finding quartiles or the median.
Students rush and use the data in the given order.
Fix: Always arrange values in ascending order before finding the median or quartiles.

Worked examples

Example 1

A firm's monthly sales (in ₹ thousands) for seven branches are 42, 45, 40, 44, blank, 43, 41. The blank is a missing value. Fill it using the mean of the available values and state the new average of all seven branches.

Show the solution

Available values: 42, 45, 40, 44, 43, 41. Count = 6.
Sum = 42 + 45 + 40 + 44 + 43 + 41 = 255.
Mean = 255 ÷ 6 = 42.5.
Impute the blank with 42.5.
New total = 255 + 42.5 = 297.5; average of seven = 297.5 ÷ 7 = 42.5.
The average stays 42.5. Mean imputation does not change the mean, though it understates the spread of the data.

Answer: The missing value is filled with ₹42.5 thousand (₹42,500), and the average remains ₹42.5 thousand.

Example 2

Daily expense claims (₹) of a sales team are: 800, 900, 950, 1,000, 1,050, 1,100, 1,150, 6,000. Using the IQR rule, identify any outlier. Use the median-of-halves method for quartiles. Suggest how to treat it.

Show the solution

Data is already sorted. n = 8.
Lower half: 800, 900, 950, 1,000. Q1 = (900 + 950) ÷ 2 = 925.
Upper half: 1,050, 1,100, 1,150, 6,000. Q3 = (1,100 + 1,150) ÷ 2 = 1,125.
IQR = 1,125 − 925 = 200.
Lower fence = 925 − 1.5 × 200 = 625. Upper fence = 1,125 + 1.5 × 200 = 1,425.
6,000 is above 1,425, so it is flagged as an outlier. No other value is outside 625 to 1,425.
Treatment: check the claim against the bill. If it is a typing error (for example ₹600 entered as ₹6,000), correct it. If it is genuine, keep it but analyse it separately or use the median for summaries.

Answer: The claim of ₹6,000 is an outlier (fences are ₹625 and ₹1,425). Verify it before correcting, capping or retaining it.

Exam tips

In MCQs, match the technique to the data type: median for skewed numbers, mode for categories, deletion only when few records are affected.
Write written answers in the order: identify problem, treatment, reason, validation. Each part can carry a mark.
Always show the sorted list and the quartile working in outlier questions. Method marks are lost when only fences are written.
Use the words in the syllabus: cleaning, transformation, integration, validation, imputation. Define each in one line.
Remember that an outlier is flagged, not automatically wrong. Say that you would verify it.

Practice questions from Data Analysis and Modelling

Data Preparation and Cleaning in other exams

The same ground in other exams, if you are preparing for more than one or want another angle on it.

CFA Level IData Types and Data Preparation

Data Preparation and Cleaning: frequently asked questions

What are the main steps of data preparation?

The usual steps are collecting data, cleaning it, transforming it, integrating data from different sources and validating the result. Cleaning removes errors, duplicates and gaps. Transformation changes the form of data, and validation confirms it is ready for analysis.

How do I handle missing values in data?

First find how many values are missing. If few, you can delete those records. Otherwise impute using the mean, median or mode, choosing by data type and spread. Median suits skewed data and mode suits categories.

How do I detect outliers?

A common method is the IQR rule. Compute Q1, Q3 and IQR, then find the fences at Q1 − 1.5 × IQR and Q3 + 1.5 × IQR. Values outside the fences are possible outliers and should be checked before any action.

What is the difference between data cleaning and data validation?

Cleaning fixes problems such as blanks, duplicates and wrong formats. Validation checks that the data follows rules like valid ranges, formats and completeness. Validation is also used to confirm that cleaning worked.