Introduction
Excel is a pretty straightforward tool for anyone starting out with data. You can input, organize, calculate and look at information without needing to write any code. However, just having data in Excel doesn't automatically make it usable. Real-world datasets often have gaps, repeated information, spelling mistakes, and other small issues that need fixing before you can really use the data.
For this task, I worked with a file named HR_Dataset_Dirty.xlsx. It had 876 employee records with details like Employee ID, Name, Department, Salary, Hire Date, Age, Gender and Performance Score. This article explains the basic Excel features I learned and how I used them to clean up this data, making it ready for analysis.
Understanding the Basics
A file in Excel is called a workbook and each sheet within it is a worksheet. A worksheet is made up of rows, numbered down the side, and columns, labeled across the top. The point where a row and column intersect is a cell; for instance, cell A2 might contain an employee's ID number.
Usually, the very first row of a dataset lists the column titles, explaining what kind of information is in each column below. In my dataset, some columns contained numbers (like Salary and Age), some had dates (like Hire Date), and others had text categories (like Department and Gender). Excel handles these types differently. You can perform calculations like sums or averages on numbers, but if a number is accidentally saved as text, those calculations won't work correctly.
Before I made any changes, my first step was to look over the raw data so I could get a sense of what I was dealing with.
A section of the raw dataset, with blank cells highlighted in red and duplicate Employee IDs highlighted in yellow.
Spotting the Problems
Looking through the raw data, I noticed a few problems right away:
- Some cells were blank — missing an age, a salary or a gender.
- Some Employee IDs appeared more than once meaning the same employee had been entered twice.
- The same category was spelled differently in different rows — for example, "HR," "Hr," and "Human Resources" all meant the same department, but Excel would treat them as three separate values.
- Some dates didn't make sense, like a hire date of
2020/13/05— there's no 13th month.
I used Excel's Conditional Formatting (Home tab → Conditional Formatting) to highlight these problems directly on the sheet, so I could see exactly which cells needed attention before touching anything.
Handling Missing Values
Instead of guessing what a missing value should be, I used Excel's Filter feature to check each column for blanks. For example, filtering the Salary column showed me exactly which employees had no salary recorded.
We were told in class that it's important not to just replace every blank with a zero — a missing salary doesn't mean the employee earns nothing, it just means the information wasn't captured. I left genuinely missing values as blank rather than guessing a number.
Removing Duplicates
I looked for employee IDs that showed up more than once because each ID should be unique. Some, like 10407 and 10870 appeared twice and had the same details.
Once I made sure these were actual duplicates and not two different employees with similar IDs, I used Excel's Remove Duplicates feature from the Data tab to get rid of them. This process removed 23 duplicate rows from the data.
Cleaning Text
When i cheched the data some text entries had too many spaces or the capitalization was all over the place; for instance, some names were just in lowercase. Excel has a couple of easy functions for fixing this:
-
TRIM()removes extra spaces from text. -
PROPER()changes text to proper case, so "grace karanja" becomes "Grace Karanja."
I used these on the Name and Location columns to get everything looking the same.
Standardizing Categories
I also noticed that the Department column had the same problem. There were several spellings for the same department like "Markting" and "Marketing," or "HR" and "Human Resources." Since these functions don't fix spelling on their own, I mapped each incorrect spelling to its correct version, so that every version of a department name became one standard value.
Cleaning formulas correcting names, departments, and other text fields, with anything still needing a manual check flagged in red.
Fixing Dates and Numbers
I also had to check the Hire Date field too. The goal was to make sure each entry was a valid date and to flag any that weren't, like the incorrect 2020/13/05 instead of trying to guess what they should have been.
The Age field had a similar problem. One entry was the word "thirty" instead of the number 30. Since Excel can't use text in calculations, I flagged entries like this so they could be reviewed, rather than letting them cause errors in later calculations.
Preparing the Clean Dataset
Once every column had been checked and corrected, I copied the results into a new sheet as plain values, so the final table didn't depend on the raw data anymore. I also added a dropdown list to the Department column (Data tab → Data Validation) so that only the correct department names can be entered from now on.
The final cleaned dataset — 852 unique employees remain after removing duplicates, with a Department dropdown to keep future entries consistent.
A Quick Summary
With the data cleaned, I used COUNTIF() and AVERAGEIF() to summarise employees by department and built a simple chart from that summary to see the results at a glance.
A quick summary of headcount and average salary by department, built from the cleaned data.
Conclusion
I learned from this exercise that before you even get to charts or dashboards, you first need to make sure the data itself is reliable. Things like missing information, duplicate entries, different spellings for the same thing or incorrect dates can all mess up your analysis if you don't find and fix them early on.
Turning a messy raw dataset into a clean and consistent one really showed me how crucial this initial step is. It also showed me that some basic tools in Excel like Conditional Formatting, Filter, Remove Duplicates, TRIM, PROPER and Data Validation are all you really need to do it.
Top comments (2)
Well done, Jack! You did a solid job explaining the Excel fundamentals and showing practical data-cleaning techniques. The article is clear and easy to follow. Keep improving by adding a little more explanation around the formulas and showing clearer before-and-after results from the cleaning steps.
Good good piece, kinda gave me an idea of how to adjust mine