Data Analytics with Advanced Excel Course
We are committed to equipping individuals and organizations with the tools and expertise they need to thrive in a rapidly changing digital world.
Request A Quote !
Data Analytics with Advanced Excel Course Outline
Duration: 24 Hours
Level: Intermediate
Delivery Mode: Online/Offline
Tools Used: Microsoft Excel, Power Query, Power Pivot, Power BI
Module 1: Introduction to Data Analytics and Excel
- What is Data Analytics?
- The Role of Excel in Data Analytics
- Overview of Excel’s Features for Data Analysis
- Understanding Data Types and Data Structures
- Setting Up Your Workspace for Data Analytics
Module 2: Advanced Excel Formulas and Functions
- Essential Excel Functions for Data Analysis
- VLOOKUP, HLOOKUP, XLOOKUP, INDEX, MATCH
- IF, SUMIF, COUNTIF, SUMIFS, COUNTIFS
- Logical Functions: AND, OR, IFERROR
- Text Functions: TEXT, LEFT, RIGHT, MID, TRIM
- Date and Time Functions: TODAY, DATE, YEAR, MONTH, DAY, NETWORKDAYS
- Array Formulas for Complex Calculations
Module 3: Data Cleaning and Preparation in Excel
- Identifying and Handling Missing Data
- Removing Duplicates and Data Validation
- Using Excel’s Data Tools for Cleaning: Text-to-Columns, Flash Fill
- Working with Data Formats and Number Precision
- Using Power Query for Advanced Data Cleaning
- Importing Data from Various Sources
- Filtering, Transforming, and Merging Data
Module 4: Data Visualization Techniques in Excel
- Creating Dynamic and Interactive Charts
- Bar Charts, Line Charts, Pie Charts, Histograms
- Sparklines and Data Bars for Quick Insights
- Formatting and Customizing Charts for Presentation
- Using Conditional Formatting to Highlight Trends and Outliers
- Creating Dashboards with Excel
- Using PivotTables and PivotCharts
- Slicers and Timeline Filters
Module 5: Advanced Data Analysis with PivotTables
- Creating and Customizing PivotTables
- Grouping and Summarizing Data
- Calculating Percentages, Growth Rates, and Other KPIs
- Using Multiple Consolidation Ranges in PivotTables
- Creating PivotCharts for Interactive Data Visualization
- Using Slicers and Filters in PivotTables for Interactive Analysis
Module 6: Introduction to Power Query and Power Pivot
- Introduction to Power Query for Data Importing and Transformation
- Using Power Query Editor to Clean and Organize Data
- Working with Multiple Data Sources in Power Query
- Introduction to Power Pivot for Data Modeling
- Creating Relationships Between Tables in Power Pivot
- Using Data Models to Analyze Complex Data Sets
Module 7: Advanced Excel Analytics Techniques
- Goal Seek and Solver for Optimization Problems
- Scenario Analysis and What-If Analysis
- Using Excel’s Analysis Toolpak for Statistical Analysis
- Regression Analysis and Trendlines
- Descriptive Statistics and Distribution Analysis
- Advanced Filtering with Advanced Filters and Data Subtotaling
Module 8: Introduction to Power BI and Integrating with Excel
- Overview of Power BI and Its Capabilities
- Connecting Excel to Power BI
- Importing Excel Data Models into Power BI
- Creating Interactive Dashboards with Power BI
- Power BI Desktop vs Power BI Service
- Basic Data Visualization in Power BI
Module 9: Final Project and Assessment
- Apply Data Analytics Concepts to a Real-World Problem (e.g., Sales Analysis, Market Research)
- Data Cleaning, Analysis, and Visualization Using Excel
- Build a Final Interactive Dashboard or Report
- Review and Optimize Excel Models for Best Practices
- Final Exam and Viva
- Certification from BACT IT Institute of Bangladesh.