Microsoft Excel is a spreadsheet software that allow users to collect,organize, calculate and visualize data efficiently. It is one of the most commonly used tools in data analysis and is useful in many fields, including business, finance, research education,accounting and data science.
As a beginner starting a journey in data analysis.learning Excel is one of the important step to take.Excell provide different tools,formulas and functions that make it easier to work with data.It can be used to enter data,organize it into tables,perform calculations,identify patterns,creat charts,and present information in a simple and understandable way.
However, before analysing any data set,it is important to make sure that the data is clean,accurate, complete,consistent and well organized.
This brings us to an important part of data analysis;data cleaning.
Data cleaning is a process of identifying and correcting errors, inconsistencies, missing informations,dublicate records and unnecessary information in a data set.
Row data can sometimes be messy because it may have been collected from different people,systems,website,forms or databases.During data collection and entry,many mistakes can easily occur.
Some common problems found in raw data include;
- Duplicate records
- Blank or missing values
- Incorrect spellings
- Incorrect data formats
- Extra spaces
- Inconsistent capitalization
- In correct dates
- Numbers stored as text
- Inconsistent categories
- Unnecessary characters For this reasons, data cleaning is an important step before beginning data analysis. Poor quality data can lead to incorrect calculations, misleading visualizations,unreliable results, and poor decision making. A simple way of understanding this is; If the data is wrong, the analysis can also be wrong. Therefore,a data analyst should always inspect and clean a dataset before using it to make conclusion.
**Common Data Cleaning Problems In Excel
1. Duplicate Records
Duplicate records occur when the same information appears more than once in a dataset.
**Customer ID Name Department Sales
101 John Sales 5000
102 Mary Finance 7000
101 john Sales 5000
The record for John appears twice. If the dubblicate is not removed, it may affect the total sales and other calculations.
Excel provides a Remove Duplicates feature that makes it easier to identify and remove duplicate records.
- To remove duplicates;
- Select your dataset.
- Go to the data tab
- Click Remove Duplicates
- Select the columns you want Excel to check
Click OK.
It is always a good practice to keep an original copy of your dataset before removing duplicates.
2. Blank or Missing Values
Missing values occur when some information is not available in a dataset.
For example;**
Name Age Department**
John 25 Sales
Mary Finance
Peter 30 IT
In this example Mary's age is missing
Missing values should be identified and handled carefully.Depending on the dataset,we may decide to leave the value blank,replace it with appropriate value,or remove the record.
The method used depends on why the value is missing and what the data will be used for. We should not simply fill every blank cell with zero because zer0 may represent an actual value rather than missing information.
Excel Formulars Used In Data Cleaning
Excel contains many formulars and functions that make data cleaningeasier. Below are some of the formulas that a beginner will commonly encounter.
= TRIM(A2) Removes extra spaces to text
This is used especially when data has been copied from another source. Extra spaces may appear harmless but they can cause problems when filtering,sorting comparing,or searching for values.
=UPPER(A2) Covert text to upper case
This can be use when standardizing text.
=LOWER(A2) Convert text to lower case. For example;
JOHN SMITH
becomes
john smith
=LEFT(A2,3) Extracts characters from the left
For example;
If A2 contains;
KENYA
the result will be;
KEN
This can be useful when extracting codes or identifying information from a larger text value,
=RIGHT(A2,3)Extract characters from the right
If A2 contains; EMP2026
The result will be;026
=PROPER(A2) Correct words to proper case. This function converts the first later of each word into uppercase.
For example;
john sMITH
Becomes
John Smith
This is useful when cleaning names,adresses and other text information,
As a data analyst when learning excel you will come across some operators and functions that will help you work easily on your data. This operations and functions include;
Addition(+)
Subtraction(-)
Multiplication()
Division(/)
Exponent(^)
In this unit you will also cover things like, aggregrate functions and this include, sum, average, medium,mode,min and maximum.Avery good example is conditional aggregration,examples are;
Countif
Countifs
Averageif
Averageifs
Finally the most interesting topic of all in this unit of excel and data cleaning is date functions. This functions are very important in data cleaning process and date function because they contain informations about when an event happened. Example include;sales date,employee joining dates transaction dates customer registration dates and payment dates.
However, dates can sometimes be stored in inconsistent format.
For example;
01/08/2026
01-08-2026
2026/08/01
August1,2026
An analyst need to ensure that dates are correctly recognized by Excel before performing calculations or analysis.
Some important date functions include;
=TODAY() This returns the current date
This is useful when creating reports that need to display the current date automatically,
=NOW()This returns the current date to time
YEAR() This extracts the year from an date,
=YEAR(A2)
If A2 contains 15/08/2026,the result will be;
2026
MONTH() This extracts the month number from a date.
=MONTH(A2)
DAY() Extract day from a date
=DAY(A2)
This functions are useful when analyzing data according to year, months or days.
For example, if we have a sales dataset covering several years,we can extract the year from each transaction date and then compare sales between different years.
**Other Important Excel Data Cleaning Tools*
Excel formulars are not the only tools used for data cleaning. Excel also provide several features that can make the cleaning process easier.
Find And Replace
The find and replace feature can be used to correct repeated mistakes quickly.
For example, suppose a department column contains;
Human Resource
Human Resources
HR
If this values are intended to represent the same department, we can standardize them using Find and Replace.
The shortcut for Find and Replace is;
Ctrl+H
This can save a lot when working with large dataset.
Text Columns
Text columns is useful when information is combined in one column but needs to be separated into different columns.
For example;
John, Sales,Nairobi
Mary, Finance,Kisumu
Peter,IT,Mombasa
The information can be separated into;
Name Department Location
John Sales Nairobi
Mary Finance Kisumu
Peter IT MombasaTo use text to columns;
Select the column
Go to the Data tab
Click Text to columns
Select the appropriate delimiter, such us a comma
Click Finish
Sorting and Filtering
Sorting and filtering are also important when working with data.
Sorting allow us to arrange data in ascending or descending order.F or example,sales values can be arranged from the lowest to the highest or from the highest to the lowest.
Filtering allows us to display only the records that meet certain conditions.
For example.if a dataset contains several departments, we can filter the Department column to display only the sales department.
Sorting and filtering make it easier to explore a dataset without deleting any information.
Data Vusualization in Excel
After cleaning and analyzing data, the next step is often to present the results using visualization.
Excel provides different types of charts.including;Column charts
Bar charts
Line charts
Pie charts
Area charts
Scatter plots
Charts make it easier to communicate patterns and trends.
For example, a line chart can be used to show how sales change over several months, while a bar chart can compare sales between different departments.
Good visualization helps turn numbers into information that can be easily understood by other people.
A Simple Data Cleaning Process in Excel
When given a new dataset, a beginner can follow these stepsInspect the data
Check for missing values
Remove Duplicates
Clean text
Use functions such as;
=TRIM()
=UPPER()
=LOWER()
=PROPER()
This are used to standardize textCorrect data formats
Standardize categories
Analyze the data
Visualize the results.
Create appropriate charts,tables or Pivot tables to communicate your findings.
Cleaning data is an essentials skill for any one beginning a journey in data analysis and data science. Clean data leads to accurate results and more confident decision making. Data cleaning is particularly important because raw data is often messy.For any beginning a journey in data analysis,mastering Excel skills provides a strong foundation for learning more advanced tools such as SQL.PYTHON.POWER BI and other data analysis technologies.
The more we practice with real datasets,the more confident we become in identifying problems,cleaning informations,analyzing data,and communicating meaningful insights.
Top comments (0)