Index Match for Complex Lookups - Basics

Visually Effective Excel Dashboards

Module 1: Foundation in Dashboards

Module 2: Before You Start with Your Dashboard

First Steps for your Excel Dashboard Project (3:19)
Create a Mockup (Wireframe) in Excel
Useful Excel Keyboard Shortcuts (11:29)
4 Time-Saving Tips for Excel (8:47)
Proper Spreadsheet Architecture & Workflow (2:35)

Module 3: Report Design Principles

Layout - Best Practice for Excel Dashboard Design (3:48)
Color - Best Practice for Excel Dashboard Design (3:20)
A Color Scheme that fits with your Excel Report (4:31)
10 Concrete Design Tips for Better Excel Reports (6:04)
Bonus: Setting a Default Theme for New Workbooks in Excel (5:41)
Quiz: Excel Report Design - Test Your Knowledge

Module 4: Dashboard Source Data & Formats

Where is Your Source Data? (1:43)
Source: Excel Spreadsheet (6:23)
Source: Microsoft Access (4:41)
Source: Text / Data / CSV File (6:51)
Source: Excel Add-ins (2:16)

Module 5: Dashboard Formula Toolbox

Excel Formulas You'll Need for Impressive Dashboards (2:07)
Index Match for Complex Lookups - Basics (11:25)
Index Match for Complex Lookups - Advanced (10:13)
Index for Dynamic List Selection (8:36)
Realistic Case: Index for Dependent Drop-down Lists (7:02)
SUMIFS, COUNTIFS, AVERAGEIFS to Handle Multiple Criteria (11:01)
LARGE and SMALL for Sorting (2:45)
ROW(S), COLUMN(S) for Indexing (6:32)
ROW as Unique Identifier for Lookups (7:12)
CHOOSE for Flexibility (9:21)
Excel Text Function for Formatting (14:56)
Excel N Function for Tracking (3:44)
GetPivotData: Extract data Efficiently from Pivot Tables (11:29)
INDIRECT for Flexible References in Excel (11:41)
Choose & Name Manager for Flexible Ranges (Alternative to Indirect) (6:09)

Module 6: Form Controls for Interactive Dashboards

Introduction to Form Controls - Active X vs. Form Controls (2:08)
Combo Box - Select From a Drop Down List in Excel (5:11)
Check Box - Check / Uncheck Option in Excel (5:18)
Spin Button - Move Up & Down Lists in Excel (2:22)
Option Button - Choose One Option Only (5:01)
List Box - Select From a List of Options in Excel (3:38)
Scroll Bar - Scroll Through Excel Graphs & Tables (6:17)
Dependent Combo Box (2nd list automatically resets once 1st list is changed) (11:59)

Module 7: Advanced Excel Chart Techniques

Chart Overview & When to Use Which Excel Graph (2:26)
Excel Chart Basics (for those who need to get familiar with basic features)
Improve Charts 1: Add Total Values to Stacked Excel Column Charts (4:57)
Improve Charts 2: Add Series Labels Inside Excel Charts (8:33)
Improve Charts 3: Excel Chart Secret: Error Bars for Additional Control (12:27)
Pro Excel Chart Technique: Changing Chart Ranges (12:00)
Infographics in Excel using Icons (male/female) (10:52)
Pro Excel Chart Technique: Dynamic Map Chart with Drop-down (17:20)

Modules 8-13: KPI Excel Dashboard - START HERE

Module 8: Excel KPI Dashboard Setup

Excel KPI Dashboard Overview (6:13)
Dashboard Wireframe in Excel (2:44)
Spreadsheet Setup (9:37)
Setup of Raw Data Sheet (9:49)
Setup of Excel Calculation sheet (6:33)

Module 9: Excel KPI Dashboard - Scrolling Table & Bars

Setup a Combo Box to Ease Selection (12:30)
Complex Lookup with Index & Match (13:58)
Scrolling Data Table in Excel (13:26)
Conditionally-Formatted Data Bars (12:01)
Arrows for Deviations in Excel (9:21)
Formatting of Scrolling Data Table (6:27)

Module 10: Excel KPI Dashboard - Dynamic Headers

Module 11: KPI Dashboard - Top/Bottom KPI Based on Selection

Show Top 6 Matches with Unique Identifier (16:23)
List Box to Select KPI for Top 6 Matches (10:02)
Option Button to Toggle Between Top OR Bottom (7:14)
Actual to Budget Comparison - Sorted Bars with Error Bar Technique (11:00)
Finalize Top / Bottom Report on Dashboard (10:35)

Module 12: Excel KPI Dashboard - Scrolling Line Chart with Check Boxes

Complex Excel Lookup to Prepare data for Scrolling Chart (10:00)
Scroll-able Line Chart (5:25)
Trick to Avoid Crashing Lines in an Excel Line Chart (6:26)
Check Box to Control Series Visibility in Excel (Hide or Show) (4:08)
Excel Line Series Tweaks for Better Readability (10:19)
Finalize Profit Comparison Section on Dashboard (3:29)

Module 13: Excel KPI Dashboard - Final Touches

Modules 14-17: Regional Sales Excel Dashboard - Start Here

Module 14: Regional (Pivot / Slicer) Excel Dashboard - Using Slicer as Buttons

More Interactivity - Pivot Slicers as Buttons (also without Pivot Tables) (11:25)
Column Chart Controlled by Excel Slicer Buttons (7:22)
Pivot Slicer Custom Sorting & Design (5:03)
Check to Ensure Single Slicer Button is Selected (2:33)
Dynamic Excel Comments with Pivot Slicer Selection (13:17)

Module 15: Regional Dashboard - Advanced Excel Pivot Table Techniques

Variances in Excel Pivot Table (9:04)
Conditional Formatting in Excel Pivot Table (4:45)
Pivot Table as Part of Excel Dashboard Report (3:11)
Pivot Charts in Excel Dashboards (4:37)
Automatically Refresh Excel Pivot Table with Simple VBA Code (5:40)

Module 16: Regional Excel Dashboard - Impressive Charts Using Shapes

Module 17: Regional Excel Dashboard - Secrets for Impressive Reports

What's next?

Discussion