FA-0203Data & Analytics

Excel Power User

In 2-days, Go beyond spreadsheets. Master the tools of advanced Excel

Introduction

Why this course

This course delves into the advanced features of Microsoft Excel, empowering you to perform in-depth data analysis, create dynamic visualizations, automate complex tasks, and tap into the power of artificial intelligence. These skills will elevate your capabilities and streamline your data-driven workflows.

Learning outcomes

Upon completing this training, you will be able to:

Implement intricate formulas for sophisticated data analysis and modeling.
Design interactive and impactful charts for insightful data storytelling.
Write VBA code and macros to automate repetitive tasks and enhance Excel's functionality.
Integrate AI features to unlock predictive analytics and data insights.

Prerequisites

Strong proficiency in Excel's intermediate features (formulas, functions, pivot tables, charting).
Basic understanding of programming concepts (variables, loops, conditional logic) is beneficial but not strictly required.

Training outline

4 modules

011. Data Analysis Mastery4 topics

Array Formulas
- Performing calculations on arrays
- Complex criteria matching and lookups
Advanced Lookup and Reference Functions
- INDEX/MATCH combinations
- XLOOKUP for flexible lookups
- INDIRECT for dynamic cell referencing
Statistical Functions
- Descriptive statistics (MODE, MEDIAN, STDEV, etc.)
- Hypothesis testing (T-TEST, CHISQ, etc.)
Scenario Analysis and Modeling
- What-If Analysis tools (Goal Seek, Scenario Manager)
- Data Tables for simulating multiple outcomes

022. Advanced Charting Techniques4 topics

Combo Charts
- Combining multiple chart types on a single chart
- Formatting for clarity and impact
Custom Chart Elements
- Adding trendlines, error bars, and data labels
- Advanced formatting and styling control
Sparklines
- Creating mini-charts within cells for quick trend visualization
Dynamic Charts
- Charts that update automatically with data changes

033. Introduction to VBA and Macros4 topics

The Visual Basic Editor (VBE)
- Environment overview and navigation
Understanding VBA Syntax
- Variables, data types, operators
- Control flow (If/Then, loops)
Recording Macros
- Automating tasks by recording actions
Modifying and Editing Macros
- Customizing recorded macros
- Debugging code

044. Harnessing the Power of AI2 topics

Excel's Built-In AI Features
- Ideas for quick analysis and visualization
- Forecasting features
Integrating External AI Models (if feasible)
- Consuming web services or APIs
- Potential examples with Azure Cognitive Services or similar

Important Note: This is a comprehensive outline. You might choose to focus more heavily on certain aspects (data analysis vs. charting vs. VBA) depending on your audience's needs.

A programme built around your team.

Share your training goals and requirements.