Description
Mastering Data Analysis & Visualization with Excel & Power BI
Day 1: Introduction to Data Analysis and Excel Fundamentals
- Morning Session:
- Course overview and learning objectives
- Introduction to data analysis concepts
- Overview of Microsoft Excel's data analysis capabilities
- Understanding data types, structures, and basic operations in Excel
- Afternoon Session:
- Data cleaning and preparation in Excel
- Sorting, filtering, and conditional formatting
- Using tables and structured references
- Practical exercises
Day 2: Advanced Excel Functions for Data Analysis
- Morning Session:
- Advanced mathematical, statistical, and logical functions in Excel (e.g., SUMIFS, COUNTIFS, AVERAGEIFS, etc.)
- Text functions for data cleaning and formatting (e.g., LEFT, RIGHT, FIND, MID, etc.)
- Afternoon Session:
- Lookup and reference functions (e.g., VLOOKUP, HLOOKUP, INDEX, MATCH, and XLOOKUP)
- Error handling and troubleshooting formulas
- Case study: Applying advanced functions to solve business problems
Day 3: Pivot Tables and Pivot Charts
- Morning Session:
- Introduction to PivotTables and their importance in data analysis
- Creating, formatting, and managing PivotTables
- Analyzing large data sets with PivotTables
- Afternoon Session:
- PivotCharts: Visualizing data with PivotTables
- Creating interactive reports with slicers and timelines
- Hands-on practice session with data sets
Day 4: Advanced Data Analysis Tools in Excel
- Morning Session:
- Using Excel’s data analysis tools: Data Analysis ToolPak
- Descriptive statistics, regression analysis, and hypothesis testing
- Afternoon Session:
- What-if analysis (Goal Seek, Scenario Manager, Data Tables)
- Solver for optimization problems
- Hands-on exercises for problem-solving using analysis tools
Day 5: Introduction to Power BI and Data Integration
- Morning Session:
- Overview of Power BI: Components and capabilities
- Installing and setting up Power BI Desktop
- Importing data from multiple sources (Excel, databases, web, etc.)
- Afternoon Session:
- Data transformation and cleaning with Power Query Editor
- Introduction to the Power BI data model and relationships
- Lab: Data loading and preparation in Power BI
Day 6: Power BI Visualizations and Dashboards
- Morning Session:
- Creating basic visualizations: Bar charts, line charts, scatter plots, pie charts
- Advanced visualizations: Tree maps, waterfall charts, and maps
- Formatting and customizing visualizations
- Afternoon Session:
- Creating interactive dashboards
- Using slicers, filters, and drill-through for report interactivity
- Hands-on session: Building a Power BI dashboard from scratch
Day 7: Power BI DAX and Advanced Analytics
- Morning Session:
- Introduction to DAX (Data Analysis Expressions)
- Using DAX for calculated columns, measures, and advanced calculations
- Commonly used DAX functions (SUMX, CALCULATE, FILTER, etc.)
- Afternoon Session:
- Time intelligence functions in DAX
- Practical applications of DAX for complex data analysis
- Case study: Using DAX for business scenario analysis
Day 8: Power BI Data Modeling and Report Publishing
- Morning Session:
- Understanding relationships, hierarchies, and data modeling best practices
- Creating and managing relationships in Power BI
- Afternoon Session:
- Publishing reports to Power BI Service
- Sharing and collaborating on Power BI reports
- Hands-on exercise: Building a full report and publishing it to Power BI
Day 9: Excel and Power BI Integration
- Morning Session:
- Connecting Excel to Power BI for live data analysis
- Exporting Power BI visuals and data back to Excel
- Creating reports in Power BI and summarizing data in Excel
- Afternoon Session:
- Using Power Pivot and Power Query in Excel for data analysis
- Lab: Integrating Excel and Power BI for comprehensive data analysis and reporting
Day 10: Final Project and Advanced Visualization Techniques
- Morning Session:
- Advanced visualization techniques in Power BI (KPI visuals, custom visuals, themes)
- Tips for designing effective dashboards and reports
- Afternoon Session:
- Final group project: Participants will create a complete data analysis and visualization report using Excel and Power BI
- Presentation of projects and feedback session
- Course conclusion and Q&A
Description
This course is designed to provide professionals with an in-depth understanding of data analysis, manipulation, and visualization using Advanced Excel and Power BI. It will equip participants with practical tools to analyze data efficiently and present it in visually engaging and insightful formats to aid decision-making.
Course Duration: 10 Days
Delivery Mode: In-person/Online
Target Audience: Data analysts, financial analysts, business intelligence professionals, managers, and anyone who uses data for decision-making.
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
- Google Meet
Choose the date you would like to take the course
Related Courses
10,000+ unique online course list designs
