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.
Top comments (0)