Welcome to the World of Data Analytics!

Before going into depth of Data Analytics, let's know Data a bit and it's importance,

#1. What is Data

Think of data as raw ingredients in a kitchen. Just like vegetables, spices, and grains, data can be
numbers, text, or even clicks on a website! By itself, it’s just a jumble of information, but with the
right recipe, you can turn it into something delicious—meaningful insights!

#2. Why is Data Important for Companies

Imagine running a lemonade stand without knowing which flavors your customers like best. Data
is like a super-smart friend who tells you what’s working, what’s not, and how to improve your
business! It helps companies make smarter decisions, keep customers happy, and be more
successful overall. 🍋📈

#3. Data Analytics and its Steps

Data Analytics is like "the Detective for Numbers" 🔍

Imagine you’re a detective, and instead of solving a crime, your job is to solve a mystery hidden
within numbers and data points. Here’s how you, the “Data Detective,” crack the case:

Data Collection: Gathering Clues ‍♂️

Just like a detective gathers clues from the crime scene, a data analyst collects raw data from
various sources. This could be anything from sales reports to website traffic data. Your job is to
gather as much relevant information as possible.

Data Cleaning: Removing Red Herrings

Not all clues are useful—some are just distractions! In data analytics, you clean the data by
removing errors, duplicates, or irrelevant information, just like a detective would ignore misleading
evidence.
Data Analysis: Piecing the Puzzle Together 🧩
Now, you start analyzing the cleaned data. This is where you look for patterns and connections,
like piecing together a jigsaw puzzle. The goal is to find the hidden insights or the “motive” behind
the data’s behavior.

Data Visualization: Creating the Storyboard 📊

Once you have your insights, you create a visual storyboard using charts, graphs, or dashboards.
This helps explain the story behind the data in a way that’s easy to understand—just like
presenting your case findings in court.

Interpretation: Solving the Mystery 🏆

Finally, you present your findings to the stakeholders, helping them make informed decisions. This
is where you explain why something happened and recommend what to do next—similar to
revealing the criminal and the motive in a detective story!

#4. Types of Data Analysis

1. Descriptive Analysis:
🎉 What Happened? This analysis acts like a storyteller, summarizing historical data to provide a
clear picture of past events. It focuses on metrics and key performance indicators (KPIs) to
understand trends over time.
Key Features:
Uses measures like averages, totals, and percentages.
Helps in creating reports and dashboards that visualize data.

2. Diagnostic Analysis:

🔍 Why Did It Happen? This is where the detective work begins! Diagnostic analysis digs deeper into
the data to identify causes behind certain trends or anomalies. It often involves comparing
different datasets to find correlations.
Key Features:
Utilizes techniques like data mining and correlation analysis.
Helps in identifying patterns that explain why certain events occurred.

3. Predictive Analysis:

🔮 What Will Happen Next? This analysis is like peering into a crystal ball! Predictive analysis uses
statistical models and machine learning algorithms to forecast future outcomes based on historical
data. It helps businesses anticipate trends and prepare accordingly.
Key Features:
Involves techniques such as regression analysis and time series forecasting.
Provides insights that can lead to proactive decision-making.

4. Prescriptive Analysis:

What Should We Do? This is the action-oriented analysis! Prescriptive analysis recommends
specific actions based on insights derived from descriptive, diagnostic, and predictive analyses. It
helps organizations make informed decisions that optimize outcomes.
Key Features:
Utilizes optimization algorithms and simulation models.
Offers actionable recommendations tailored to business objectives.

#5. How Companies Use Data Analytics—From Netflix to Pizza Shop

1. Netflix: When you binge-watch Stranger Things, Netflix uses data analytics to recommend other
shows you might like. It knows your watching habits better than your best friend. They look at
everything—what shows you click on, how long you watch, and even when you hit pause. This is
how they keep you hooked.

2. Spotify: Ever wonder how your Discover Weekly playlist is so on point? Spotify’s algorithm digs
through data from millions of users to suggest songs that match your music taste. It's basically a
data DJ!

3. Pizza Shop (Yes, your favourite pizzaaaaaaa!): Let’s say you run a promotion on Sundays for half-
price pizza. Your data shows that more people are ordering pizza at 5 p.m. than any other time.
What can you do with this info? Maybe extend the promotion to Saturday or start an afternoon
pizza deal. That’s data-driven decision-making.

#6. The Role of A Data Analyst

A Data Analyst is like that friend who always has the tea (information) and helps you make the
best decisions. ☕ They gather, clean, and analyze data to uncover insights that guide businesses in
making informed choices. Here’s how they do it:

 Curious Detective: They ask questions to understand what the business needs, like why
sales dropped last month.

 Data Treasure Hunter: They collect data from various sources, like customer feedback and
sales reports, finding the golden nuggets of information.

 Data Cleaner: They ensure the data is accurate and tidy, just like making sure your room is
clean before throwing a party!

 Data Explorer: They analyze trends and patterns, spotting opportunities for growth (e.g.,
noticing more pizzas sell on weekends).

 Storyteller: They present findings through visuals, making complex data easy to
understand—like turning a boring lecture into a fun story!

 Decision-Maker: They provide actionable insights that help businesses decide their next
steps, like launching a new promotion.

 Continuous Learner: They keep updating their skills to stay ahead in the ever-evolving
world of data.

So, the next time you hear "data analyst," think of them as your go-to friend for insights that can
make a big difference! 🌟

#7. Tools to become a Data Analyst

Data analysts have their secret weapons: Excel, SQL, Python, and many more . They’re like the
Avengers of data!

In the upcoming weeks, you’ll become proficient in these powerful tools, preparing you to take on
the world of data analysis with confidence! 🌟
Excel Foundations

Q. How to select particular column upto certain row or upto a certain table row?

Method : Using Name Box

1. Click on the Name Box (top left, next to formula bar).

2. Type A1:A5000 (or 1:5000 for entire rows).

3. Press Enter to select the range instantly.

1. Introduction to MS-Excel

Microsoft Excel is a spreadsheet program used for storing, organizing, and manipulating data. It
provides tools for performing calculations, creating charts, and analyzing large datasets.
Examples:

Ribbon Overview: The Ribbon in Excel is divided into several tabs (Home, Insert, Data, etc.), each
containing a set of tools. For example, the Data Tab is used for importing and analyzing external data.

Worksheets and Cells: Excel organizes data into worksheets, where each worksheet is made up of
rows and columns. The intersection of a row and a column is called a cell, which is identified by a cell
address (e.g., A1, B2). You can enter data into these cells.

Quick Access Toolbar: This toolbar is located at the top of the Excel window and contains commonly
used commands such as Save, Undo, and Redo.

Formula Bar: Displays the contents or formulas of the active cell. For instance, if you
input =SUM(A1:A10), the formula bar will display it, showing the calculation of the sum of values
from A1 to A10.

2. Basic Features in Excel

Excel offers several basic features that make it easy to organize and analyze data, including sorting,
filtering, and formatting options.

Examples:

1. Sorting Data: Sort the "products.csv" file by BuyPrice to list products from most expensive to
least expensive.

ProductCode ProductName BuyPrice

S18_2795 1952 Citroen-15CV $60.50

S18_1749 1969 Harley Davidson $51.30

S18_2248 1952 Alpine Renault $34.30

Go to Data → Sort → Choose the column to sort by (BuyPrice) and select "Descending".

2. Filtering Data: Filter the "customers.csv" file to show only customers from the USA.

CustomerNumber CustomerName Country

112 Signal Gift Stores USA

124 Mini Gifts USA

Go to Data → Filter → Apply a filter on the Country column for "USA".

3. AutoSum: Use the SUM() function to calculate the total sales from the "orders.csv" file.
 OrderNumber OrderDate TotalAmount

10101 2023-05-10 $1,250

10102 2023-06-12 $3,000

Formula

=SUM(C2:C3) // Total sales: $4,250

4. Text Formatting: Format the text in the "products.csv" file by making the product names
bold and setting a currency format for prices.

ProductCode ProductName BuyPrice

1948 Porsche
S18_2325 $43.40
Type 356

Use the Home Tab → Font section to bold the ProductName and format BuyPrice as currency.

3. Data Validation in Excel

Data validation allows you to set rules that restrict what type of data can be entered into cells.

Examples:

1. Restricting Buy Prices: Set a data validation rule in the "products.csv" file so that
the BuyPrice must be between 20and20and100.

ProductCode ProductName BuyPrice

1969 Harley
S18_1749 $51.30
Davidson

1952 Alpine
S18_2248 $34.30
Renault

Go to Data → Data Validation → Set a rule that only allows decimal values between 20 and 100.

2. Order Quantity Limits: Restrict the QuantityOrdered in "order details.csv" to values between
1 and 100.

ProductCode QuantityOrdered

S18_1749 25
 ProductCode QuantityOrdered

S18_2248 40

Go to Data Validation → Set the criteria as a whole number between 1 and 100.

3. Date Range: Ensure that the OrderDate in "orders.csv" falls between January 1, 2022, and
December 31, 2023.

OrderDate ProductCode QuantityOrdered

2023-07-05 S18_1749 25

2022-09-15 S18_2325 45

Apply Data Validation → Date Range → Between 01/01/2022 and 12/31/2023.

4. Restricting Product Codes: Prevent duplicate entries in the "products.csv" file by ensuring
each ProductCode is unique.

ProductCode ProductName BuyPrice

1969 Harley
S18_1749 $51.30
Davidson

1948 Porsche
S18_2325 $43.40
Type 356

Use a custom data validation rule with the formula =COUNTIF(A:A, A1) = 1 to restrict duplicate
entries.
4. Conditional Formatting in Excel

Conditional formatting is a feature that allows you to format cells based on their values.

Examples:

1. Highlight High-Value Payments: In the "payments.csv" file, highlight payments greater than
$5000.

CustomerNumber PaymentAmount

112 $5,750

121 $3,300

Go to Home → Conditional Formatting → Greater Than $5000 and apply a color.

2. Highlight Low Stock Products: In the "products.csv" file, highlight products with
a BuyPrice lower than $30.
 ProductCode ProductName BuyPrice

1952 Alpine
S18_2248 $34.30
Renault

1948 Porsche
S18_2325 $43.40
Type 356

Go to Conditional Formatting → Highlight Cells with values below $30.

3. Top 5 Products by Quantity Ordered: Highlight the top 5 products based on

the QuantityOrdered in "order details.csv".

ProductCode QuantityOrdered

S18_1749 50

S18_2248 40

Apply Conditional Formatting → Top/Bottom Rules → Top 5 Items.

4. Color Scale for Sales: Apply a color scale to the TotalAmount column in "orders.csv" to
visualize sales distribution.

OrderNumber TotalAmount

10101 $1,250

10102 $3,000

Go to Conditional Formatting → Color Scales → Select a gradient to represent different sales values.

5. Sorting Data in Excel

Sorting helps you organize data in ascending or descending order based on a specific column.

Examples:

1. Sort by Product Price: Sort the "products.csv" file by BuyPrice in descending order to see the
most expensive products first.

ProductCode ProductName BuyPrice

S18_2795 1952 Citroen- $60.50

 ProductCode ProductName BuyPrice

15CV

1948 Porsche
S18_2325 $43.40
Type 356

Go to Data → Sort → Choose "BuyPrice" and select "Descending".

2. Sort by Customer Name: Sort the "customers.csv" file by CustomerName alphabetically.

CustomerNumber CustomerName Country

103 Atelier graphique France

112 Signal Gift Stores USA

Select the CustomerName column, go to Data → Sort A-Z.

3. Sort by Payment Date: In the "payments.csv" file, sort by PaymentDate to organize payments
chronologically.

CustomerNumber PaymentDate Amount

103 2023-08-10 $4,500

112 2023-08-12 $5,750

Select the PaymentDate column and apply Sort from oldest to newest.

6. Filtering Data in Excel

Filtering allows you to display only the data that meets specific criteria.

Examples:

1. Filter by Country: In the "customers.csv" file, filter the data to show only customers from
France.

CustomerNumber CustomerName Country

103 Atelier graphique France

128 Cafeter
7. Dealing with Duplicate Values in Excel

Duplicate values in your dataset can lead to misleading insights. Here’s how you can handle them
using Excel’s built-in features.

Examples:

1. Remove Duplicate Product Records: In the products.csv file, each ProductCode should be
unique. We will identify and remove any duplicates to maintain data accuracy.

ProductCode ProductName BuyPrice MSRP

1969 Harley
S18_1749 $51.30 $95.70
Davidson

S18_2248 1952 Alpine Renault $34.30 $66.00

1969 Harley
S18_1749 $51.30 $95.70
Davidson

Steps:

 Select the columns ProductCode, ProductName, and BuyPrice.

 Go to Data → Remove Duplicates.

 Check the columns you want to compare, then click OK.

 Identify Duplicate Customer Records: In the customers.csv file,

duplicate CustomerName entries can cause redundant data. We’ll identify these records to
ensure each customer is represented once.

CustomerNumber CustomerName City Country

103 Atelier graphique Paris France

112 Signal Gift Stores San Francisco USA

103 Atelier graphique Paris France

Steps:

 Select the columns CustomerName, City, and Country.

 Use Conditional Formatting → Highlight Cells Rules → Duplicate Values to highlight

duplicates in CustomerName.
  Remove Duplicate Payment Records: In the payments.csv file, ensure that
each CustomerNumber and CheckNumber ombinationis unique to avoid double-counting
payments.

CustomerNumber CheckNumber PaymentDate Amount

112 HQ336336 2023-08-12 $5,750

114 ND748623 2023-09-15 $8,050

112 HQ336336 2023-08-12 $5,750

Steps:

 Select columns CustomerNumber and CheckNumber.

 Go to Data → Remove Duplicates.

 Verify the correct columns are selected and click OK.

 Highlight Duplicate Order Records: In the orders.csv file, check for

repeated OrderNumber entries to ensure each order is listed once.

OrderNumber OrderDate CustomerNumber Status

2023-05-
10101 103 Shipped
10

2023-05-
10102 112 Cancelled
12

2023-05-
10101 103 Shipped
10

Steps:

 Select columns OrderNumber and CustomerNumber.

 Go to Data → Remove Duplicates to eliminate duplicate orders.

8. Dealing with Null Values in Excel

Null values can disrupt data analysis and lead to errors. It’s crucial to handle these values
appropriately to maintain data integrity.

Examples:
 1. Identify Missing Product Prices: In the products.csv file, check for missing BuyPrice values
and replace them with a default or average price.

ProductCode ProductName BuyPrice MSRP

1969 Harley
S18_1749 $51.30 $95.70
Davidson

S18_2248 1952 Alpine Renault $66.00

S18_4409 1936 Mercedes-Benz $37.25 $72.00

Steps:

 Select the BuyPrice column.

 Go to Conditional Formatting → Highlight Cells Rules → Blanks.

 Replace missing values with the formula =IF(ISBLANK(C2), AVERAGE(C:C), C2).

 Replace Missing Payment Amounts: In the payments.csv file, any missing Amount values can
be replaced with 0 or another default value.

CustomerNumber CheckNumber PaymentDate Amount

103 HQ336336 2023-08-12 $4,500

112 ND748623 2023-09-15

114 XZ836258 2023-08-25 $8,050

Steps:

 Highlight the Amount column.

 Use Find & Select → Go To Special → Select Blanks.

 Type 0 and press Ctrl + Enter to fill all blank cells with 0.

 Fill Missing Customer Information: In the customers.csv file, ensure that

all CustomerName and Country fields are filled.

CustomerNumber CustomerName City Country

103 Atelier graphique Paris France

 CustomerNumber CustomerName City Country

112 Signal Gift Stores San Francisco

119 Tokyo Japan

Steps:

 Select columns CustomerName and Country.

 Apply Conditional Formatting → Highlight Cells Rules → Blanks.

 Fill missing values manually or use logical formulas (e.g., =IF(ISBLANK(B2), "Unknown", B2)).

 Handle Null Order Dates: In the orders.csv file, flag any missing OrderDate values and
replace them with a default or estimated date.

OrderNumber OrderDate CustomerNumber Status

10101 2023-05-10 103 Shipped

10102 112 Cancelled

10103 2023-05-13 119 Processing

Steps:

 Select the OrderDate column.

 Use Conditional Formatting → Highlight Cells Rules → Blanks.

 Use a formula like =IF(ISBLANK(B2), "2023-01-01", B2) to fill missing dates with a default
value.
Data Cleaning & Functions in Excel

1. Removing Whitespaces from Data

Whitespaces, especially leading or trailing spaces, can cause issues when analyzing data. Excel
provides tools to remove these unnecessary spaces, ensuring that data is clean and consistent.

Examples:

1. Removing Leading and Trailing Spaces: In the "customers.csv" file, some customer names
may have extra spaces that affect sorting or searching.

CustomerNumber CustomerName

103 " Atelier graphique "

112 "Signal Gift Stores "

Steps:

 Use the TRIM() function to remove extra spaces:

=TRIM(B2)

This removes any leading and trailing spaces from the customer names.

2. Removing Spaces from Product Names: In the "products.csv" file, remove unwanted spaces
from ProductName for cleaner data.

ProductCode ProductName

S18_1749 " 1969 Harley Davidson "

S18_2248 "1952 Alpine Renault "

 Use the TRIM() function:

=TRIM(B2)

3. Cleaning Payment Data: In the "payments.csv" file, ensure there are no extra spaces in
the CustomerNumber or Amount fields to prevent issues in analysis.

CustomerNumber PaymentDate Amount

112 2023-08-12 " 5750"

121 2023-08-18 " 3300"

 Apply the TRIM() function to the Amount column to remove any leading or trailing spaces:

=TRIM(C2

4. Combining and Cleaning Names: If FirstName and LastName are stored separately in a
dataset, like in the "employees.csv" file, use TRIM() when combining them to avoid extra
spaces.

FirstName LastName

John " Doe "

Jane "Smith "

=TRIM(A2) & " " & TRIM(B2)

2. Fixing Column Formats in Excel

Column formats are crucial when working with numerical, date, or text data. Incorrect formats can
cause calculation errors or make the data unreadable.
Examples:

1. Fixing Date Formats: In the "orders.csv" file, ensure that all dates are correctly formatted
as Date types instead of text.

OrderNumber OrderDate TotalAmount

10101 "2023/05/10" $1,250

10102 "2023-06-12" $3,000

Steps:

 Select the OrderDate column.

 Go to Home → Number Format → Short Date to standardize the format.

2. Fixing Currency Formats: In the "payments.csv" file, ensure that payment amounts are
formatted as currency.

CustomerNumber PaymentDate Amount

103 2023-08-10 4500

112 2023-08-12 5750

Steps:

 Select the Amount column.

 Go to Home → Number Format → Currency to format all payment amounts with the
appropriate currency symbols.

3. Standardizing Product Prices: In the "products.csv" file, ensure the BuyPrice is formatted
with two decimal places for better consistency.

ProductCode ProductName BuyPrice

1969 Harley
S18_1749 51.3
Davidson

1952 Alpine
S18_2248 34
Renault

Steps:

 Select the BuyPrice column.

 Go to Home → Number Format → Number, then set Decimal Places to 2.

 4. Text Alignment: Fix text alignment in the "customers.csv" file to ensure names and addresses
are left-aligned.

CustomerNumber CustomerName Country

103 Atelier graphique France

112 Signal Gift Stores USA

Steps:

 Select the relevant columns.

 Go to Home → Alignment → Align Left.

3. Text Functions in Excel

Text functions in Excel help manipulate and clean up text-based data, making it easier to manage
large datasets.

Examples:

1. Concatenating First and Last Names: In the "employees.csv" file,

combine FirstName and LastName into a single full name.

FirstName LastName

John Doe

Jane Smith

Steps:

 Use the CONCATENATE() function or & to combine names:

=A2 & " " & B2

2. Extracting Product Codes: In the "order details.csv" file, extract the first 3 characters of
the ProductCode to group similar products.

ProductCode QuantityOrdered

S18_1749 25

S18_2325 45

Steps:
  Use the LEFT() function to extract the first 3 characters:

=LEFT(A2, 3)

3. Finding Text in Product Names: In the "products.csv" file, find all products containing the
word "Harley".

ProductCode ProductName

S18_1749 1969 Harley Davidson

S18_2248 1952 Alpine Renault

Steps:

 Use the SEARCH() function to find if "Harley" appears in ProductName:

=SEARCH("Harley", B2)

4. Changing Case: In the "customers.csv" file, convert customer names to uppercase for
consistency.

CustomerNumber CustomerName

103 Atelier graphique

112 Signal Gift Stores

Steps:

 Use the UPPER() function:

=UPPER(B2)

4. Logical Functions in Excel

Logical functions allow you to perform conditional operations based on specific criteria,
returning TRUE or FALSE.

Examples:

1. Conditional Discounts: In the "order details.csv" file, give a discount if QuantityOrdered is

greater than 50.
 ProductCo QuantityOrd UnitPri DiscountedP
de ered ce rice

S18_174
60 $25
9

S18_224
40 $35
8

Steps:

 Use the IF() function to apply a 10% discount for large orders:

=IF(B2 > 50, C2 * 0.9, C2)

2. Checking Late Payments: In the "payments.csv" file, check if the payment is late by
comparing PaymentDate with the due date.

CustomerNumber PaymentDate DueDate Late?

2023-08-
103 2023-08-10
05

2023-08-
112 2023-08-12
15

Steps:

 Use the IF() function to determine if the payment is late:

=IF(B2 > C2, "Yes", "No")

3. Checking Stock Levels: In the "products.csv" file, check if the StockLevel is below the reorder
level of 20.

ProductCode ProductName StockLevel ReorderNeeded?

1969 Harley
S18_1749 18
Davidson

1948 Porsche
S18_2325 25
Type 356

Steps:

 Use the IF() function to flag products that need to be reordered:

=IF(C2 < 20, "Yes", "No")

 4. Multiple Conditions with AND/OR: In the "orders.csv" file, check if an order is both large
(over $2,000) and placed in 2023.

OrderNumber OrderDate TotalAmount Large?

10101 2023-05-10 $1,250

10102 2023-06-12 $3,000

Steps:

 Use the AND() function:

=IF