Live Online Excel Course for Data Analytics

Week 1: Introduction to Excel and Data Fundamentals (6 Hours)

1. Overview of Excel and its role in data analytics

2. Navigating Excel interface – ribbons, tabs, and shortcuts

3. Understanding data types, cell references, and formatting

4. Working with formulas and basic functions (SUM, AVERAGE, COUNT, etc.)

5. Sorting, filtering, and conditional formatting for data organization

6. Introduction to data entry, data validation, and basic charts

Week 2: Data Cleaning and Preparation (6 Hours)

1. Techniques for cleaning and structuring raw data

2. Handling duplicates, blanks, and inconsistent entries

3. Text functions (LEFT, RIGHT, MID, TRIM, CONCATENATE, TEXTJOIN)

4. Date and time functions for data processing

5. Using logical functions (IF, AND, OR, IFERROR)

6. Practical exercises on preparing real-world datasets

Week 3: Data Analysis with Formulas and Functions (6 Hours)

1. Lookup and reference functions (VLOOKUP, HLOOKUP, XLOOKUP, INDEX-MATCH)

2. Statistical functions (AVERAGEIF, COUNTIF, SUMIFS)

3. Mathematical and financial functions (ROUND, RANK, PMT, NPV, IRR)

4. Data summarization with dynamic formulas

5. Using named ranges and structured references

6. Automating analysis with nested formulas

Week 4: Data Visualization and Reporting (6 Hours)

1. Creating effective charts (Column, Line, Pie, Bar, Scatter, Combo)

2. Advanced chart customization (axes, labels, dynamic ranges)

3. Conditional visualizations with data bars and color scales

4. Building interactive dashboards using form controls

5. Using Sparklines and Conditional Formatting for insights

6. Best practices for designing clean analytical dashboards

Week 5: Advanced Analytics and Pivot Tools (6 Hours)

1. Introduction to PivotTables and PivotCharts

2. Grouping, filtering, and summarizing data in PivotTables

3. Using Slicers and Timelines for interactivity

4. Advanced calculations with DAX and Power Pivot

5. Introduction to Power Query for ETL (Extract, Transform, Load)

6. Automating repetitive tasks using Macros and basic VBA

Week 6: Business Intelligence and Capstone Project (6 Hours)

1. Using Power Query to merge and clean multiple datasets

2. Integrating Excel with Power BI and external data sources

3. Performing scenario and sensitivity analysis (Goal Seek, Solver, Data Tables)

4. Real-world case study: End-to-end data analytics project in Excel

5. Building an interactive dashboard with live insights

6. Final review, project presentation, and assessment