Introduction
When I first started learning data analytics, I thought working with data was mainly about creating charts, finding patterns and presenting insights.
I quickly realised there is an important step that comes before all that: making sure the data is actually ready to be analysed.
Raw datasets can contain missing values, duplicate records, inconsistent text, incorrect data types, and formatting problems. If these issues are ignored, they can affect the results of the analysis and eventually lead to misleading conclusions.
During my Excel training at LuxDevHQ, I was introduced to some of the tools and techniques Excel provides for preparing data for analysis. This included sorting and filtering, formatting, handling missing values, removing duplicates and text functions such as TRIM, PROPER and CONCAT.
For my practice, I worked with a synthetic dataset that allowed me to simulate a real-world data-cleaning task. Rather than jumping into analysis, I focused on understanding the dataset first and gradually transforming it into a cleaner and more consistent version.
In this article, I'll share my process, the Excel tools I used, and some of the lessons I picked up along the way.
1. Starting With the Raw Dataset
The first step in any data analysis project is understanding what you're working with.
I imported my dataset into Excel and took some time to look through the columns and rows before making any changes.
At this stage, I wasn't trying to fix anything yet. I wanted to understand:
- What information does each column contain?
- Which columns contain numbers?
- Which contain text?
- Are there dates?
- Are there missing values?
- Are there values that look inconsistent?
- Could any columns have duplicates?
This simple inspection helped me realise that data cleaning shouldn't begin with immediately changing things. You first need to understand what the data represents.
2. Creating a Safe Copy
One thing I learned early was the importance of protecting the original dataset.
Instead of working directly on my original data, I created a duplicate worksheet and used the copy for my cleaning process.
This gave me a backup that I could return to whenever I made a mistake.
It might seem like a small step, but when you're experimenting with Excel for the first time, it's easy to accidentally delete a column, change a value or apply formatting to the wrong range.
Having an untouched copy made the process much safer.
3. Making the Dataset Easier to Work With
Before looking for errors, I first made the spreadsheet easier to read.
I adjusted the column widths using Excel's AutoFit feature so that the values and column headings were visible.
I also converted the dataset into an Excel table.
This was useful because tables make it easier to:
- Filter information
- Sort records
- Navigate through large datasets
- Keep formatting consistent
- Work with formulas
I then enabled filters on the columns so I could inspect specific categories instead of manually going through every row.
For example, filtering a column allowed me to quickly identify blank cells or check whether the same category had been entered in different ways.
4. Checking Data Types
One of the most interesting things I learned was that how a value looks isn't always the same as how Excel understands it.
A column containing numbers doesn't necessarily mean it should be treated as a numerical field.
For example, an ID such as:
10001
may look like a number, but if it is simply identifying a transaction or customer, there is no reason to calculate an average or total from it.
The same applies to phone numbers.
I therefore checked my columns and made sure they were using appropriate formats.
Some of the common data types I worked with included:
| Data | Appropriate Format |
|---|---|
| Transaction/Customer ID | Text |
| Names | Text |
| Phone Numbers | Text |
| Dates | Date |
| Quantity | Number |
| Prices/Revenue | Currency or Number |
| Categories | Text |
This was a good reminder that data types should be based on what the data means, not just what it looks like.
5. Looking for Missing Data
Next, I checked the dataset for missing values.
Missing data is not automatically an error. Sometimes a value is genuinely unavailable.
The important thing is deciding what to do with the missing value based on the context.
For example, if a category such as location was missing, I could use a value such as "Unknown" where appropriate.
However, I wouldn't randomly replace a missing numerical value with zero.
A blank amount and an amount of zero do not necessarily mean the same thing.
This made me realise that data cleaning isn't simply about making every cell look complete. It's about making appropriate decisions about what each value represents.
6. Finding Duplicates
Another important check was identifying duplicate records.
I used Excel's
Data → Remove Duplicates
feature to check whether the dataset contained repeated records.
Even when duplicates aren't found performing the check is still useful.
It confirms that you've considered one of the common problems that can affect analysis.
For example, if the same transaction appeared twice and I calculated total revenue without noticing it the final result could be higher than the actual revenue.
This showed me why data validation should happen before analysis.
7. Cleaning Inconsistent Text
Text data can look simple, but it can create some interesting problems.
For example, Excel may treat these as different values:
- Nairobi
- nairobi
- NAIROBI
To a person, they all appear to mean the same thing. To a computer, however, differences in formatting or extra spaces can affect how values are grouped and analysed.
This is where some of Excel's text functions became useful.
TRIM
I used TRIM to remove unnecessary spaces from text.
=TRIM(A2)
This is particularly useful when data has been copied from another source and contains unwanted space.
PROPER
I also explored PROPER for standardising names.
=PROPER(A2)
For example:
JOHN KAMAU
can become:
John Kamau
CONCAT
Another function I practised was CONCAT,which can be used to join pieces of text together.
=CONCAT(A2,B2)
These functions showed me that Excel isn't only useful for calculations. It can also be used to transform and standardise text.
8. Using Find and Replace
Not every cleaning problem requires a formula.
Excel's Find and Replace feature can be much quicker when dealing with specific inconsitencies.
The shortcut:
Ctrl + H
Opens Find and Replace
For example, if one category had been entered several times incorrectly, I could search for the incorrect version and replace it with the correct one.
This was one of those small Excel features that I initially overlooked but found surprisingly useful during the cleaning process.
9. Helper Columns: A Simple but Useful Technique
Something else I learned was the use of helper columns.
Instead of changing the original values immediately, I could create a temporary column containing a formula such as:
=TRIM(A2)
Then I could fill the formula down the dataset, check the result and, once satisfied, copy the result and use Paste Special → Values.
This allowed me to compare the original values with the cleaned ones before replacing anything.
For a beginner, I found this approach much less intimidating because I could see exactly what the formula was doing.
10. What This Exercise Taught Me
This exercise changed the way I look at spreadsheets.
Before, I thought cleaning data mainly meant removing duplicates and filling blank cells.
Now I understand that it involves several different decisions, including:
Understanding → Checking → Cleaning → Validating → Preparing
I also learned that there isn't always one correct way to clen a dataset.
The right approach depends on:
- The type of data
- What each column represents
- Why values are missing
- The type of inconsistency
- What the data will eventually be used for
For example, a missing text value might reasonably be labelled "Unknown", while a missing financial value may need further investigation rather than simply being replaced with zero.
11. From Cleaning to Analysis
The biggest lesson for me was that data cleaning is part of data analysis, not something separate from it.
A dashboard may look beautiful, but if the data behind it contains errors, the visualization can still communicate the wrong story.
The process therefore becomes:
Raw Data → Cleaning → Validation → Analysis → Visualization → Insights
Excel can support several of these stages, which makes it a useful starting point for someone learning data analytics.
Conclusion
My first hands-on experience with Excel data cleaning gave me a better understanding of what happens before the charts and dashboards are created.
I learned how to inspect a dataset, protect the original data, work with tables, check data types, identify missing values and duplicates, standardise text and use Excel functions such as TRIM, PROPER, and CONCAT
More importantly, I learned that cleaning data isn't about making a spreadsheet look perfect. It's about making sure the information is consistent, meaningful and reliable enough to support analysis.
I'm still at the beginning of my data analytics journey, but this exercise has given me a stronger foundation in Excel and a better appreciation for the work that happens behind the scenes before we can confidently say:
"Let's analyse the data"
Top comments (0)