madeshleo005@gmail.
com
LCEXZA8FK4
DATA ANALYTICS USING EXCEL
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Agenda
▪ Phases of a data analytics project
▪ Data Cleaning
▪ Functions
▪ Sort and filter
▪ Lookup functions
madeshleo005@[Link]
LCEXZA8FK4
▪ Conditional Formatting
▪ Data Validation
▪ Pivot Tables
▪ Data Visualisation using Excel
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
PHASES OF DATA ANALYTICS PROJECT
madeshleo005@[Link]
LCEXZA8FK4
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Phases of data analytics project
Data Data Data
madeshleo005@[Link]
LCEXZA8FK4
Cleaning Analysis Visualization
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Phases of data analytics project
Data Data
Data Cleaning Analysis Visualization
madeshleo005@[Link]
LCEXZA8FK4
Setting Up of problem statement
Performing preliminary analysis
Fixing misspellings / typos
Removing duplicates
Checking for bias
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Phases of data analytics project
Data Data Data
Cleaning Analysis Visualization
madeshleo005@[Link]
LCEXZA8FK4
Understanding the problem statement
Adjusting and formatting data
Finding Appropriate relationships between data sets
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Phases of data analytics project
Data Data Data
Cleaning Analysis Visualization
madeshleo005@[Link]
LCEXZA8FK4
Delivering data in most efficient way.
Helps make critical decisions in an
unbiased manner.
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
DATA CLEANING
madeshleo005@[Link]
LCEXZA8FK4
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Data Cleaning
Importance of Data Cleaning
▪ To ensure high data integrity.
▪ To check if there is any bias involved in data collection.
madeshleo005@[Link]
▪
LCEXZA8FK4 Data should be in-line with problem statement.
▪ Data should have complete sample size.
▪ There should be uniformity in data.
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
madeshleo005@[Link]
LCEXZA8FK4
FUNCTIONS
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Types of Functions
What are Functions?
They are pre defined instructions that perform specific calculations in a particular order in
a spreadsheet.
Basic syntax of function:-
= Formula(Range of cells)
madeshleo005@[Link]
LCEXZA8FK4
=AVERAGE(No.1, No.2)
Start of a
function
Range of cells
Formula name,
performs a specific
calculation
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Types of Functions
• COUNT IF
• LEN
• LEFT/RIGHT
madeshleo005@[Link]
LCEXZA8FK4
• CONCATENATE
• TRIM
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Types of Functions
COUNTIF
A function that counts the number of cells that match a specific condition.
= COUNTIF( Range, “Condition”)
madeshleo005@[Link]
LCEXZA8FK4
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Types of Functions
LENGTH
A function that returns the length of a text string.
madeshleo005@[Link]
LCEXZA8FK4= LEN(Cell)
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Types of Functions
LEFT/RIGHT
A function that returns specific number of characters from left/right side in a cell.
= LEFT( Cell, No. of characters)
madeshleo005@[Link]
LCEXZA8FK4
= RIGHT( Cell, No. of characters)
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Types of Functions
CONCATENATE
A function that combines multiple text cells into a single cell.
madeshleo005@[Link]
= CONCATENATE( CELL-1, CELL-2)
LCEXZA8FK4
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Types of Functions
TRIM
A function that removes extra unwanted spaces from the cell.
madeshleo005@[Link]
= TRIM( CELL-1)
LCEXZA8FK4
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
SORT AND FILTER
madeshleo005@[Link]
LCEXZA8FK4
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
madeshleo005@[Link]
LCEXZA8FK4
LOOKUP FUNCTIONS
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Lookup Functions
VLOOK UP
A function that searches for a certain value in a column to return a corresponding piece of
information.
= VLOOKUP(look up value, table array, column_index_num, true/false)
madeshleo005@[Link]
LCEXZA8FK4
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Lookup Functions
HLOOK UP
A function that searches for a certain value in a row to return a corresponding piece of information.
= HLOOKUP(look up value, table array, Row_num, true/false)
madeshleo005@[Link]
LCEXZA8FK4
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
madeshleo005@[Link]
LCEXZA8FK4
CONDITIONAL FORMATTING
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Conditional Formatting
An Excel tool that changes how cells look when value meets
specific conditions.
madeshleo005@[Link]
LCEXZA8FK4
DO NOT WRITE ANYTHING
HERE. LEAVE THIS SPACE FOR
WEBCAM
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
madeshleo005@[Link]
LCEXZA8FK4
DATA VALIDATION
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Data Validation
A tool that allows you to control what could or could not be entered in your spreadsheet.
madeshleo005@[Link]
LCEXZA8FK4
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
madeshleo005@[Link]
LCEXZA8FK4
PIVOT TABLES
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Pivot Tables
What are Pivot Tables?
• A Pivot Table is used to sort, count, group, total or reorganize data stored in a table.
• It helps us to Change rows into columns and columns into rows.
madeshleo005@[Link]
The Pivot fields (column) can be grouped, and advanced calculations could be performed on them.
LCEXZA8FK4
•
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
madeshleo005@[Link]
LCEXZA8FK4
DATA VISUALISATION USING EXCEL
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Data Visualization
PIE CHART
• Pie chart represents a part-to-whole
relationship.
madeshleo005@[Link]
LCEXZA8FK4
• Each pie slice is composed of three
components: area, central angle and arc
length.
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Data Visualization
BAR CHART
• A chart that uses bands of different
heights to show different amounts so that
madeshleo005@[Link]
LCEXZA8FK4
they can be distinguished.
• A bar chart is used to represent
Comparative data.
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Data Visualization
LINE CHART
• It is visual representation to show changes
over time.
madeshleo005@[Link]
LCEXZA8FK4
• The Category data is along the horizontal axis,
and all value data is along the vertical axis.
• It is preferred when one parameter is non
numeric.
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Data Visualization
TREE CHART
• Provides a hierarchical view of your data.
• It makes it easy to recognize patterns.
madeshleo005@[Link]
LCEXZA8FK4
• Good for comparing proportions within the
hierarchy.
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Data Visualization
MAP CHART
• It is used to navigate values and show
categories across different geographical regions.
madeshleo005@[Link]
LCEXZA8FK4
• Mostly used in a dataset that contains data
about Imports/Exports.
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Summary
▪ We discussed about the phases of a data analyst project.
▪ We learnt about the importance of data cleaning.
▪ Then we discussed about different types of function.
▪ Furthermore we learnt about conditional Formatting.
madeshleo005@[Link]
LCEXZA8FK4
▪ Then we got a brief overview of Data Validation and Pivot Tables.
▪ At the end we discussed about Visualizing data using Excel charts.
This file is meant for personal use by madeshleo005@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
madeshleo005@[Link]
LCEXZA8FK4
Thank You
This file is meant for personal use by madeshleo005@[Link] only.
Proprietary content.
Sharing or©Great Learning.
publishing theAllcontents
Rights Reserved.
in part Unauthorized usefor
or full is liable or distribution
legal [Link]