As students of Data Analysis with Excel and Power BI, you will be introduced to essential tools, techniques, and workflows used for analyzing real-world data.

Data Cleaning

What You’ll Learn:

1. Clean datasets by identifying and removing duplicates or filling missing values

2. Format data consistently — including dates, numbers, and text

3. Fix common entry errors and resolve inconsistencies across records

4. Use Excel and Power BI to split, merge, and restructure data columns as needed

Text Functions
What You’ll Learn:

1. Understand how to use key Excel text functions like TRIM, LEFT, RIGHT, SUBSTITUTE, and more

2. Apply these functions to clean and transform text data in real-world datasets.

Background:
Netflix Inc. aims to improve its content recommendation system and customer satisfaction by ensuring its content library data is clean, accurate, and ready for analysis using Excel and Power BI. The company discovered issues such as missing values, duplicate records, and incorrect classifications, which could affect the performance of its recommendation engine.

Task Overview:

As a Data Analyst at Netflix, your role is to clean and prepare the company’s dataset for analysis using Excel and Power BI. The dataset contains details about movies and TV shows, including titles, genres, release dates, ratings, and user engagement metrics.

Your Responsibilities:

1. Use Excel and Power BI to identify columns with missing data

2. Apply appropriate methods to handle missing values — such as replacing with defaults, imputing, or removing affected rows or columns

3. Detect and remove duplicate records to ensure the dataset contains only unique entries

4. Check and correct formatting inconsistencies in fields like dates, ratings, and text entries

5. Standardize categorical values (e.g., genres, countries) for uniformity across the dataset

6. Validate numerical fields (e.g., duration, user ratings) to confirm they fall within expected ranges

7. Review and cross-check time-based fields to ensure date and time logic is accurate and consistent

What you’ll learn 

• • Understand how to use logical functions like IF, IFS, and nested IF statements

  • Apply conditional formulas such as SUMIF, COUNTIF, and AVERAGEIF

  • Use advanced logic to filter, segment, and analyze datasets based on specific criteria

  Functions

ACTIVITIES:

• • Create conditional rules using logical functions to highlight and categorize data

  • Use Excel formulas to calculate totals, averages, and counts based on conditions

  • Apply multiple criteria using functions like SUMIFS, COUNTIFS, and AVERAGEIFS

  • Practice filtering and segmenting datasets to uncover insights from sales or customer data

  combining with AVERAGEIFS).

Background:
A global retail company wants to optimize its marketing campaigns to increase sales and improve customer engagement. The marketing team has collected sales data from various campaigns over the last year, including information about customers, sales transactions, and campaign-specific details. Your task is to analyze this data using logical functions to identify trends, segment customers, and recommend strategies to improve future campaigns.

Your Responsibilities:

1. Use logical functions to segment customers based on various criteria such as age group, purchase amount, and loyalty program membership.
2. Identify high-value customers who consistently make large purchases or frequently engage with marketing campaigns.
3. Determine the effectiveness of different campaigns by calculating total sales, average purchase amount, and the number of transactions for each campaign.
4. Use logical functions to filter and compare the performance of campaigns based on specific conditions, such as region, discount application, and sales channels.
5. Use logical functions to identify trends in sales data, such as peak purchase times, popular product categories, and regional preferences.
6. Analyze the behavior of loyalty program members compared to non-members using logical conditions.
7. Based on your analysis, recommend strategies to optimize future marketing campaigns, such as targeting specific customer segments, adjusting discount strategies, or focusing on high-performing channels and regions.
8.  

What you’ll learn

• Introduction to VLOOKUP
• Practicing basic VLOOKUP with simple datasets.
• Nesting VLOOKUP within other formulas for advanced functionality.
• Explore alternative lookup functions like INDEX and MATCH.
• Learning how to combine INDEX and MATCH to create powerful lookup formulas.
• Understanding the advantages of using INDEX and MATCH over VLOOKUP.

Background:
A retail company is looking to enhance its sales reporting by integrating additional data from various sources to gain deeper insights into sales performance. The company has data on sales transactions, customer information, and product details stored in separate datasets. Your task is to use lookup functions to consolidate this data and perform a comprehensive sales analysis.

Your Responsibilities:

1. Use lookup functions to match and combine data from the Sales Transactions dataset with the Customer and Product datasets.

2. Create a comprehensive sales report that includes the customer name, product name, category, region, and loyalty program membership for each transaction.

3. Use lookup functions to categorize sales data by product category.

4. Leverage lookup functions to segment sales data by customer region and loyalty program membership.

5. Analyze the sales contribution from different regions and compare the purchasing behavior of loyalty program members versus non-members.

6. Use lookup functions to link sales data with product suppliers.

7. Calculate important sales metrics such as average order value, total units sold, and revenue per customer using the consolidated data.

8. Suggest strategies for targeting high-value customers, optimizing inventory, and enhancing supplier relationships.

What you’ll learn 

• Design effective and professional dashboard layouts using Excel

• Use PivotTables, PivotCharts, and slicers to create interactive reports

• Incorporate key metrics and KPIs for business insights

• Visualize trends, patterns, and comparisons using bar charts, line graphs, and pie charts

• Build dynamic dashboards that help decision-makers understand data at a glance

• Improve presentation skills by organizing and communicating insights visually

• .

PRESENTATION

• What You’ll Do:

  1. Collaborate in teams to prepare and deliver data-driven presentations

  2. Practice presenting your dashboards and explaining insights clearly

  3. Receive feedback to improve your communication and storytelling skills

  4. Learn how to present your Excel visualizations with confidence in interviews or stakeholder meetings

•  

Background:
A top bicycle retailer wants to strengthen its business strategy by analyzing recent sales data. The goal is to uncover key trends, identify important customer segments, and enhance inventory planning. As the data analyst, your role is to examine the sales data, build an interactive Excel dashboard, and present meaningful insights and strategic recommendations to the management team.

Your Responsibilities:

1. Analyze overall sales trends over time to identify peak seasons and slow periods

2. Evaluate the performance of different bike categories based on sales volume and revenue

3. Segment customers by demographics such as age, gender, and location to understand buying behavior

4. Design an interactive Excel dashboard that clearly visualizes your findings

5. Include key metrics like total revenue, average order value, top-selling products, and regional sales performance

6. Add slicers and filters to allow users to drill down into specific time frames, locations, or customer groups

7. Use visual tools like bar charts, line graphs, pie charts, and heat maps for better data storytelling

8. Recommend which bike types to prioritize for marketing and inventory based on sales performance and customer demand

ACTIVITIES

• ACTIVITIES

  Identifying various data sources.
  Importing data into Power BI from various sources (Excel, CSV, databases, etc.).

  Data Cleaning and Transformation

  ACTIVITIES

  Using Power Query to clean data
  Applying Data types and

  Data Modeling

  ACTIVITIES

  Creating relationships between tables.
  Defining primary keys and foreign keys.

Background:

FreshMart Superstores, a large retail chain with branches across the country, has gathered extensive sales data from the past year. However, the data is disorganized, with missing entries, inconsistencies, and duplicate records. The management team wants to make use of this data to evaluate sales performance, uncover buying trends, and guide future business decisions. Your role is to clean, restructure, and analyze this data using Power BI to generate meaningful insights.

Your Responsibilities:

1. Identify and handle missing values using appropriate methods (e.g., mean, median, or row/column removal)

2. Eliminate duplicate entries to ensure that each sales transaction is unique

3. Verify that each Total Sales figure is accurate by recalculating it as Quantity × Unit Price

4. Categorize products into broader groups and summarize sales by each category

5. Analyze sales trends over time, including peak periods, seasonal shifts, and top-performing store branches

6. Study customer behavior to spot high-value shoppers and evaluate how discounts affect their purchasing patterns

7. Recommend which products or categories should be prioritized based on consistent sales volume and demand.

Activities – Data Visualization:

• Build basic visual reports using bar charts, line graphs, pie charts, and other visuals in Excel and Power BI

• Use these visualizations to explore patterns, spot trends, and interpret data more effectively

Activities – DAX Formulas in Power BI:

• Create summary insights using DAX measures (e.g., totals, averages, and counts)

• Write advanced DAX expressions for calculated fields and custom metrics

• Apply DAX formulas to build logic-driven insights and calculated KPIs

Activities – Dashboard Development:

• Design interactive dashboards using Excel or Power BI

• Add charts, key metrics, and visuals to effectively tell a data story

• Enhance dashboards with filters, slicers, and other interactive elements for deeper analysis

Background:
A retail analytics company has collected a rich dataset from a major e-commerce platform to better understand consumer shopping behavior. The objective is to uncover actionable insights, discover emerging trends, and deliver data-driven recommendations that will help the retailer improve marketing strategies, product selection, and customer experience. Your role is to explore the dataset using Excel and Power BI to analyze patterns and present strategic findings..

Your Responsibilities:

1. • Segment customers based on demographic attributes like age, gender, region, and income level, and identify high-value groups based on how often and how much they purchase

   • Discover which product categories are most favored by each customer segment and detect shifts in buying preferences

   • Analyze purchase timing to find peak shopping days, hours, and seasonal trends

   • Compare shopping behavior across devices (mobile, desktop, tablet) to see how customer choices vary

   • Examine preferred payment methods for each demographic and time period to spot usage patterns

   • Identify items frequently bought together to reveal opportunities for cross-selling or product bundling

   • Find products commonly purchased by the same types of customers, indicating strong product affinity

   • Develop targeted marketing suggestions based on behavioral insights such as customer segments, device preferences, and shopping times

   • Create visual dashboards using Power BI and Excel to clearly communicate these trends and help guide business decisions in real-time

Excel and Power BI are transforming how businesses handle data and make decisions. In this unit, you’ll learn how to integrate Excel and Power BI into real-world data analytics workflows — helping organizations improve data quality, uncover actionable insights, and support smarter, evidence-based decisions across departments.

Complete 3 capstone projects and 15+ mini projects focused on Sales performance, Marketing optimization, Financial analysis and Customer segmentation.

In this segment of the training, We will help you prepare for technical and HR interviews for Data Analysis Roles. Also, we will provide you with training in CV writing skills and LinkedIn Optimization. Everything you need to grab any job opportunity.

Attend a Q&A session with a working professional using Excel & Power BI in a real job role.

Learn a step-by-step system to earn money working remotely as an Excel & Power BI data analyst.

Get a proven guide on how to apply for Skilled Worker & Tech Nation visas from Nigeria.