Mellon Training

Introducing Pivot Tables

Customizing Pivot Table Reports

Creating Useful Pivot‐Driven Views

Adding Interactivity with Pivot Slicers

Building Pivot Charts

GETPIVOTDATA function

PROJECT: Social Media Dashboard (Introduction)

ANSWER of PROJECT: Social Media Dashboard

❓QUIZ 7: Pivot Tables & Charts Techniques

Introduction

Part 1: Prepare the data

Part 2: Set up the Relationships

Part 3: Creating Pivot Tables

Part 4: Creating Pivot Charts

Part 5: Adding the Slicers & Final steps

Part 6: Putting Everything Together

Introduction

Combo Box Drop-Down Lists (+ EXAMPLE with Dynamic Chart)

Option Buttons (+ EXAMPLE with Dynamic Chart)

Check Box (+ EXAMPLE with Dynamic Combo Chart)

List Box (+ EXAMPLE with Interactive Column Chart)

Scroll Bar (+ EXAMPLE with Dynamic Line Chart)

Progress Doughnut Chart

(NEW 2024) Pies and Doughnuts

Excel Infographics: People Graph

Waffle Charts for KPIs (Better than Pie Charts)

Funnel Chart

Sparklines and Win/Loss Charts

Bullet Graph

Lollopop, Dot Plot or Dumbbell Chart

Emphasizing & Mark Significant Events

Line Graph: Multiple Lines

Dynamic Heat Map using Scroll Bar & Formulas

Representing trends with Icon Sets, Data Bars and Symbols

Display Symbols in Charts (+IFERROR Function)

In-Cell Charts (with REPT function)

TOP and BOTTOM Ranking Chart

Comparisons and Data Bars (with Conditional Formatting)

Dynamic Line Chart with MAX and MIN Dots

Dynamic Chart with Drop-Down Lists and Filters

Side‐by‐Side Time Comparisons (Panel Chart)

(ADVANCED) Scroll and Sort Table

(ADVANCED) Rollover Method

❓QUIZ 8: Dashboard Components

Input (Raw) Data of the Dashboard

Presenting the Calculations and Dashboard Worksheets

Building the Calculations Part:1

Building the Calculations (SUMIFS & VLOOKUP) Part:2

Building the Calculations (SUMPRODUCT) Part:3

Creating a Trending Column Chart

Creating a Line chart with Markers

Creating a Bullet Chart with Performance Targets

Creating a Combo Chart & Setting Up the Option Buttons

Creating Stacked Bars with Positive & Negative Values

Creating a Four-Segment Line Chart

Finalizing our Dashboard

Introduction

Input (Raw) Data of the Dashboard

Building the Calculations - Part: 1

Building the Calculations - Part: 2

Building the Calculations - Part: 3

Building the Calculations – Final Part

Create the Sales Chart

Number of Customers Chart

Create the Donut Charts

Completing the tables on our Dashboard

Creating Mini Charts for the Balanced Scorecard

Finalizing the Dashboard with Option Buttons

INTRO and some Dashboard Design Rules

PART 1

PART 2

PART 3

PART 4

PART 5

Part 2: Setup of Calculation Worksheet

Part 3: Complex Lookup Calculations with SUMPRODUCT Function

Part 4: Preparing Data for Bullet and Clustered Stacked Chart

Part 5: Create Metrics with Sparklines & Conditional Formatting

Part 6: Starting to Build your Dashboard

Part 7: Final Touches

Part 8: Enhance your Dashboard with Beautiful Background & Design

Part 1: Preparation of the KPI Dashboard

Part 2: Set Up a Sorting Procedure for KPI’s

Part 3: Get The Data Sorted with the help of FORMULAS

Part 4: Add Visualizations & In-Cell Charts

Part 5: Final Touches

Part 6: Protecting & Sharing your Dashboard with the Outside World

Part 7: Protect your Excel Dashboard

Part 8: Linking Your Excel Dashboards to PowerPoint

Part 9: Saving your Dashboard to One Drive

Introduction

Part 1: Start designing the dashboard

Part 2: Adding Multiple Pivot Tables to a Single Workbook

Part 3: Calculated Fields

Part 4: Calculated Field Limitations

Part 5: Adding Sparklines, Chart & Timeline

Part 6: Finishing the Design of the Dashboard

Part 7: Refreshing Data & Protecting the Dashboard

Part 1: Start designing our Dashboard

Part 2: Using Nested IF & AVERAGE for Calculations

Part 3: Designing the traffic lights (Camera Tool)

Part 4: Final Touches

Introduction

Compatibility & Macros Security

What are macros (and some examples)

Ways of Recording a Macro

Ways of Running a Macro

Assign a Macro to Ribbon Icons and Create your Ribbon

Use Navigation Buttons to Change Chart Type

(ADVANCED) Animated Chart using VBA Code

Common Error Values in Microsoft Excel Formulas

QUIZ: Macros & VBA

BONUS LESSON: Introduction to Excel 2019

Common Error Values in Microsoft Excel