Introduction
Data is definitely everywhere because almost all the information we receive everyday can be collected and analysed data, but having a dataset is only the beginning of the data analytics process. Before analysing data and drawing conclusions from it, it is important to make sure that the data is accurate, consistent, complete and in the correct format through a process called data cleaning.
During Week 3 of my Data Science and Analytics training at LuxDev Academy, I was introduced to the basics of Microsoft Excel and how it can be used to prepare data for analysis. I used to view Excel as just an application that is used to view spreadsheets, but this week I learnt that it has many useful features for working with real-world datasets. These include sorting, filtering, formatting, removing duplicates, finding and replacing values and using functions such as TRIM, PROPER and CONCAT which are known as Text Functions.
For practice, I decided to work on a synthetic Kenyan M-Pesa transaction dataset generated using Mockaroo. The dataset contains 193 transaction records and includes information such as transaction IDs, transaction dates, sender and receiver names, transaction types, transaction amounts, transaction status, counties and transaction fees.
Even though the dataset was synthetic and small, it contained several inconsistencies that provided an opportunity to practise the data-cleaning concepts I had learned. In this article, I will walk through how I did the cleaning, from importing the dataset into Excel to identifying and correcting formatting, missing values, duplicates and inconsistencies.
1. Generating and Importing the Dataset
I first generated the M-Pesa transactions dataset using Mockaroo and downloaded it as a CSV(Comma Separated Values) file. I chose an M-Pesa dataset because mobile money transactions are really relevant to the Kenyan context as millions of people transact money everyday using M-Pesa and it can contain different types of information that would be useful for data analysis.
After downloading the CSV file, I opened Microsoft Excel. Instead of opening the file directly through the File section, I practised importing the data through the Data option on the ribbon at the top of the blank workbook: 'Data' then 'Get Data' then 'From File' then 'From Text/CSV'. Excel then showed a preview of the data before loading it into the workbook.
It was a very important first step before beginning any cleaning.
This is how the dataset looked like after loading and before I started cleaning.
2. Making a Duplicate Sheet Before Cleaning
Before making any changes to the data, I duplicated the worksheet by right-clicking the sheet tab by copying the whole dataset by first using keyboard shortcut Ctrl+A to select the whole dataset,then Ctrl+C to copy, then added a new sheet and then right-clicked in the top-left cell to paste the dataset . This gave me an untouched version of the original dataset to refer back to, while I carried out all my cleaning on the duplicate sheet.
If I made a mistake while cleaning, I still had the original data intact rather than needing to re-download the dataset or opening it from my file explorer.
Creating a backup copy of the sheet so the original data stayed untouched.
3. Formatting, Sorting and Checking for Duplicates
Once the dataset was loaded, I used 'Home' then 'Format' then 'AutoFit Column Width' so that all the values were completely visible, which made it easier to see the data during cleaning.
I then checked the dataset for duplicate records using 'Data' then 'Data Tools' then 'Remove Duplicates', selecting all columns so that only exact duplicate rows would be removed. Excel returned that no duplicates were found. Even though none were present, the step was important because checking that a potential problem does not exist is as much a part of data cleaning as fixing one that does.
I also applied filters via 'Home' then 'Sort & Filter' then 'Filter', which added drop-down buttons to each column header. This made it much faster to look at individual columns. For example, checking 'County' for blanks or checking 'Transaction Status' and 'Transaction Type' for inconsistent entries without having to scroll through every column.
The Home ribbon tools I used for formatting and creating the drop-downs.
4. Checking and Correcting Data Types
I then went column by column checking whether Excel had interpreted each one correctly, since a value can look correct visually while having the wrong data type.
- Transaction ID - changed from Number to Text, since it is an identifier rather than a value that can be calculated or aggregated.
- Transaction Date - changed from General to a proper Date format (year-month-day), making it possible to sort and support analysis later.
- Sender Name / Receiver Name - already correctly identified as text.
- Sender Phone - converted to Text, since phone numbers are identifiers not quantities that can be aggregated.
- Transaction Type / Transaction Status - already identified as text, but needed further cleaning for consistency.
- Transaction Amount / Transaction Fee - formatted from Number to Currency (KES), since these represent monetary values and needed to clearly show that.
5. Handling Missing Values
Using the drop-downs I had applied earlier, I checked each column for blanks. I found missing values in County, as well as in the numeric fields Transaction Amount and Transaction Fee.
I did not treat all missing values the same way. For the text column, County, I replaced blanks with "Unknown". For the numerical fields, I left the blanks as they were rather than filling in a made-up value as it would have added incorrect information to the dataset rather than fixing it.
This taught me that how you handle a missing value should depend on what the column represents and not a single default rule applied everywhere.
6. Standardising Text with Key Functions
Several columns had inconsistent capitalisation or extra spacing, so this was where most of my actual cleaning work happened:
- Find and Replace (Ctrl + H) - used to correct inconsistent entries in Transaction Type (e.g different casings of "Pay Bill") and Transaction Status (e.g "failed" vs "Failed").
- TRIM - used in a helper column to remove hidden extra spaces from text values that looked correct but weren't matching due to leading spaces.
- PROPER - used in a helper column to standardise capitalisation in Sender Name and Receiver Name (e.g turning "MUTUA KARANJA" into "Mutua Karanja").
-
CONCAT - used to restore the leading zero on Kenyan phone numbers after removing apostrophes and the country-code had left some numbers starting with "1" instead of "0". The formula I used was
=CONCAT("0",D2).
For each of these, I applied the formula to the first cell in a row, used AutoFill to apply it down the column, then used Paste Special for Values only to convert the formula results into permanent values in a new column before deleting the helper columns. This kept my final dataset clean while preserving the original column structure.
For cleaning the sender name column I created a helper column and used the above formulas to clean it.
7. What I Learned From the Cleaning Process
Working on this dataset helped me understand that data cleaning is more than removing duplicates and deleting or filling blank cells.
This is a brief summary of what I learnt:
The importance of understanding the meaning of each column before deciding how to clean it. A phone number consists of digits, but treating it as numeric wouldn't make sense because there's no meaningful total or average of phone numbers. Also, a Transaction ID may contain only numbers but should be treated as text because it's an identifier, not a value to calculate.
Missing values should be handled according to the type and meaning of the data i.e "Unknown" for text fields where appropriate and left blank for numeric fields instead of guessing.
Sorting and filtering are really important tools as they help you find out what's actually in a column before you touch it. In addition, creating a copy of the original sheet before cleaning meant I could correct mistakes without risking the original data.
Excel usually offers more than one way to solve the same problem like in the case of PROPER versus Find and Replace and choosing between them often comes down to the type of inconsistency and personal preference.
Conclusion
My first practical data-cleaning project using Excel gave me a much better understanding of how raw data can be prepared for analysis.
I started with a synthetic Kenyan M-Pesa transactions dataset generated using Mockaroo and imported it into Excel. Before making any changes, I duplicated the sheet to preserve the original data, then worked through the dataset column by column correcting data types, formatting monetary fields as KES currency, converting dates into the correct format, treating identifiers like Transaction ID and phone numbers as text, handling missing values carefully and standardising text using TRIM, PROPER, Find and Replace and CONCAT.
The dataset after cleaning now with consistent formatting and standardized text.
The dataset was not too messy, but that actually made it easier for my first practice exercise. It allowed me to focus on understanding the purpose of each Excel tool rather than becoming overwhelmed by a very complicated dataset.
I learnt that good data cleaning requires both technical Excel skills and an understanding of the data itself. Excel provides the tools, but I still need to make informed decisions about how those tools should be used.
As I continue learning data analytics, I hope to build on these skills and become more confident in using Excel for larger and more complex datasets. Week 3 has given me a strong foundation and I now have a better understanding of how important data cleaning is before carrying out analysis and visualisation.
Top comments (2)
Great work, Stacy.
I like that you did more than just demonstrate Excel features, you explained why certain cleaning decisions matter, especially treating Transaction IDs and phone numbers as text and handling missing values differently depending on the type of data.
Good progress. Keep documenting your learning journey.
Will do,thank you Harun.