vanshikaj0906@gmail.
com
2PJ4VFKLDU
DATA ANALYTICS USING EXCEL
This file is meant for personal use by vanshikaj0906@[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
vanshikaj0906@[Link]
2PJ4VFKLDU
▪ Conditional Formatting
▪ Data Validation
▪ Pivot Tables
▪ Data Visualisation using Excel
This file is meant for personal use by vanshikaj0906@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
PHASES OF DATA ANALYTICS PROJECT
vanshikaj0906@[Link]
2PJ4VFKLDU
This file is meant for personal use by vanshikaj0906@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Phases of data analytics project
Data Data Data
vanshikaj0906@[Link]
2PJ4VFKLDU
Cleaning Analysis Visualization
This file is meant for personal use by vanshikaj0906@[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
vanshikaj0906@[Link]
2PJ4VFKLDU
Setting Up of problem statement
Performing preliminary analysis
Fixing misspellings / typos
Removing duplicates
Checking for bias
This file is meant for personal use by vanshikaj0906@[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
vanshikaj0906@[Link]
2PJ4VFKLDU
Understanding the problem statement
Adjusting and formatting data
Finding Appropriate relationships between data sets
This file is meant for personal use by vanshikaj0906@[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
vanshikaj0906@[Link]
2PJ4VFKLDU
Delivering data in most efficient way.
Helps make critical decisions in an
unbiased manner.
This file is meant for personal use by vanshikaj0906@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
DATA CLEANING
vanshikaj0906@[Link]
2PJ4VFKLDU
This file is meant for personal use by vanshikaj0906@[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.
▪ Data should be in-line with problem statement.
vanshikaj0906@[Link]
2PJ4VFKLDU
▪ Data should have complete sample size.
▪ There should be uniformity in data.
This file is meant for personal use by vanshikaj0906@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
vanshikaj0906@[Link]
2PJ4VFKLDU
FUNCTIONS
This file is meant for personal use by vanshikaj0906@[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)
vanshikaj0906@[Link]
2PJ4VFKLDU
=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 vanshikaj0906@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
Types of Functions
• COUNT IF
• LEN
vanshikaj0906@[Link]
2PJ4VFKLDU
• LEFT/RIGHT
• CONCATENATE
• TRIM
This file is meant for personal use by vanshikaj0906@[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”)
vanshikaj0906@[Link]
2PJ4VFKLDU
This file is meant for personal use by vanshikaj0906@[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.
= LEN(Cell)
vanshikaj0906@[Link]
2PJ4VFKLDU
This file is meant for personal use by vanshikaj0906@[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)
vanshikaj0906@[Link]
2PJ4VFKLDU
= RIGHT( Cell, No. of characters)
This file is meant for personal use by vanshikaj0906@[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.
= CONCATENATE( CELL-1, CELL-2)
vanshikaj0906@[Link]
2PJ4VFKLDU
This file is meant for personal use by vanshikaj0906@[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.
vanshikaj0906@[Link]
2PJ4VFKLDU
= TRIM( CELL-1)
This file is meant for personal use by vanshikaj0906@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
SORT AND FILTER
vanshikaj0906@[Link]
2PJ4VFKLDU
This file is meant for personal use by vanshikaj0906@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
vanshikaj0906@[Link]
2PJ4VFKLDU
LOOKUP FUNCTIONS
This file is meant for personal use by vanshikaj0906@[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)
vanshikaj0906@[Link]
2PJ4VFKLDU
This file is meant for personal use by vanshikaj0906@[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)
vanshikaj0906@[Link]
2PJ4VFKLDU
This file is meant for personal use by vanshikaj0906@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
vanshikaj0906@[Link]
2PJ4VFKLDU
CONDITIONAL FORMATTING
This file is meant for personal use by vanshikaj0906@[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.
vanshikaj0906@[Link]
2PJ4VFKLDU
DO NOT WRITE ANYTHING
HERE. LEAVE THIS SPACE FOR
WEBCAM
This file is meant for personal use by vanshikaj0906@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
vanshikaj0906@[Link]
2PJ4VFKLDU
DATA VALIDATION
This file is meant for personal use by vanshikaj0906@[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.
vanshikaj0906@[Link]
2PJ4VFKLDU
This file is meant for personal use by vanshikaj0906@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
vanshikaj0906@[Link]
2PJ4VFKLDU
PIVOT TABLES
This file is meant for personal use by vanshikaj0906@[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.
vanshikaj0906@[Link]
2PJ4VFKLDU
• The Pivot fields (column) can be grouped, and advanced calculations could be performed on them.
This file is meant for personal use by vanshikaj0906@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
vanshikaj0906@[Link]
2PJ4VFKLDU
DATA VISUALISATION USING EXCEL
This file is meant for personal use by vanshikaj0906@[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
vanshikaj0906@[Link]
relationship.
2PJ4VFKLDU
• Each pie slice is composed of three
components: area, central angle and arc
length.
This file is meant for personal use by vanshikaj0906@[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
vanshikaj0906@[Link]
heights to show different amounts so that
2PJ4VFKLDU
they can be distinguished.
• A bar chart is used to represent
Comparative data.
This file is meant for personal use by vanshikaj0906@[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
vanshikaj0906@[Link]
over time.
2PJ4VFKLDU
• 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 vanshikaj0906@[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.
vanshikaj0906@[Link]
2PJ4VFKLDU
• Good for comparing proportions within the
hierarchy.
This file is meant for personal use by vanshikaj0906@[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
vanshikaj0906@[Link]
categories across different geographical regions.
2PJ4VFKLDU
• Mostly used in a dataset that contains data
about Imports/Exports.
This file is meant for personal use by vanshikaj0906@[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.
vanshikaj0906@[Link]
2PJ4VFKLDU
▪ 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 vanshikaj0906@[Link] only.
Sharing or publishing the contents in part or full is liable for legal action.
vanshikaj0906@[Link]
2PJ4VFKLDU
Thank You
Proprietary content. ©Great Learning. All for
This file is meant Rights
personalReserved. Unauthorized
use by vanshikaj0906@[Link] [Link] or distribution prohibited
Sharing or publishing the contents in part or full is liable for legal action.