Getting Started with Excel for Data Analytics: From Basics to Data Cleaning

1. Introduction

Excel is much more than a spreadsheet for entering numbers. It can be used as a data-analysis tool that helps analysts inspect, validate, filter, summarize, and prepare raw data before deeper analysis begins. In typical analytics, the quality of the final work depends heavily on the quality of the data used; therefore, data cleaning is not an optional step—it is the foundation of effective data analysis.
This article demonstrates key Week 1 Excel concepts _using an employee dataset containing _employee IDs, names, departments, gender, marital status, hire dates, salaries, educational level, performance score among others. The raw file intentionally contains common data-quality issues: inconsistent capitalization on the First and Last names, blank records, duplicate employee records, varying department names, currency and dates that need review. By working through these issues, the article shows how Excel’s formatting tools, text functions, filters, conditional formatting, numerical functions, conditional summaries, and date functions can turn a messy workbook into an analysis-ready dataset.

2. Why Data Cleaning Matters

Data cleaning is more than just about removing errors. By standardizing formats and categories, we make datasets more transparent, usable, and valuable for management analysis and reporting purposes.
Data analysis is simple – garbage in, garbage out. A dashboard or prediction can appear professional, but can be misleading if the underlying data has duplicates, blank values, inconsistent categories or incorrectly formatted text and dates.
For example, “IT” “I.T.” and “Information Tech” can be viewed as different department values if naming is not standardized. Duplication of an employee ID can inflate employee counts and department totals. A blank performance score might mean that something is missing and should be looked into and dates saved as text cannot be reliably used in calculations such as employee tenure checks.
A good practice is to retain the raw data on a separate worksheet, create a separate cleaned-data worksheet, and document the cleaning logic through clearly labelled worksheets.

3. Dataset and Initial Inspection

The sample dataset contains Eight hundred and seventy seven (877) raw employee rows. It includes duplicate records. The format of employee ID, First and Last Names, Department, Salary, Hire Dates, Age, Gender and performance score and all the other data field labels included inconsistent formats
The first step was to format the range using Autofit Column Width to make rows easier to read. Freeze the view of the first row of the column titles to facilitate the movement around the dataset
In this exercise, I applied a structured process to standardize records, eliminate duplicates, and format key columns for clarity. Below is a step-by-step account of the transformation.

3.1. Standardizing Employee IDs

The first step was to clean up the Employee ID column. All entries beginning with emp- were stripped of the prefix to create a uniform naming convention. This ensured that IDs were consistent and easier to manage.
Next, I checked for duplicate records. Using Conditional Formatting in Excel, I highlighted duplicate values in the Employee ID column. By filtering these duplicates, I analyzed their content:
• If records were identical, one was deleted.
• If records differed, only one ID was retained.
This process revealed seven duplicate records, reducing the dataset to > 870 entries.

3.2. Cleaning Names

To improve readability, I standardized the First Name and Last Name columns. By applying the =PROPER(text) function, I converted names to proper case (> e.g., john → John). The corrected values were pasted into new columns, and the original raw data was removed.

3.3. Standardizing Departments

Department names were filtered and renamed to ensure consistency. This eliminated variations and spelling differences across records.

3.4. Formatting Salaries and Bonuses

Financial data required special attention:
• Salary and Bonus columns were converted into currency format (KES).
• Sorting from largest to smallest allowed easy comparison.
• Any entries in other currencies were replaced with the standardized KES format.

3.5. Dates and Employment Details

The Hire Date column was reformatted into a consistent date style. Sorting from newest to oldest provided a clear timeline of staff recruitment.
Other employment-related fields were standardized using filters:
• Gender, Performance Score, Full-Time Status, Marital Status, Education Level, Employee Type, Office Location, Remote Work Status → all categories were renamed for uniformity.
• Working Experience, Project Count, Last Promotion Year, Annual Training Hours, Manager’s Feedback Score → reformatted into consistent numerical or categorical formats.

3.6. Filtering data

Filtering is one of the quickest ways to inspect a dataset without deleting or moving records. In the employee table, a single filter can answer questions such as:
• Which employees are in the IT department?
• Which records belong to Female employees?
• Which salaries exceed 80,000?
• Which performance scores are blank?
For a single-condition filter, an analyst can filter Department to show only IT. For a multiple-condition filter, the analyst can filter Department to IT and Gender to Female at the same time. This is a practical way to inspect the subset before calculating conditional counts, totals, or averages.
Number filters are especially useful for checking thresholds. For example, a filter can display salaries greater than 80,000, salaries between two values, or the top ten salary values. This lets an analyst inspect unusual values before treating them as valid, errors, or outliers.

4. Identifying Data-Quality Issues

Before permanently changing data, it is safer to identify suspicious records. Conditional formatting is useful for this because it highlights cells based on rules while preserving the original information.
There is a clear distinction between identifying duplicates and deleting them: Conditional Formatting can highlight duplicate values for review, while Data > Remove Duplicates permanently removes duplicate records based on the selected columns. That difference matters because a duplicate may be an accidental repeated row, but it may also represent a legitimate repeated event in another type of dataset.

4.1. Conditional formatting

Conditional formatting improves quality control because it brings potential problems to the analyst’s attention immediately. Common rules include:

• Highlight duplicate values.
• Highlight blank cells.
• Highlight dates before today.
• Highlight values above or below a threshold.
• Use colour scales or data bars to make patterns visible.
In this example, employee Id 10407 appears twice. Highlighting the ID first allows the analyst to compare both rows and verify that they are exact duplicates before removing one. This is safer than deleting values without checking the rest of the row.

4.2. Find and Replace with Ctrl + H

Excel’s Find and Replace feature is a fast method for correcting known, repeated issues. Pressing Ctrl + H opens the Replace dialog box.
For example, it can be used to:
• Replace a misspelled department name such as Finanace with Finance.
• Replace a repeated placeholder such as N/A with a blank value where appropriate.
• Replace an unwanted character in an identifier.
• Standardise a known label across the worksheet.
Find and Replace is powerful, but it should be used carefully. Replacing a short word or character across the whole workbook can accidentally alter valid values. A safer practice is to select the relevant column first, use Find All to preview matches, and then replace only after verifying that the target is correct.

5. Cleaning Text with Functions

The cleaned dataset uses formulas rather than overwriting the raw values.
The following functions standardise text casing:
Function Purpose Example
=PROPER(text) > Capitalises the first letter of each word aNNa mWANGI becomes Anna Mwangi
=LOWER(text) > Converts text to lowercase JAMES becomes james
=UPPER(text) > Converts text to uppercase it becomes IT
For names, PROPER is generally useful because it improves readability.

5.1. Removing extra spaces with TRIM

The TRIM function removes leading spaces, trailing spaces, and repeated spaces within text:
=TRIM(A2)

5.2. Measuring and extracting text

Excel includes useful functions for working with text structure.
Function Purpose

=LEN(text) > Counts characters
=LEFT(text, num_chars) > Extracts characters from the left
=RIGHT(text, num_chars) > Extracts characters from the right
=MID(text,start_num,num_chars)> Extracts text from the middle
=CONCAT(text1,text2,...) > Joins pieces of text

6. Numerical and Statistical Functions

After text and structural problems are addressed, Excel can calculate descriptive statistics and validate numerical fields.

6.1. Arithmetic operators

Excel formulas begin with = and use familiar arithmetic operators:
Operator Meaning
+ Addition
- Subtraction
* Multiplication
/ Division
^ Exponent
For example, an estimated 5% salary increase can be calculated as:

=G2*0.05
A new salary after the increase can be calculated as:
=G2+(G2*0.05)
Excel also includes functions for common mathematical calculations:

=POWER(2,3) returns 8, while:

=SQRT(8100) returns 90. The PRODUCT function multiplies a set of values:

=PRODUCT(2,3,4) returns 24.

6.2. Aggregate functions

Aggregate functions summarise a group of numeric values:
Function Meaning
SUM > Adds values
AVERAGE > Calculates the arithmetic mean
MEDIAN > Finds the middle value after sorting
MODE > Returns the most frequent value
MIN > Finds the lowest value
MAX > Finds the highest value
PRODUCT > Multiplies values

Using the Salary column in the cleaned dataset, examples include:

=SUM(Cleaned_Data!G2:G12)
=AVERAGE(Cleaned_Data!G2:G12)
=MEDIAN(Cleaned_Data!G2:G12)
=MIN(Cleaned_Data!G2:G12)
=MAX(Cleaned_Data!G2:G12)

7. Counting and Conditional Analysis

Counting is essential for validating completeness and producing initial summaries.

7.1. **COUNT, COUNTA, and COUNTBLANK**

These three functions answer different quality-control questions:
Function What it counts Example use
COUNT - Numeric cells only - Number of salary values
COUNTA - Nonblank cells - Number of employee IDs entered
COUNTBLANK - Empty cells - Missing performance scores or missing hire dates

Examples:
=COUNT(Raw_Data!G2:G12). This checks how many numeric salary entries exist.
=COUNTA(Raw_Data!A2:A12). This counts records with an Employee ID.
=COUNTBLANK(Raw_Data!I2:I12). This identifies how many employees have no performance scores

These checks are simple but valuable. If there are 11 employee IDs but only 10 numeric salaries, the dataset may contain a missing or incorrectly formatted salary. Before calculating an average, the analyst should investigate that discrepancy.

7.2. COUNTIF and COUNTIFS

COUNTIF counts cells meeting one condition:
=COUNTIF(Cleaned_Data!C2:C12,"IT")
This counts IT employees.

COUNTIFS counts records meeting multiple conditions:
=COUNTIFS(Cleaned_Data!C2:C12,"IT",Cleaned_Data!D2:D12,"Female")
This counts employees who are both in IT and Female.

Conditional counting is more reliable than manually filtering and counting visible rows, particularly when the analysis needs to be repeated as new data is added.

7.3. SUMIF, SUMIFS, AVERAGEIF, and AVERAGEIFS

Conditional aggregation answers business questions about particular groups.

=SUMIF(Cleaned_Data!C2:C12,"IT",Cleaned_Data!G2:G12)
This returns the total salary for the IT department.

=SUMIFS(Cleaned_Data!G2:G12,
Cleaned_Data!C2:C12,"IT",
Cleaned_Data!D2:D12,"Female")
This returns the total salary for Female employees in IT.

=AVERAGEIF(Cleaned_Data!C2:C12,"IT",Cleaned_Data!G2:G12)
This calculates the average IT salary.

=AVERAGEIFS(Cleaned_Data!G2:G12,
Cleaned_Data!C2:C12,"IT",
Cleaned_Data!D2:D12,"Female")
This calculates the average salary for Female IT employees.

In the sample workbook, these formulas show how an analyst can move from cleaning to basic exploration.

8. Dates and Time-Based Cleaning

Useful date functions include:
=TODAY()
Returns the current date.
=NOW()
Returns the current date and time.
=YEAR(F2)
Extracts the year from a date.
=MONTH(F2)
Extracts the month number from a date.
=DAY(F2)
Extracts the day of the month.

8.1. DATEDIF

DATEDIF calculates the difference between two dates. It is useful for employee tenure or contract duration.
To calculate the number of days an employee has worked:
=DATEDIF(F2,TODAY(),"d")
To calculate completed months:
=DATEDIF(F2,TODAY(),"m")
To calculate completed years:
=DATEDIF(F2,TODAY(),"y")

8.2. NETWORKDAYS

NETWORKDAYS calculates the number of whole working days between dates, normally excluding weekends:
=NETWORKDAYS(F2,TODAY())
This is useful for operational analysis. For example, it can calculate working days since a staff member started working

9.Recommended Cleaning Workflow

A repeatable workflow makes Excel analysis more accurate and easier to audit.

1. Preserve the original dataset in a raw-data sheet.
2. Copy the dataset into a separate worksheet and format headers, dates, and numbers.
3. Inspect data using filters, sorting, and conditional formatting.
4. Identify duplicates visually before using Remove Duplicates.
5. Use TRIM, PROPER, LOWER, and UPPER to standardise text.
6. Use LEFT, RIGHT, MID, LEN, and CONCAT to validate or restructure text.
7. Use COUNT, COUNTA, and COUNTBLANK to check completeness.
8. Use conditional functions such as COUNTIFS, SUMIFS, and AVERAGEIFS to summarise cleaned data.
9. Use date functions to calculate time-based variables such as tenure and workdays.
10. Review the cleaned output, document assumptions, and only then begin visualisation or advanced analysis.

Conclusion

The most important lesson is that cleaning should happen before interpretation. Conditional formatting helps detect issues; filters help investigate subsets; Find and Replace corrects known repeated values; text functions standardize labels; aggregate functions describe numerical data; conditional functions reveal group-level patterns; and date functions create meaningful time-based variables.
By applying these techniques in a structured cleaning workflow, an analyst creates a dataset that is more consistent, more reliable, and ready for later stages such as PivotTables, charts, dashboards, and statistical analysis.