Description
Objectives
1. Data Collection, Cleaning, and Transformation
✅ Identify data sources and import data into Excel from multiple formats (CSV, databases, web, etc.).
✅ Apply data cleaning techniques using Power Query, removing duplicates, handling missing values, and correcting errors.
✅ Utilize text and logical functions to manipulate and standardize data.
2. Advanced Data Analysis Techniques
✅ Perform descriptive statistics (mean, median, standard deviation) and interpret data trends.
✅ Use IF statements, conditional formulas, and lookup functions (VLOOKUP, XLOOKUP, INDEX-MATCH) to analyze large datasets.
✅ Conduct trend analysis, forecasting, and scenario planning for business decision-making.
3. Data Summarization Using Pivot Tables
✅ Build dynamic Pivot Tables and Pivot Charts to summarize and analyze data.
✅ Implement calculated fields and slicers for deeper insights.
✅ Extract and manipulate Pivot Table data using GETPIVOTDATA.
4. Data Visualization and Dashboard Development
✅ Design interactive dashboards using charts, slicers, and conditional formatting.
✅ Apply best practices in data visualization to enhance readability and insights.
✅ Integrate Power BI with Excel for advanced reporting and visual storytelling.
5. Advanced Excel Functions for Data Analytics
✅ Use SUMIF, COUNTIF, AVERAGEIF for conditional calculations.
✅ Apply TEXT, CONCATENATE, SUBSTITUTE for text-based data manipulation.
✅ Master dynamic array formulas (SORT, UNIQUE, FILTER) for advanced automation.
6. Automation Using Macros and VBA
✅ Record and run macros to automate repetitive data analysis tasks.
✅ Write basic VBA scripts for customized automation and report generation.
✅ Develop user-defined functions (UDFs) to enhance Excel’s built-in capabilities.
Description
Module 1: Introduction to Data Analytics and Advanced Excel
📌 Key Topics:
- Overview of Data Analytics and its applications in business
- Understanding Excel as a Data Analytics Tool
- Data types, formatting, and basic functions
- Introduction to Excel Shortcuts & Productivity Tools
- Setting up Data Models and Data Structures
🔹 Excel Functions & Tools Covered:
- Keyboard Shortcuts (CTRL + ALT + V, CTRL + SHIFT + L)
- TEXT, CONCATENATE & TRIM (for text manipulation)
- Data Validation & Named Ranges
Module 2: Data Collection, Cleaning, and Preparation
📌 Key Topics:
- Importing and Managing Large Datasets
- Cleaning Data for Accuracy and Consistency
- Handling duplicates, missing values, and errors
- Data transformation using Power Query
- Data Consolidation from multiple sources
🔹 Excel Functions & Tools Covered:
- Power Query (to clean and transform data)
- TEXT, LEFT, RIGHT, MID (for text manipulation)
- Find & Replace, Remove Duplicates (data cleaning)
- IFERROR, ISERROR, ERROR.TYPE (handling errors)
Module 3: Advanced Data Analysis Techniques
📌 Key Topics:
- Descriptive Statistics (Mean, Median, Mode, Standard Deviation)
- Trend and Forecasting Analysis
- Identifying Patterns and Anomalies in Data
- What-If Analysis & Scenario Planning
- Regression Analysis and Correlation
🔹 Excel Functions & Tools Covered:
- SUMIF, COUNTIF, AVERAGEIF (conditional analysis)
- TREND, FORECAST, LINEST (trend analysis)
- Data Tables & Goal Seek (What-If Analysis)
- Analysis ToolPak Add-in (for statistical analysis)
Module 4: Pivot Tables and Data Summarization
📌 Key Topics:
- Creating and Customizing Pivot Tables
- Using Pivot Charts for better visualization
- Group, Sort, and Filter Data
- Calculated Fields and Calculated Items in Pivot Tables
- Using Slicers and Timelines for Interactive Reports
🔹 Excel Functions & Tools Covered:
- Pivot Tables & Pivot Charts
- Slicers & Timelines (for interactive filtering)
- GETPIVOTDATA (extracting data from Pivot Tables)
Module 5: Data Visualization & Dashboard Development
📌 Key Topics:
- Best Practices for Data Visualization
- Creating Dynamic and Interactive Charts
- Building Custom Dashboards in Excel
- Using Conditional Formatting for Insights
- Introduction to Power BI for Advanced Reporting
🔹 Excel Functions & Tools Covered:
- CHARTS (Bar, Line, Pie, Combo)
- SPARKLINES (miniature trend graphs)
- Conditional Formatting (heat maps, alerts)
- Power BI Integration with Excel
Module 6: Advanced Excel Functions for Data Analysis
📌 Key Topics:
- Using Advanced Lookup Functions
- Creating Automated Reports with Formulas
- Array Formulas and Dynamic Arrays
- Using Logical & Text Functions for Analysis
🔹 Excel Functions & Tools Covered:
- VLOOKUP, XLOOKUP, INDEX-MATCH (data lookup)
- IF, AND, OR, IFS (logical functions)
- TEXTJOIN, CONCATENATE, SUBSTITUTE (text functions)
- Dynamic Arrays (FILTER, SORT, UNIQUE)
Module 7: Automation with Macros and VBA
📌 Key Topics:
- Introduction to Macros and VBA in Excel
- Recording and Running Macros
- Writing Basic VBA Scripts for Automation
- Automating Repetitive Data Analysis Tasks
- Creating User-Defined Functions (UDFs)
🔹 Excel Functions & Tools Covered:
- Macro Recording & Editing
- VBA for Data Processing
- Automating Reports with VBA
General Notes
GENERAL NOTES
i. The clients have many options on where they would wish to have the training
ii. The client should communicate their preferred training venue at least seven days before the training date failure to which the training shall take place in Nairobi.
iii. This course is delivered by our seasoned trainers who have vast experience as expert professionals in the respective fields of practice. The course is taught through a mix of practical activities, theory, group works and case studies.
iii. Training manuals and additional reference materials are provided to the participants.
iv. Upon successful completion of this course, participants will be issued with a Chania finance consultancy certificate.
v. The onsite training fee covers tuition fees, training materials, tea at 10am, lunch, tea at 4pm, training venue and a certificate on completion. Accommodation and travel may be arranged for our participants upon request.
vi. The online training fee covers tuition fees, training materials, and a certificate on completion.
Available at the following Venues
- Nairobi
- Kisumu
- Nakuru
- Mombasa
- Zoom
- Google Meet
- Google Teams
- Webex
Choose the date you would like to take the course
Related Courses
10,000+ unique online course list designs