Microsoft Excel for Data Analysis: Crash Course
Day 1: Excel Basics & Interface
Objective: Understand the Excel interface and basic operations.
Topics:
- Ribbon, Worksheets, Cells, Rows, Columns
- Basic formulas: SUM(), AVERAGE(), COUNT(), MIN(), MAX()
- Formatting cells (number, text, date)
- Shortcuts for navigation and selection
Exercise:
1. Create a simple budget sheet with columns for "Item", "Cost", "Quantity", and "Total".
2. Use SUM() to total the "Total" column.
3. Format currency and apply basic cell borders.
Day 2: Data Cleaning & Preparation
Objective: Learn to clean and structure raw data.
Topics:
- Remove duplicates
- Find & Replace
- Text functions: TRIM(), LEFT(), RIGHT(), MID(), LEN(), TEXT()
- Date functions: TODAY(), NOW(), DATEDIF()
- Data Validation
Exercise:
1. Import a raw contact list with inconsistent formatting.
2. Use TRIM() to remove extra spaces.
3. Extract domain names from email addresses using RIGHT() and FIND().
4. Validate a "Phone Number" column using Data Validation.
Day 3: Sorting, Filtering, and Conditional Formatting
Objective: Learn to explore and visually segment data.
Topics:
- Basic & Custom Sorting
- AutoFilter and Advanced Filter
- Conditional Formatting rules
Exercise:
1. Sort a sales dataset by "Region" and then by "Sales Amount".
2. Filter out records where sales are below a target.
3. Highlight top 10% performers with Conditional Formatting.
Day 4: Working with Functions
Objective: Master commonly used analytical formulas.
Topics:
- Logical: IF(), AND(), OR(), IFERROR()
- Lookup: VLOOKUP(), HLOOKUP(), XLOOKUP(), INDEX(), MATCH()
- Math: ROUND(), RANK(), COUNTIF(), SUMIF(), AVERAGEIF()
Exercise:
1. Use IF() to label sales as "Pass" or "Fail" based on target.
2. Use VLOOKUP() to fetch product names by ID.
3. Use RANK() to rank employees by sales performance.
Day 5: Pivot Tables and Charts
Objective: Summarize and visualize large datasets.
Topics:
- Creating and modifying Pivot Tables
- Grouping data in Pivot Tables
- Creating Pivot Charts
- Best practices for chart selection
Exercise:
1. Create a Pivot Table from a transaction log.
2. Group data by month and category.
3. Visualize using a bar chart and pie chart.
Day 6: Dashboarding and Automation
Objective: Build an interactive dashboard with basic automation.
Topics:
- Named ranges and dynamic formulas
- Form controls (dropdowns, checkboxes)
- Basic macros and the Developer tab
- Introduction to Power Query (Get & Transform)
Exercise:
1. Build a KPI dashboard with metrics like Total Sales, Avg Order Value.
2. Add dropdown filters for region and category.
3. Record a macro to automate formatting.
Day 7: Case Study & Capstone Project
Objective: Apply all skills in a real-world scenario.
Project Task:
Analyze a company's sales data:
- Clean the data
- Use formulas to calculate metrics
- Create a summary with Pivot Tables
- Visualize trends
- Build a dashboard for stakeholders
