Description

Duration: 2 days

Course Overview:

This comprehensive course is designed to equip participants with advanced Excel skills necessary for efficient data analysis, validation, and visualization. Through a series of modules, participants will delve into complex functions, pivot tables, data linking, and dynamic charting techniques, enhancing their ability to manage and interpret data effectively for informed decision-making.

Learning Outcomes:

By the end of the course, participants will be able to:

• Utilize advanced logical functions for data manipulation.
• Implement lookup functions techniques to ensure data integrity.
• Create and manage dynamic pivot tables and pivot charts.
• Perform complex calculations and data analysis using built-in Excel features.
• Visualize data through a range of charting options and advanced graphical techniques.

Target Audience:

This course is designed for:

• Business Professionals who use Excel for data management and reporting.
• Data Analysts and Business Intelligence Professionals seeking to enhance their Excel skills for deeper data insights.
• Project Managers need advanced Excel tools for data tracking and analysis.
• HR Professionals aiming for improved efficiency in data handling.
• Financial Analysts are looking to refine their data analysis capabilities.
• Anyone looking to enhance their Excel skills for professional use.

 

Training Methodology:

2 Days Instructor-led Training: Delivered through a combination of lectures, explanation of the features, hand on and best practices demonstrations

 

Course Modules

Module 1: Analysing Data with Advance Functions

Sub-Module 1.1: Mastering Logical Functions and IF Statements

• Understanding Logical Functions
• IF Logical Tests
• Expanding Nested IF Statements

Sub-Module 1.2: Advanced Usage of IF and Related Functions

• AND, OR Functions with IF
• Using IFS for Multiple Conditions
• Breaking Up Complex Formulas

Sub-Module 1.3: Use Logical Power Functions

• Tabulating data using multiple criteria: SUMIFS, AVERAGEIFS, COUNTIFS
• Using MAXIFS and MINIFS

Module 2: Lookup Functions

Sub-Module 2.1: Lookup Functions Deep Dive

• Understanding LOOKUP Functions
• Finding Closest & Exact Matches with HLOOKUP Function
• Finding Closest & Exact Matches with VLOOKUP Function

Sub-Module 2.2: Powerful Dynamic Lookup Functions

• Locating Data with the MATCH Function
• Retrieving Information by Location with INDEX Function
• Two-way Lookup with INDEX & MATCH

Sub-Module 2.3: Practical Use of Lookup Functions

• Trap LOOKUP Function’s Errors with IFERROR Function
• Retrieve Multiple Values using Single VLOOKUP Function
• Nested and Mixed Lookup Functions

 

Module 3: Mastering Pivot Table

Sub-Module 3.1: Set Up a Dynamic Data Source

• What is a Table?
• Preparing Data for a Table
• Creating Tables
• Naming & Renaming a Table
• Table Name Rules
• Adding a Total Row

Sub-Module 3.2: Getting Started

• Creating a PivotTable
• Using the PivotTable Tools Tabs
• Specifying PivotTable Data
• Adding and Removing Data with the Field List
• Search the PivotTable Fields List

Sub-Module 3.3: Refresh Pivot Table

• Change Source Data
• Refresh Pivot Table data
• Automatically Refresh Data when Opening the Workbook

 

Module 4: Pivot Table Techniques

Sub-Module 4.1: Pivot Table Styles

• Explore Different Style Options
• Insert Blank Line after Each Item
• Change the Layout
• Repeat All Item Labels
• Turn Grand Totals On Or Off
• Turn Subtotals On Or Off

Sub-Module 4.2: Grouping Data in Pivot Table

• Grouping Options
• Understanding Auto Date Grouping
• Group Together Items in a Field
• Group Dates into years, quarters, months, weeks or others
• Group Numbers into Ranges

 Sub-Module 4.3: Advanced PivotTable Tasks

• Produce Multiple PivotTables
• Automatically Create a Pivot Table for Each Item in a Filter

Sub-Module 4.4: Adding Interactive Slicers and Timelines

• Using the Data Slicer with a PivotTable
• Creating a Slicer
• Using a Slicer
• Change the Number of Columns in a Slicer
• Add a Timeline
• Using a Timeline
• Connect Slicers or Timelines to Multiple Pivot Tables

 

Module 5: Enhancing Pivot Table with Calculations

Sub-Module 5.1: Build-In Calculation Features

• Add Multiple Subtotal Calculations
• Add a Second Field to the Values Area
• Change the Default SUM Function to Others

Sub-Module 5.2: Power of Show Values As

• Introduction to Show Value As
• Show Value as % of Grand, Column, Row, Parent Column, Parent Row Total
• Show Value as Difference & % of Difference
• Show Value as Running Total & % of Running Total
• Show Value as Rank

Sub-Module 5.3: Create Calculated Fields and Items

• Introduction to Calculated Fields
• Inserting, modifying or deleting a calculated field
• Introduction to Calculated Item
• Inserting, modifying or deleting a calculated Item

Sub-Module 5.4: Getting Hard Data from a Pivot Table

• Understanding the Get Pivot Data Function
• Turn On/Off GET PIVOT DATA
• Get Pivot Data Function Basics
• A Get Pivot Data Shortcut
• Referencing Pivot Table Cells by Address

 

Module 6: Making your Pivot Tables Look Amazing

Sub-Module 6.1: Format Numbers

• Format numbers to make them more appropriate
• Rename any Label
• Rename a Label with a Trailing Space

Sub-Module 6.2: Conditional Formatting

• The Right Way to Apply Conditional Formatting to a Pivot Table
• Editing Rule Descriptions
• Clearing Conditional Formatting

Sub-Module 6.3: Advance Filter

• Filter on Top N Items
• Add a Value Filter for Any Field
• Hide Selected Items
• Keep Selected Items
• Include New Items in Manual Filters

Sub-Module 6.4: Advance Sort

• Sort Items According to a Corresponding Value
• Create a Custom Sort Order

 

Module 7: Data Visualization

Sub-Module 7.1: Create Charts

• Selecting Data to Chart
• Recommended Charts
• Creating a Chart
• Styling Charts with the Design Tab
• Modifying Charts with the Format Tab
• Manipulating a Chart

Sub-Module 7.2: Modify and Format Charts

• Changing the Type of Chart
• Changing the Source Data
• Working with the Chart Axes and Data Series
• Saving a Chart as a Template

 Sub-Module 7.3: Pivot Charts

• Create a PivotChart
• Adding Data to your Chart
• Using the PivotChart Analysis Tabs
• Using & Hiding the PivotChart Filter Field Buttons

Sub-Module 7.4: Create Advanced Charts

• Creating Combo Chart
• Making Awesome Combo Charts
• Sparkline Chart
• Map Chart

 

Module 8: Different Ways To Present Excel Report

Sub-Module 8.1: Methods to Import Charts into Word / PowerPoint

• Direct copy-paste with formatting options
• Linking Excel Data for dynamic updates
• Using the 'Paste Special' feature for format control
• Inserting charts as images to prevent changes

Sub-Module 8.2: Optimizing Excel for Presentation Mode

• Setting up full-screen view
• Removing gridlines and distractions
• Utilizing Hyperlinks for seamless navigation