Kipyegon Patrick

Posted on Jan 25

Introduction to MS Excel for Data Analytics

#analytics #data #datascience #beginners

Microsoft Excel is a spreadsheet application that is mostly used for data analytics. It helps one to collect, clean, analyze, visualize and report data for decision making purposes. The beauty of Microsoft Excel is the fact it is easy to learn and is user friendly.
The application allows one to do the following:

Collect and store data
Clean and organize data
Perform calculations such as sum(+), averages(/), differences(-) and products(*)
Analyze the data and visualize the trends

This article will detail a beginner data analytics learning guide step by step.
Data in excel is arranged in rows (horizontal line of cells) and columns (vertical line of cells). A cell is the box formed where a row and column intersect. Data is entered and stored in the cells as shown bellow:

What is data analytics?

Data analytics is the science of examining raw data to determine:
a) Patterns
b) Trends
c) Relationships and
d) Insights that are used for decision making

Why use Excel for Data Analytics

Excel remains a popular tool because of the following reasons:

It is easy to learn and use and is widely available
It handles datasets efficiently
It comes with built-in-formulas and functions
It provides quick options for creation of charts and dashboards
It works well with other tools such as Power BI, SQL and Python

Organizing data for analysis

Excel as an analytic tool only works well with a well structured dataset. The data should have one clear header, every row and column should each represent one unique record. Also remove blanks and duplicates.

Data Cleaning

Removing duplicates:

Select the dataset -> click home -> conditional formatting -> highlight cell rules -> duplicate values -> choose a color -> Ok.

Fixing data types:

Format dates as dates, numeric values as numbers, currencies as currency and texts as Text. Correct data types are critical for accurate analysis.

Handling missing values:

Find and impute the missing values.
Highlight blanks by; selecting data range -> go to home -> conditional formatting -> new rule -> choose format only cells that contain blanks -> pick a colour click ok

Sorting and Filtering data

Sort data (A-Z, largest to smallest and vice versa) you can also filter to view specific values only.

Performing Calculations using operators

Formulas in Excel start with equal to sign (=) and with the operators below:
.Addition (+)
.Subtraction (-)
.Division (/)
.Multiplication (*)
.Exponent (^)

Addition operator

Subtraction operator

Division operator

Multiplication operator

Performing Calculations using Formulas

Sum

=Sum(E2:E10)

Average

AVERAGE(E2:E10)

Maximum

MAX(E2:E10)

Minimum

MIN(E2:E10)

Count

Count(E2:E10)

COUNTIF

COUNTIF(E2:E10,">90000")

Lookup Formulas

VLOOKUP

Finds data vertically
VLOOKUP(10007,A2:E10,4,FALSE)

HLOOKUP

Finds data horizontally
HLOOKUP(10007,A1:J5,4,FALSE)

XLOOKUP

Is a more advanced lookup functions can find data both vertically and horizontally and is less error-prone.

Pivot Tables

Allows one to summarize large datasets, group and aggregate data and to compare values easily.

Steps to create a Pivot Table

. Select your data
. Go to insert -> Pivot Table
. Choose where to place, as a best practice choose new worksheet
. Drag fields into rows (departments), columns and values(salaries)

Charts in excel

Common types includes:
. Column Chart- used when comparing categories
. Line Chart- used to show trends over time
. Pie Chart - used to show proportions