Data Analytics Using Excel Lecture Notes

Introduction

• Data is pervasive: texting, online shopping, social media.
• Importance of data analysis to uncover trends and opportunities.
• Data analytics is a sought-after domain for career advancement.
• Great Learning offers a course to help kickstart data analytics journey.

Learning Objectives

• Understand the phases of a data analytics project.
• Importance and methods of data cleaning.
• Familiarity with basic Excel functions and formulas.
• Techniques for sorting and filtering data in Excel.
• Introduction to lookup functions (VLOOKUP and HLOOKUP).
• Conditional formatting techniques.
• Data validation methods, including creating dropdown lists.
• Understanding pivot tables and their significance.
• Data visualization using various chart types.

Phases of a Data Analytics Project

1. Data Cleaning

• Set up a problem statement: Identify specific business questions (e.g., customer purchases influenced by ads).
• Initial data analysis: Check for completeness, consistency, and accuracy.
• Correct errors: Eliminate misspellings, typos, and duplicates.
• Remove bias: Ensure unbiased questions in surveys to obtain a clear picture.

2. Data Analysis

• This phase involves understanding the problem, finding relationships, and formatting the data.
• Techniques include:
  • Lookup functions
  • Conditional formatting
  • Data validation
  • Pivot tables

3. Data Visualization

• Use visual methods to present analysis efficiently to stakeholders.
• Types of charts:
  • Bar charts, line charts, pie charts, scatter plots, tree charts, and map charts.

Data Cleaning Techniques

• Excel is the dominant platform for data analysis.
• Removing duplicates: Use Excel's built-in function.
• Check for irregularities (e.g., unreasonable values).
• Maintain a log of changes and keep a backup of the raw data.

Functions in Excel

Basic Functions

1. Functions: Predefined instructions for calculations.
2. Examples of essential functions: COUNTIF, LEN, LEFT, RIGHT, CONCATENATE, TRIM.

COUNTIF Function

• Counts cells that meet specific criteria.
• Syntax: =COUNTIF(range, criteria).

LEN, LEFT, and RIGHT Functions

• LEN: Returns length of a text string.
• LEFT/RIGHT: Returns specified number of characters from the left/right side of a string.

CONCATENATE Function

• Combines multiple text strings into one cell.

TRIM Function

• Removes extra spaces from text within cells.

Sorting and Filtering Data

• Sorting: Rearrange data in ascending or descending order.
• Filtering: Narrow down data view based on specific criteria.

Lookup Functions

VLOOKUP

• Searches for a value in the first column and returns a corresponding value from another column.
• Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).

HLOOKUP

• Similar to VLOOKUP, but searches through rows instead of columns.

Conditional Formatting

• Formats cells based on certain conditions (e.g., highlight cells with values above a threshold).
• Features include:
  • Highlight cell rules
  • Top and bottom rules
  • Data bars, color scales, icon sets.

Data Validation

• Controls what can be inputted into the spreadsheet.
• Examples:
  • Set limits on text length.
  • Create dropdown lists.
  • Define ranges for numerical inputs.

Pivot Tables

• Summarizes and reorganizes data for analysis.
• Allows for dynamic reporting and multiple data perspectives.

Data Visualization

• Types of Charts: Pie charts, bar charts, line charts, tree charts, and map charts.
• Pie Chart: Represents part-to-whole relationships.
• Bar Chart: Shows differences between categories.
• Line Chart: Displays changes over time.
• Tree Map and Map Chart: Hierarchical data and geographical representation respectively.

Conclusion

• Recap of the phases of data analytics, cleaning, functions, validating, pivot tables, and visualization.
• Encouragement to subscribe for more learning resources and to engage with the content.