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:

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.

6.Converting Text into Numerical Values

Textual representations of numbers pose a challenge when analyzing data. For example, the Bonus column has records like:
KES 1877.71. Excel processes this as a number only when the currency text is removed. We use this formula:

=VALUE(SUBSTITUTE(K2,"KES ",""))

With SUBSTITUTE deleting "KES" and VALUE converting the remaining text into a number.
For example, if the original record is:

KES 1877.71

the result will be:

1877.71
The same applies to the Work Experience column too. In this case, if you have 5 years, the numerical part is extracted and converted. This is important since I might need to calculate average work experience later on.

6. Simple Excel Calculations for Analysis

Once the data has been cleaned, Excel can be applied to perform productive business calculations.
For example, total salary can be computed as follows:

=SUM(E2:E870)

Average salary can be established as follows:

=AVERAGE(E2:E870)

Maximum salary can be obtained as follows:

=MAX(E2:E877)

Minimum salary can be derived as follows:

=MIN(E2:E877)

The number of employees can be obtained as follows:

=COUNTA(A2:A877)

Conditional calculations may be implemented. For example, total salary could be computed for the employees of Finance:

=SUMIF(D:D,"Finance",E:E)

To determine how many employees work in a specific department, A COUNTIF formula may be used:

=COUNTIF(D:D,"Finance")

These formulas illustrate how cleaning is closely connected to the process of analysis. If the Department column is not standardized, the results of those calculations will be unreliable, and analysis won't make any sense.

Conclusion

Insights gained from the dataset are that, from the Interaction with the HR dataset, raw data hardly ever comes in a perfect structure. Even in a straightforward dataset of employees, one may have missing values, misspellings, inconsistent capitalization, repeated entries, and information that comes in various formats. Data cleaning involves decision-making. While Excel can help in automatically detecting errors, it is up to the human analyst to determine what the proper value is. Even though the value appears as a number, it may be stored as a text string, causing some formulas and analyses to fail.

Excel is a good tool for learning this process because it shows you the data issues and offers solutions on how to deal with them. Thus, developing proficient skills in using Excel for data cleaning is an important step to being a good data analyst.