Summary of "Full Project in Excel with Interactive Dashboard | Excel Tutorial for Beginners"

Summary of "Full Project in Amazon.com/s?k=Excel&tag=dtdgstoreid-20">Excel with Interactive Dashboard | Amazon.com/s?k=Excel&tag=dtdgstoreid-20">Excel Tutorial for Beginners"

This video tutorial by Rishabh demonstrates how to complete an end-to-end data analytics project in Amazon.com/s?k=Excel&tag=dtdgstoreid-20">Excel, focusing on creating an Interactive Dashboard to solve a business problem for a retail store named Vrindavan Store. The tutorial covers data cleaning, processing, analysis, visualization, and sharing insights with clients.

Main Ideas, Concepts, and Lessons

Project Overview and Objective
- The goal is to analyze sales data from Vrindavan Store, which sells online through multiple channels.
- Create an annual report for 2022 to understand customer behavior and help grow the business in 2023.
- The final output is an Interactive Dashboard with slicers that filter data dynamically across charts.
Data Understanding and Cleaning
- Initial review of columns such as Order ID, Customer ID, Gender, Date, Status, Category, Size, Amount, City, State, and Country.
- Check for:
  - Duplicate or null values (none found in Order ID and Customer ID).
  - Consistency in categorical data (e.g., unify gender labels from “M”, “man”, “Woman”, “W” to standardized values).
  - Correct date formats and valid ranges.
  - Numeric data validation for size, amount, quantity.
- Replace inconsistent or incorrect data using Amazon.com/s?k=Excel&tag=dtdgstoreid-20">Excel’s Find & Replace.
- Remove unnecessary columns or data if irrelevant.
Data Processing
- Create new calculated columns for better analysis:
  - Age group classification using IF formula:
    - Senior: age ≥ 50
    - Adult: age between 31 and 49
    - Teenager: age ≤ 30 (used loosely)
  - Extract month names from date column using TEXT() formula for monthly analysis.
- Convert formulas to values to improve performance.
Data Analysis Using Pivot Tables and Charts
- Create pivot tables to answer business questions:
  - Compare sales amount and order count by month in a combined chart.
  - Identify the month with highest sales and orders (March).
  - Analyze sales by gender using pie charts.
  - Examine order status distribution (Delivered, Canceled, Refund, Return).
  - Determine top 5 states by sales.
  - Analyze sales by age group and gender.
  - Identify sales contribution by sales channel (Amazon, Flipkart, Myntra).
- Formatting tips for charts:
  - Use combo charts with secondary axis for sales vs orders.
  - Format axis numbers to show values in millions with decimals.
  - Remove gridlines and grand totals for cleaner visuals.
  - Customize pie chart data labels and legend for clarity.
  - Use horizontal bar charts for top states visualization.
Creating an Interactive Dashboard
- Insert slicers for Month, Channel, and Category to filter all pivot tables/charts simultaneously.
- Connect slicers to multiple pivot tables using "Report Connections" to sync filters.
- Demonstrate filtering dashboard dynamically by selecting different slicer values.
- Clear filters easily with the slicer’s clear button.
Insights and Sharing with Client
- Summarize key insights from the dashboard:
  - Maximum sales occur in March.
  - Women contribute the most to sales.
  - Majority of orders are delivered successfully.
  - Top states for sales: Maharashtra, Karnataka, Telangana, Tamil Nadu.
  - Adult women (ages 30-49) are the prime customers.
  - Amazon is the leading sales channel (~35% contribution).
- Prepare a written summary of insights alongside the dashboard for client presentation.
- Provide actionable recommendations:
  - Target adult women customers aged 30-49 in top states with ad coupons on Amazon and Flipkart to boost sales.
- Highlight that reports can be generated on different time scales (weekly, monthly, quarterly, annual).

Detailed Methodology / Instructions

Data Cleaning:
- Check each column for nulls, duplicates, and inconsistencies.
- Standardize categorical data using Find & Replace.
- Validate numeric and date columns.
- Remove or ignore irrelevant columns.
Data Processing:
- Add new columns for age groups using IF formula.
- Extract month names from dates using =TEXT(date_cell, "mmm").
- Convert formulas to values to optimize workbook performance.
Pivot Table Creation:
- Insert pivot tables for different analyses:
  - Sales and orders by month.
  - Sales by gender.
  - Order status counts.
  - Top states by sales.
  - Sales by age group and gender.
  - Sales by channel.
- Remove grand totals for cleaner presentation.
Chart Creation and Formatting:
- Use combo charts for sales and orders with secondary axis.
- Use pie charts for gender and order status distribution.
- Use horizontal bar charts for top states.
- Format axis numbers to display in millions with two decimals.