Introduction
Microsoft Excel remains one of the most widely used tools for data analytics, not because it is the most powerful platform available, but because it combines accessibility with genuine analytical depth. It has data entry, data inspection, cleaning, calculation, filtering, visualisation, and reporting in one familiar environment. Through the Excel, we always get to have our first real encounter with dataset for analysis. This is exactly where my Week 1 learning began: understanding the building blocks of a spreadsheet, then immediately putting those building blocks to work on a dataset that badly needed attention.
This article explains my practical exercise, where I used the HR_Dataset_Dirty.xlsx dataset. The dataset contains 876 employee records and 21 variables, including Employee ID, department, salary, hire date, age, gender, performance score, employment status, bonus, education level, work experience, office location, project count, remote-work status, training hours and manager feedback score. The purpose of the exercise was not simply to calculate statistics, but to demonstrate an important principle of data analytics that reliable analysis begins with reliable data.
1. Understanding the Excel Environment
Before touching the dataset, it is worth revisiting the fundamental concepts that make everything else in Excel possible. These are not advanced features, they are the vocabulary of the spreadsheet, and every cleaning technique used later in this article is really just a combination of these basics.
Workbooks, Worksheets, and Cells
An Excel workbook is a file that can contain multiple worksheets (tabs), each organised as a grid of rows and columns. Columns are normally used for variables or fields, while rows represent individual observations. In the HR dataset, for example, Department is a variable and each row represents an employee record. The intersection of a row and a column is a cell, identified by a unique address such as A1 or D9. Every worksheet in the workbook built for this project - from the raw data to the final cleaned table, is simply a grid of cells connected to one another through references and formulas.
The image above illustrates a worksheet, it having both Original Dirty Hr Dataset, and Cleaned Dataset.
The above image demonstrates an Active Cell, bordered in green colour, and positioned in D5, with D being the column, and 5 being the row. The active cell therefore, is at the intersection of D column, and row 5.
Formulas and Functions
A formula is any expression that begins with an equals sign and calculates a result, a function is a predefined formula such as TRIM, PROPER, or IF. In this, I learnt text functions (TRIM, PROPER, SUBSTITUTE, UPPER), logical functions (IF), and counting functions (COUNTIF, COUNTBLANK). As shown throughout this article, these five or six functions are, in practice, almost the entire toolkit needed to take a messy dataset and make it ready for analysis.
2. Inspecting the HR Dataset
The first step in a data-cleaning workflow is data profiling. I checked the number of rows and columns, missing values, duplicate records, data types and inconsistent categories.
The original HR dataset contained 876 rows and 21 columns. It also contained 277 blank cells and seven exact duplicate rows. More importantly, several fields contained inconsistent representations of the same concept.
For example, the Department field contained values such as HR, H.R, Hr, Human Resource, Human Resources, Humman Res. and Humna Resources. These values may refer to the same department, but Excel would treat them as different categories when counting. See in the picture below.
Similar problems occurred in other fields. Gender included Male, MALE, male, M, Female, female, F and Femle. Employee Type included Permanent, Perm, permanent, Contract, Contrct and contractor. Office Location contained variations such as London and Londn, Nairobi and Nairob, and Tokyo and Tokio.
The dataset also contained invalid or suspicious numeric values. Examples included a performance score of 11 even though the intended scale is 1–10, an age recorded as thirty, and a project count recorded as ten. There were also values such as -1 in work experience and 1900 or 2030 in Last Promotion Year that require validation rather than blind acceptance.
Additionally, it had:
Inconsistent Employee IDs - some stored as plain numbers (10764), others prefixed with text (EMP-10540), and several IDs duplicated across two different employees.
Invalid or inconsistent dates - hire dates such as "2019-02-30" (30 February does not exist), "2020/13/05" (month 13 does not exist), "31/04/2021" (April has 30 days), and plain text such as "not available".
Missing values - blank first names, blank office locations, and blank salaries scattered throughout the sheet.
Data Cleaning
Rather than cleaning the data by hand, I built the entire workflow using formulas, so that every 'cleaned' value is calculated automatically and stays connected to its raw source. If the raw data changes, the cleaned output updates with it - this is the real advantage of doing data cleaning in Excel using formulas rather than typing corrections over the original values.
Step 1: Standardising Text - Names, Departments, Locations, and Status
The first pass targets text inconsistency. Full names are rebuilt from First Name and Last Name using TRIM (to remove stray spaces), SUBSTITUTE (to collapse accidental double spaces), and PROPER (to apply consistent capitalisation). Where either name was blank, an IF/OR check flags the record as "MISSING - REVIEW" instead of silently producing an incomplete name.
The result was a worksheet where every text field - name, department, office location, and employment status - now uses one consistent spelling throughout, which is essential before any analysis of the data started.
Step 2: Detecting Duplicate and Missing Records
With text standardised, my next step was finding rows that should not exist (duplicates) and rows that are missing critical information. I first stripped Employee ID of its "EMP-" prefix and converted to a genuine number with SUBSTITUTE and VALUE, so that "EMP-10540" and "10540" are recognised as the same identifier. Then I used COUNTIF against that cleaned ID column to flag any employee ID that occurs more than once, and COUNTBLANK to check each entire row for missing fields.
Step 3: Correcting Numeric Fields - Salary, Age, and Work Experience
Several numeric columns were stored as text, which silently breaks any SUM, AVERAGE, and other calculations on them. Salary values such as "$65109" were converted to true numbers, and the currency used as KES(Kenyan Shillings).
Step 4: Validating and Standardising Hire Dates
Dates were the least consistent field in the dataset, mixing genuine date values with plain text ("not available"), and several strings that look like dates but describe days that do not exist on any calendar.
Step 5: Consolidating into an Analysis-Ready Table
The final worksheet pulls the cleaned output of every previous step into a single, consolidated table, with one row per employee, with standardised names, departments, locations, employment status, salary, age, work experience, and hire date. A final Record Status column combines every flag raised earlier (duplicate ID, incomplete record, invalid date, or missing name/salary/age) into one clear verdict: "Ready for analysis". I added an AutoFilter so that a colleague could instantly isolate the records that still need attention before running any further analysis.
Step 6: Validating the Cleaned Dataset
After cleaning, I performed another inspection rather than assuming the data was correct.
The cleaned working dataset contained 869 rows after removal of the seven exact duplicate rows. Standardised categories were used for fields such as Department, Gender, Education Level, Employee Type, Office Location and Remote Work Status.
Numeric fields were converted to usable numeric values where possible, and invalid values were flagged or converted to blanks when they fell outside defined logical ranges.
Step 7: Basic Excel Analytics After Cleaning
Once the data was clean enough to analyse, I used Excel formulas to provide immediate descriptive statistics.
For example, I used AVERAGE to calculate mean salary, age or performance score. COUNTIF to count employees belonging to a particular department, and SUMIF to total salaries or bonuses for a selected category.
Using the cleaned working data, the average salary was approximately $74,114.16, the average age was approximately 41.98 years, and the average performance score was approximately 4.98.
There were 155 Finance employees and 263 employees classified as Fully Remote in the cleaned working dataset.
Conclusion
Excel is a powerful tool, and the skills provide the foundation for the wider data-analytics workflow. Understanding workbooks, worksheets, cells, ranges, formulas, data types, tables, sorting and filtering may appear basic, but these skills directly support professional data preparation and analysis.
The HR dataset demonstrates that real-world data is rarely perfectly structured. The original file contained 876 records, 21 variables, 277 missing cells, seven exact duplicate rows, inconsistent categorical labels, mixed data types, invalid dates and suspicious numeric values.
Through inspection, standardisation, type conversion, validation and duplicate handling, the working dataset was reduced to 869 records and made substantially more analysis-ready.
The most important lesson is that data cleaning is not merely about making a spreadsheet look neat. It is about making the meaning of the data consistent, transparent, and defensible.
Top comments (0)