Getting Started with Excel for Data Analytics: From Basics to Data cleaning.

Daniel tayari — Wed, 02 Sep 2026 13:38:39 +0000

1. Introduction

Excel is the post popular data tool that involves entering, validating, cleaning and analysing data
Data cleaning is a very important part of data analysis since it determines the quality of the data. Data cleaning improves accuracy, handles missing data, removes duplicate data and makes data analysis easy.
This article explains the basics of excel and data cleaning.
I will use fictional laboratory dataset for demonstration of basic excel and data cleaning.

2. Dataset Description

The dataset used here is a fictional laboratory dataset containing approximately 50 lab records and one duplicate record.
The dataset contain the following variables:

PatientID
Age -gender
Test
Results
Unit
Test Date
Facility
County
Cost kes

3. Understanding Excel

3.1 Workbook and worksheets

An Excel workbook can contain multiple worksheets. In this article the workbook will contain two sheets , one for the raw data and the other one will be the cleaned data.

3.2 Rows, Column and Cells

Excel organizes information using rows and columns.
Each observation occupies a row , while each variable occupies a column .
A cell can be identified using a column letter and row number eg LAB005 is in cell A6.

4. Data quality and Data Cleaning

in the laboratory dataset I provided we will be using there are several deliberate data quality issues that we will use to do data cleaning.
The issues include:

Missing values
Duplicate records
Inconsistent gender entries.
Inconsistent facility names
Inconsistent county capitalization
Unrealistic age values
invalid cost values
Inconsistent qualitative results

4.1 Identifying missing values

In this section we will identify missing values in the dataset I have provided. I did the following steps to filter for blanks and fix then:
-select the dataset

presse ctrl+shift+L
Click the filter arrow on all columns and select blanks
Excel will show only rows where there are missing values. below are screenshots of how I have identified missing values: I fixed the mossing vales by inputting some data.

5. Removing Duplicate Records

Duplicate record in an excel occurs when same observation appears more than once.
The dataset contains an intentionally duplicated lab record.
The following steps identify and remove duplicate records:

Select the whole sheet
Click on the DATA icon then remove duplicate
A window will appear showing column names.
you can leave all columns or select some or even one column depends on how you define duplicate. in our case we will leave only patient ID column since it's the only column with duplicate data.
after selecting the patient ID column, click OK A Window pop will appear saying

"1 duplicate value found and removed;50 unique values remain"
NB: We can identify duplicates before deleting them by clicking:
Home > conditional formatting > highlight > cells rules > duplicate.
Excel will show duplicate entries, allowing inspecting before deleting them.

6. Standardizing Inconsistent Text Data

6.1 Gender

The dataset contain variations such as :
Male
male
Male
Female
female
FEMALE
Although these values represent the same categories, inconsistent formatting can affect analysis.
For example, Excel may treat differently formatted text as separate categories during some analytical operations.
Therefore, the values should be standardized to:
Male
Female
I followed the following steps to standardize:

Selected the gender column.
Pressed ctrl+H (Find and Replace).
Replaced all variants by finding and replacing.

Results after standardizing Gender column

6.2 Cleaning Facility and County Names

The dataset contains inconsistencies in the facility & county columns. eg kwale, KWALE,Kwale.
I standardized the county column to Kwale

6.3 Cleaning Date Data

Dates are very important in excel and data analysis because they analysis to happen in time.
Our dataset contains different date representations. This is how we standardized the dates into consistent date format:

7. Conclusion

That is what I learned on the week one of basics of excel and data cleaning.

DEV Community: Daniel tayari