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

Introduction

Data analytics does not always begin with complicated programming or advanced statistical tools. For beginners, it can start with something as familiar as Microsoft Excel.

During Week 1 of my data analytics learning, I focused on the fundamentals of preparing data for analysis. These included formatting, sorting, filtering, identifying missing values, removing duplicates, correcting inconsistent data and validating information.

To apply these concepts practically, I worked with an HR dataset containing employee information such as Employee ID, names, departments, salaries, hire dates, ages, performance scores, employee types, office locations and other employee-related information.

The dataset contained 877 records and 21 columns. Although it looked like a normal spreadsheet at first, closer inspection revealed several data-quality problems. This made it a useful dataset for practicing the data-cleaning techniques covered during Week 1.

Inspecting the Raw Dataset

The first step in cleaning data is to understand what you have before changing anything.

I opened the HR dataset in Excel and reviewed the columns and records. Some of the main fields included:

• Employee ID
• First Name
• Last Name
• Department
• Salary
• Hire Date
• Age
• Gender
• Performance Score
• Employee Type
• Office Location
• Project Count
• Remote Work Status
• Annual Training Hours
• Manager Feedback Score

Each row represented an employee, while each column represented a specific attribute.

Raw Dataset

Before cleaning, the dataset was as follows;

The initial inspection revealed missing values, duplicate employee IDs, inconsistent spelling, invalid dates and incorrect data types.

This showed me why inspecting data before analysis is important. Data can look organized while still containing errors.

Formatting the Data

After inspecting the dataset, I formatted the worksheet to make it easier to work with.
I confirmed that the data contained headers.
Using a table made it easier to work with the 877 records because Excel automatically provided filtering options for each column, as seen below;

I also adjusted column widths and made sure that the headings and values were easy to read. This is done by selecting the whole dataset, and autofitting the column width, i.e;

Formatting may not change the actual data, but it improves readability and makes the cleaning process much easier.

For example, salary values can be displayed consistently with commas and in same format, as numbers and not texts, while dates can be displayed using one standard format,i.e;

Identifying and Removing Duplicates

Duplicate records were another important issue in the dataset.

Employee ID is particularly useful when checking for duplicates because it is intended to identify an employee.

I found employee IDs that appeared more than once in the dataset.

If the same employee record is accidentally entered twice, keeping both records could produce incorrect results. For example, the number of employees could be overstated.

Excel provides a Remove Duplicates feature under the Data tab.
The process is:

1. Select the dataset.
2. Click Data.
3. Select Remove Duplicates.
4. Select the column used to identify duplicates.
5. Review the records.
6. Remove confirmed duplicates. This is illustrated below;

However, duplicate removal should be done carefully.

A repeated ID does not automatically prove that the entire record is incorrect.

The records should first be compared to determine whether they are genuine duplicates.

This taught me an important lesson: data should be investigated before it is deleted.

Correcting Inconsistent Data

Another problem I discovered was inconsistent text.

For example, the Department column contained variations such as:

Marketing
Markting

There were also different versions of the IT department:

IT
I.T
Info Tech
Information Tech

The Office Location column had similar problems:

London
Londn

and:

San Francisco
San Fransisco
SF

Inconsistent Values

These differences can cause problems when sorting, filtering or eventually grouping the data.

For example, Excel would treat London and Londn as different values even though they may represent the same location.

Some formatting inconsistencies can be handled with Excel functions such as:

=TRIM(A2)

which removes unnecessary spaces.

Capitalization can also be standardized using:

=PROPER(A2)

However, spelling mistakes such as Londn or Markting need to be corrected systemically based on the expected value.

This showed me that data cleaning is not simply about formatting. It also requires understanding what the values are supposed to represent.

Validating the Data

After cleaning obvious problems, the next step was validation.

Validation means checking whether the values make sense for their particular columns.

For example, the Age column should contain reasonable numerical values.

However, the dataset contained a value such as:

thirty

while most other ages were numerical.

This value needs to be investigated because it does not follow the expected format.

The Hire Date column also contained invalid dates, including:

2019-02-30

February does not have 30 days, so this cannot be a valid calendar date.

The Performance Score column also contained unusual values, including text and values outside the expected range.

These examples demonstrated that validation requires more than checking whether a cell contains data.
Validation is usually done before data is entered, to standardize the texts entered in the dataset, i.e,

The question should be:

Does this value make sense for this particular field?

Basic Sorting

Sorting was another technique I used when working with the dataset.

Excel allows data to be arranged from smallest to largest, largest to smallest or alphabetically.

For example, I could sort the Salary column from largest to smallest to identify the highest salaries.

I could also sort Employee IDs to make repeated IDs easier to identify.

Sorting the Department or Office Location columns can also make spelling inconsistencies easier to notice because similar values appear together.

Sorting therefore serves both an organizational and data-cleaning purpose.

Basic Filtering

Filtering allows me to temporarily display only the records I need.

For example, I could filter the Department column to show only employees working in HR.

I could also filter the Age column or Salary column to investigate specific ranges.

Most importantly for data cleaning, I could filter for blank values to quickly find missing information.

With 877 records, this is much faster than manually checking every row.

Sorting and filtering are simple Excel features, but they are extremely useful when working with real datasets.

My Data-Cleaning Workflow

After working with the dataset, I developed a simple workflow:

Raw Dataset
     ↓
Inspect
     ↓
Format
     ↓
Find Missing Values
     ↓
Identify Duplicates
     ↓
Remove Confirmed Duplicates
     ↓
Correct Inconsistent Values
     ↓
Validate Data
     ↓
Sort and Filter
     ↓
Clean Dataset

The final cleaned file looked as below;

The purpose of this process is to make the dataset more consistent and reliable before using it for further analysis.

What I Learned

Working with the HR dataset helped me understand that data cleaning is an essential part of data analytics.

The dataset contained problems that would have been easy to overlook without careful inspection. These included missing values, duplicate Employee IDs, inconsistent department names, misspelled locations, invalid dates and incorrect data types.

The Excel tools I learned during Week 1 provided practical ways to deal with these problems.

Formatting made the dataset easier to read. Filtering helped locate missing values. Sorting helped identify patterns and inconsistencies. Remove Duplicates helped eliminate confirmed duplicate records. Validation helped determine whether values were reasonable and correctly formatted.

Most importantly, I learned that cleaning data requires judgment. An unusual value should not immediately be deleted. It should first be investigated and understood.

Conclusion

Excel provides a simple but powerful starting point for learning data analytics.

Working with the HR dataset allowed me to apply the Week 1 concepts to a realistic data-cleaning problem rather than simply learning them theoretically.

The process can be summarized as:

Inspect → Format → Clean → Validate → Sort/Filter → Prepare

The biggest lesson I took from this exercise is that analysis should not begin with assumptions about the data. It should begin by understanding and checking the data.

A clean dataset produces more reliable results, while dirty or inconsistent data can lead to misleading conclusions.

As I continue learning data analytics, these basic Excel skills will provide a foundation for working with larger datasets and more advanced tools.

Before analyzing data, make sure the data is clean. Before trusting the data, validate it.