Philip Saidi

Posted on Aug 29

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

#datascience #beginners

Overview

What is data and data analysis

Data is information that helps people or business make decisions.

Types of data

Text (Abcde)
Numbers (12348.0)
Dates (12/3/1993, 12-5-2026)
Currency ($45.5, KES200.5)

Data analysis

The process of making data to a useful information using different tools such as Excel.
It entails processes like

Data formatting
Data validation
Data cleaning
Data analysis
Data visualization

What is Excel

Excel is a spreadsheet application used to create, view, edit , clean, analyze and visualize data.

Excel use cases

Create data or record data for reference.
To record data /data entry.
Data analysis and give insights.
Business intelligence on light weight.
Project management.
Cleaning data.
Create beautiful visual representation of data.

Creating data with Excel for Students grades

Open excel application in your pc
Open Blank workbook name it Students grades
Save by clicking File then Save as.
Choose a location to save and name the file
Click on cell A1 and name it FIRSTNAME
Press TAB to move to the next cell
Name it LASTNAME, next QUIZ 1, QUIZ 2, QUIZ 3, and AVERAGE as shown below
Autofit the columns by Clicking Home tab -> Format -> Autofit column -> OK.
Enter the data as shown in the screenshot and save.

Introduction to Excel Cleaning data.

The first step to data cleaning:

Sample data errors

Wrong date format
Incorrect data types
Blank cells/ missing values
Duplicates
Wrong format of data types
Missing values
Incorrect information

Formatting data

You can format numbers, texts, rows and columns

Number formatting

Numbers always align to the right of the cell.
Follow these steps to format numbers to desired data type

Select the column you want to format
Click Home tab/ribbon and find Number group
A drop down menu will occur, choose which number format you want and click OK.

Text Formatting

Text are a string or words like John, Lorry, HR.
Text are aligned to the left of the cell
Follow these steps to format texts

Select the text you want
Click Home tab
Choose to color or make it bold in the font section
You can change the background color and click OK.
Save your work.

Row and Column formatting.

Follow these steps

Select an entire row/column example ROW 1
Select Home tab and Click B to make it Bold.

Data Cleaning Using Excel

Data cleaning is the art of formatting and correcting data so that it can be correct and accurate for use in data analysis.

For this We will use a random data set from the internet called kenya-school-grades-dirty.
Start with removing duplicates

Select all data
Go to data tab
Click remove duplicates.
Select the column you want
Click OK.

In the data set names are not in proper case and have spacing.
We rectify this by:

Selecting the columns Student Name.
Right click and insert a new column
In the empty column write the formula =TRM(PROPER(B2:B221)) and Enter.
This removes any whitespace and capitalize the first letter.
Insert another cell and copy the corrected student name and delete the previous one then save.

In the gender it has more than one identifier that is M, f, Female, FEMALE, MALE.
We can correct this by removing the repeating gender by find and replace method.