FA-0200Data & AnalyticsAutomation, RPA & Power Platform

Excel Data Domination

Unlock advanced Excel tools for smarter decisions in 2 days

Introduction

Why this course

Welcome to this comprehensive Microsoft Excel Intermediate course! Excel is an incredibly powerful spreadsheet tool used across industries to organize, analyze, and visualize data. In today's data-driven world, strong Excel skills give you a significant advantage in making informed decisions, streamlining processes, and communicating insights effectively. This course will equip you with the tools to go beyond the basics of Excel and master intermediate techniques.

Learning outcomes

Upon completing this training, you will be able to:

Create and implement more complex formulas for sophisticated calculations.
Employ techniques for efficient data management and organization.
Construct various chart types to vividly communicate data trends and patterns.
Utilize PivotTables and PivotCharts to summarize, analyze, and explore large datasets with ease.
Leverage time-saving automation techniques, including basic macros.
Discover how Excel's AI features enhance data analysis and prediction.

Prerequisites

A solid understanding of basic Excel concepts (cell navigation, entering data, simple formulas like SUM, AVERAGE).
Familiarity with basic Excel formatting and worksheet management.

Training outline

5 modules

01Mastering Formulas4 topics

Understanding Cell References
1. Relative references
2. Absolute references
3. Mixed references
Common Functions
1. Mathematical (SUM, AVERAGE, MIN, MAX, COUNT, etc.)
2. Logical (IF, AND, OR, NOT)
3. Text (LEFT, RIGHT, MID, CONCATENATE, etc.)
4. Date & Time (TODAY, NOW, DATE, YEAR, etc.)
Nested Formulas (Combining functions for complex calculations)
Array Formulas (Performing calculations on multiple cells at once)

02Data Management and Organization3 topics

Sorting and Filtering
1. Sorting by single or multiple columns
2. Custom sorting
3. Filtering by criteria
4. Advanced Filter
Data Validation
1. Setting validation rules (number ranges, dates, text length, etc.)
2. Creating input messages and error alerts
Named Ranges
1. Defining named ranges
2. Using named ranges in formulas for clarity

03Data Visualization with Charts3 topics

Chart Selection
1. Column and Bar charts
2. Line charts
3. Pie charts
4. Scatter plots
5. Other specialized chart types
Chart Creation
1. Selecting appropriate data for charting
2. Creating basic charts using the Chart Wizard
Chart Customization
1. Adding and formatting chart elements (titles, axes, legends, data labels)
2. Changing colors, styles, and chart layouts

04Power of PivotTables and PivotCharts3 topics

Understanding PivotTables
1. The concept of summarizing and aggregating data
2. Creating a basic PivotTable
Manipulating PivotTable Data
1. Grouping data by categories
2. Filtering and sorting within PivotTables
3. Calculating values within PivotTables (sum, count, average, etc.)
PivotCharts
1. Creating PivotCharts for powerful data visualization
2. Customizing PivotCharts for clarity and impact

05Automation and AI-Enhanced Analysis2 topics

Introduction to Macros
1. Understanding the concept of macros
2. Recording simple macros to automate repetitive tasks
Excel’s AI Features
1. Ideas for data analysis and visualizations
2. Flash Fill for pattern recognition and automatic data entry
3. Basic forecasting capabilities

Important Note: The pace and depth of the course can be adjusted based on the participants' existing Excel knowledge and the specific focus needed for their roles.

A programme built around your team.

Share your training goals and requirements.