Introduction
Microsoft Excel is one of the most powerful and beginner friendly tool for data analytics. It helps you to organize,store, calculate, clean,analyze and visalize data.
Data analytics is the process of collecting, cleaning and examinig raw data to find useful insights.
Excel Basics
Before diving into the specific data cleaning techniques I learnt, we can get into the basics of Excel to avoid feeling overwhelmed by all the gridlines.
Excel ribbon
This are tabs at the top of Excel interface that helps users to navigate and locate commands when using Excel.
The following are the core tabs under the Excel ribbons and the use of each:
1. Home tab-this is the everyday button that contains different formating options such, font type (colour and size), alignment options,number formating (Currency,date, accounting,date and time),styles, cells and editing.
2. Insert- This is used to insert charts, pivot tables, symbols, tables,shapes, slicers, comments and links.
3.Page Layout- It contains a list of commands that determine how the pay appears.
4.Formulas- It contains mathematical formulars used for calculation and formula auditing tools.
5.Data- This is a section used ti import data, sort, filter and analyse data.
6.Review-Has tools used to check spelling, manage accessibility and to check the performance of the workbook.
Excel Terms
Cell- This is a single box in which column and rows meet.
Column- This is a vertical group of cells labelled with letters (A,B,C).
Row-A horizontal group of cells labelled with numbers (1,2,3)
Worksheet- A single sheet inside Excel used to enter data.
Workbook-A file in excel containing one or more worksheets.
Data Cleaning in Excel
Data cleaning is the processs of identifying and correcting errors, inconsistencies, duplicates and missing values to ensre it is accurate, reliable and ready for analysis.
Steps Taken During Data Cleaning :
1. I inspected the dataset I was checking on to check for the any issues.
Below are the issues I ecountered:
Missing values
Duplicates
Inconsistent formating
2.Removing duplicates
There were data that appeared more than once as shown.
To identify and remove duplicates, I used the Employee ID as the unique identifier since each employee should have a different ID.
I selected the dataset,under data tab on the ribbon, I selected remove duplicates under data tools. I selected the Employee ID column to allow Excel to identify duplicates and remove them.
3.Removing Missing Values
The missing values appeared on most of my column and I replaced the text types by using "Unknown" value.
4.Correcting Inconsistent Text
The same information was written in the same format.Some with different capitalization, extra spaces and spelling variation. Which will cause inconsistencies during analysis.
To correct this ,I used find and replace: Find what> Replace With> Replace All.
This ensured that all the data remained consistent.
I also used Excel text functions to standardize the data:
TRIM-removes extra spaces from texts.PROPER-changes the texts to proper case.UPPER-changes the texts to upper case.LOWER-changes the texts to lower case.
5.Vaidating Cleaned Data
After completing the data cleaning process, I validated the data to ensure the changes had been made.
I used Filters and Sorting to check for any incorrect and unusal values. I also used Conditional Formating to check for any available duplicate values and missing values.
I also checked for the data types to ensure that columns were being stored under the correct data types such as dates under dates,numerical values as numbers.
After doing this, I confired that the data was ready for the next step which is analysis.
Conclusion
Through the entire process, I realised that data cleaning is not just about deleting blanks, removing blanks and correcting obvious errors. It is about understanding the data, investigating inconsistencies and making informed decisions before analysis. Every missing value, duplicate, unusual value can tell a story and removing it can lead to misleading insights.
Top comments (0)