Data Analytics Using Excel 2019 teaches learners how to analyze, organize, and visualize data using Microsoft Excel 2019 through formulas, PivotTables, charts, dashboards, and reporting tools.
Duration 3 Days – 21 hrs.
Overview
The Data Analytics using Excel 2019 Training Course equips participants with the essential skills to analyze, interpret, and visualize data using Microsoft Excel 2019. This course focuses on practical, real-world applications of data analytics, enabling participants to transform raw data into meaningful insights that support business decision-making.
Participants will learn how to clean and prepare data, apply formulas and functions, create dynamic reports using PivotTables, and build interactive dashboards. The course also introduces fundamental analytical thinking, data storytelling, and best practices in data management.
Through hands-on exercises and case studies, participants will gain confidence in using Excel as a powerful analytics tool for reporting, forecasting, and business intelligence.
Objectives
- Understand the fundamentals of data analytics and its business applications
- Clean, organize, and prepare datasets for analysis
- Apply Excel formulas and functions for data analysis
- Use PivotTables and PivotCharts to summarize large datasets
- Create meaningful visualizations and dashboards
- Analyze trends, patterns, and key performance indicators (KPIs)
- Perform basic forecasting and what-if analysis
- Present data insights effectively using reports and dashboards
Target Audience
- Business Analysts
- Data Analysts (Beginner to Intermediate)
- Finance and Accounting Professionals
- Operations and Reporting Staff
- Marketing and Sales Professionals
- Project Managers
- Anyone working with data in Excel
Prerequisites
- Basic knowledge of Microsoft Excel (navigation, basic formulas)
- Familiarity with spreadsheets and data entry
- No prior analytics experience required
Course Outline
Module 1: Introduction to Data Analytics & Excel 2019
- Overview of Data Analytics concepts
- Types of data (structured vs unstructured)
- Role of Excel in data analysis
- Excel interface refresher (2019 features)
- Understanding datasets and data structures
Module 2: Data Preparation and Cleaning
- Importing data (CSV, text, external sources)
- Data formatting and standardization
- Removing duplicates and handling errors
- Text functions (LEFT, RIGHT, MID, TRIM)
- Data validation and cleaning techniques
- Introduction to Flash Fill
Module 3: Working with Formulas and Functions
- Logical functions (IF, AND, OR)
- Lookup functions (VLOOKUP, HLOOKUP, INDEX-MATCH)
- Date and time functions
- Statistical functions (AVERAGE, COUNT, SUMIF, COUNTIF)
- Conditional calculations
- Named ranges and formula auditing
Module 4: Data Analysis Techniques
- Sorting and filtering data
- Advanced filtering techniques
- Conditional formatting for insights
- Subtotals and grouping
- Introduction to What-If Analysis
- Goal Seek
- Scenario Manager
Module 5: PivotTables and PivotCharts
- Creating and customizing PivotTables
- Grouping data (dates, categories)
- Calculated fields and items
- PivotCharts for visualization
- Slicers and timelines for interactivity
- Best practices in summarizing data
Module 6: Data Visualization and Dashboarding
- Principles of effective data visualization
- Creating charts (bar, line, pie, combo charts)
- Designing interactive dashboards
- Using slicers and form controls
- KPI tracking and reporting dashboards
- Formatting for clarity and storytelling
Module 7: Introduction to Power Tools (Excel 2019)
- Overview of Power Query (Get & Transform)
- Importing and transforming data
- Basic data modeling concepts
- Introduction to Power Pivot (if enabled)
- Relationships between tables
Module 8: Basic Forecasting and Trend Analysis
- Trend analysis techniques
- Forecasting using Excel tools
- Moving averages
- Identifying patterns and anomalies
- Data-driven decision making
Module 9: Reporting and Data Storytelling
- Structuring reports for stakeholders
- Communicating insights effectively
- Data storytelling techniques
- Best practices in presentation
- Common mistakes in data reporting
Module 10: Capstone Exercise / Case Study
- End-to-end data analytics project
- Data cleaning → analysis → dashboard creation
- Presentation of insights
- Trainer feedback and improvement recommendations
