Microsoft Éxcel is one of the most beginner friendly tools for cleaning, visualizing and analyzing data.
In this article, we'll go through the basics of using Excel for data analytics.
Step 1: Importing and Viewing Data
Loading your dataset into Excel can be done into two ways;
- Copy and paste data directly into a worksheet
- Go to Data → Get Data → From Text/CSV → Select file to import data from → Load dataset
Each column represents a variable (like "ID", "Name" or "Gender") and each row represents a record.
Step 2: Cleaning Data
Raw data is usually messy, containing missing values, duplicates, data type errors, extra spaces or wrong alignment. Excel has simple tool to handle that:
Remove Duplicates:
Select your whole data(Ctrl+A) → Go to Data → Remove Duplicates
Data Types:
Select column → Right Click → Format Cells
*Unique numbers such as ID, Phone-numbers, Postal addresses should be formatted as Texts.
Alignment:
Numbers and Dates should be right-aligned while Texts should be left-aligned
Step 3:Exploring with Sorting and Filtering
Sorting:Arrange data in ascending/descending order.
Select column → Go to Home → Sort & Filter → Sort A to Z or Sort Z to A
Filtering:Allows one to display only the rows that meet a certain criteria.
Select column → Go to Home → Sort & Filter → Turn on Filter, then select drop-down arrow to show only relevant records
To replace blanks;
Select column → Go to Data → Under Data Tools → Click Data Validation → Set Allow to List → In the source box, type the values you want to allow(separated by commas) → Click OK
This prevents invalid data entry or flags errors during processing
Step 4: Using Basic Function Formulas
The formulas let you summarize and analyze data efficiently
Functions are grouped into;
i)Text Functions
They're used to manipulate, extract or clean text in cells.
=LEFT(text,num_chars)
num_chars returns the first characters from a text string
Other useful text functions: RIGHT(),MID(),LEN(),TRIM(),PROPER(),UPPER(),LOWER()
ii)Aggregate Functions
They perform calculations on a range of numbers.
=SUM(range) -Adds up all numbers in a range
=AVERAGE(range) -Finds the mean value of numbers in a range
Other useful aggregate functions: MAX(),MIN(),COUNT()
iii)Date & Time Functions
They allow you to calculate, format or extract information from dates and times
=TODAY() -Returns today's date
=DATEDIF(start_date,end_date,"unit") -Calculates the difference between two dates in days, months or years
Other useful functions: NOW(),DAY(),MONTH(),YEAR()
Step 5:Summarizing & Visualizaing the Data
Visual data such as charts help us visualize trends or comparisons
i)Select your dataset
ii)Go to Insert → PivotTable → From Table/Range → Click on a New Worksheet → OK
iii)On the PivotTable Fields → Drag fields into Rows, Columns and values
iv)Go to Insert → Recommended Charts
v)Choose from column, line, pie or bar charts
Top comments (0)