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

Introduction

Microsoft Excel is the most popular tool for data work and analysis. Its user-friendly environment makes it easy to understand, organize, clean, and explore data.
For a data analyst or scientist, knowing how to work with Excel goes beyond entering information into cells. Excel can be used to inspect datasets, identify errors, standardize values, filter records, sort information, remove duplicates, and prepare structured data for further analysis; in other words, clean data to draw meaningful insights from it.
This article demonstrates the key Excel concepts covered during Week 1 from the Human Resources dataset. The dataset contains employee information such as employee ID, department, salary, hire date, age, gender, performance score, employment type, office location, project count, training hours, and manager feedback score.
The original dataset contains 876 employee records and 21 columns. It is intentionally named "dirty", meaning that it contains data that an analyst should be able to clean to be able to use for insights and analysis.

1. Understanding the Excel Environment

The first step in working with Excel for analytics is understanding how information is organized.
An Excel worksheet consists of:

Rows, which normally represent individual observations or records.

Columns, which represent variables or attributes.

Cells, which contain individual values.

Worksheets, which allow related tables to be organized within the same workbook.

In the HR dataset, one row represents an employee record while each column represents an employee attribute.
For example, the columns include:

Column	Description
Employee ID	Unique identifier for an employee
First Name	Employee's first name
Last Name	Employee's surname

Understanding the dataset structure matters because it determines what types of analysis you can perform later.

2. Inspecting the Dataset before Cleaning

A common mistake we make when working with data is changing values immediately without first understanding the dataset. The first step should always be data profiling for a data analyst. I inspected the HR dataset to determine its size, column types, missing values, duplicate records, and inconsistent entries by using the filter function.

The dataset contains 876 rows and 21 columns. It also contains missing values across several fields. In total, there are approximately 291 missing cells.

For example, the Employee ID column contains missing values, while fields such as Hire Date, Gender, Bonus, Department, and Annual Training Hours also contain incomplete records. For example, some department names may appear as "HR", "H.R", "Human Resources", or "Human Resource". Although these values refer to the same department, Excel treats them as different text values. Similarly, office locations contain variations such as_ "London", "Londn", "San Francisco", "San Fransisco", and "SF". These needed cleaning for the department; I retained “HR” and sorted the location, for example, _“Londn” to “London”.

3. Data Types in Excel

Correct data types are essential for analysis.
Common Excel data types include:

• Text
• Numbers
• Dates
• Currency
• Percentages
• Logical values such as TRUE and FALSE

For example, Salary should be stored as a number rather than text because we may need to calculate average salary, total payroll, minimum salary, maximum salary, Sumif, Sumifs, and Averageifs.

Hire Date should be stored as an actual date so that we can calculate employee tenure or group employees by year.
The HR dataset contains several examples of inconsistent data types.
For instance, the Work Experience column contains numeric values such as 28, but some records contain values such as "5 years". Project Count is mostly numeric, but at least one value appears as "ten". The Bonus column also contains values such as 5575.99 and text values such as KES 1877.71
These values need to be converted into a consistent numeric format before mathematical calculations can be performed reliably.

4. Sorting and Filtering Data

Sorting and filtering are two of the most basic but useful Excel features for analysts.

Sorting

Sorting allows records to be arranged according to a particular field.
For example, I could sort the HR dataset by:

• Salary from highest to lowest
• Hire Date from oldest to newest
• Performance Score from highest to lowest
• Annual Training Hours from highest to lowest

If I want to identify employees receiving the highest salaries, I can sort the Salary column in descending order.

Filtering

Filtering allows specific records to be displayed while hiding records that do not meet a particular condition.

For example, I could filter:
Department = Finance to view only Finance employees. I could also filter: Employee Type = Permanent or: Remote Work Status = Fully Remote

Filtering is particularly useful when investigating data-quality problems because it allows an analyst to isolate values that don’t make sense.

For example, filtering the Department column makes it easy to identify variations such as "H.R", "Human Resource", "Human Resources", and "Hr".

5. Identifying Missing Values

Missing data is one of the most common problems in analytics, and understanding why it is missing. For instance, a blank cell does not automatically mean that the information is zero. It could mean that the information was not collected, was unavailable, or was accidentally omitted.
In the HR dataset, missing values appear in several columns.
For example:

• Employee ID has missing values.
• First Name and Last Name contain missing values.
• Salary contains missing values.
• Hire Date contains missing values.
• Gender contains missing values.
• Bonus contains missing values.
• Training Hours contains missing values.
• Manager Feedback Score contains missing values. Excel provides several ways to identify missing values. A simple method is filtering a column and selecting Blanks or using the COUNTBLANK function:

=COUNTBLANK(A2:A877)

This counts the number of blank cells in the specified range.
For more complex analysis, conditional formatting can also be used to visually highlight blank cells.

It is important not to automatically replace every missing value with zero. The correct treatment depends on the meaning of the variable. For example, a missing bonus may mean the employee received no bonus, but it could also mean that the bonus information was not recorded. The analyst should understand the business context before deciding how to treat it.