Advanced Formulas, Data Analysis and Dashboard Reporting with Excel

Course Outline

This Master Class in Advanced Excel covers below 6 areas which is vital parts in Microsoft Advanced Excel and ramp up your Business Intelligence (BI) and Analytics Skills and absolutely revolutionize the way you manage, analyze, and visualize data in Excel. Throughout this course you will learn how to do Advanced Analysis in Excel, Working with Big Data and Advanced Data Cleaning techniques (Power Query) and How to Build a complete Highly Interactive Dashboard from scratch.

OBJECTIVE #1: ADVANCED EXCEL FORMULAS AND FUNCTIONS

DURATION: 6HOURS

• Nested IF Statements
• Nested IF Statements with AND/OR Operators
• Fixing Errors with IFERROR
• COUNTIFS/SUMIFS/AVERAGEIFS
• Building a Basic Dashboard with COUNTIFS & SUMIFS
• The SUMPRODUCT Function
• VLOOKUP/HLOOKUP
• Joining Data with VLOOKUP
• Advanced Range Names
• The INDEX Function
• The MATCH Function
• Using INDEX & MATCH together
• The OFFSET Function
• Combining OFFSET with COUNTA
• Using Array Formulas
• Formula Auditing – Trace precedent and dependents
• Evaluating Formulas and Error Checking
• XLOOKUP (Latest Excel Lookup Function)
  

  ---

OBJECTIVE #2: ADVANCED DATA VISUALIZATION WITH CHARTS & GRAPHS

DURATION: 3 HOURS

• Key Principles in Charting
• The Good, The Bad & The Ugly chart examples
• Chart Elements, Layouts & Styles
• Chart Formatting Options
• Changing Chart Types & Adding a Secondary Axis
• Creating, Modifying & Applying Custom Templates
• Geospatial Maps with Power Map
• Basic Combo Charts
• Sparklines
• Custom Image Overlay Charts
• Adding Interactive Elements with Form Controls
• Animating Changes Over Time
• Dynamic Value-Based Formatting
• Building a Custom Gauge Chart

OBJECTIVE #3: DATA ANALYSIS WITH PIVOT TABLES & PIVOT CHARTS

DURATION: 3 HOURS

• Structuring the Source Data
• Navigating the Field List
• Analyze & Design Options
• Selecting, Clearing, Moving & Copying Pivots
• Refreshing & Updating Pivots
• TIP: Dealing with Growing Source Data
• Removing & Reviving Data from Cache
• PivotTable Formatting & Customization
• Number Formatting
• TIP: Formatting Empty Cells
• Table Layouts & Styles
• TIP: Using Tabular Layouts to Create New Tables
• Customizing Headers & Labels
• Adding Conditional Formats
• TIP: Data Bars with Invisible Text
• Advanced Conditional Formatting
  

  ---

OBJECTIVE #4: EXCEL POWER QUERY, POWER PIVOT (EXCEL BIG DATA HANDLING)

DURATION: 5 HOURS

• Introduction
• Meet Power Query (aka Get & Transform)
• The Query Editor
• How to Connect Data from Any source
• Regional Date Formatting
• Basic Table Transformations
• Text-Specific Tools
• Number-Specific Tools
• Date-Specific Tools
• PRO TIP: Creating a Rolling Calendar
• Adding Index & Conditional Columns
• Grouping & Aggregating Data
• Pivoting & Unpivoting
• Modifying Workbook Queries
• Merging Queries & Appending Queries
• Connecting to a Folder of Files
• Power Query Best Practices
  

  ---

OBJECTIVE #5: EXCEL DATA MODELING FINAL INTERACTIVE DASHBOARD BUILDING

(EXCEL BUSINESS INTELLIGENCE)

DURATION: 5 HOURS

• Meet Excel’s Data Model
• Data vs. Diagram View
• Database Normalization
• Data Tables vs. Lookup Tables
• Relationships vs. Merged Tables
• Creating Table Relationships
• Modifying Table Relationships
• Active vs. Inactive Relationships
• Relationship Cardinality
• Connecting Multiple Data Tables
• Filter Direction
• Hiding Fields from Client Tools
• Defining Hierarchies
• Data Model Best Practices
• Using Power Pivot and Handling millions of data
• Final Dashboard DEMO session

OBJECTIVE #6: BONUS FEATURES

DURATION: 2 HOURS

• Adding Data Validation to your cells
• How to make dependent Data Validation List
• How to make Formula Based Conditional Formatting
• Highlight cells, rows using conditional formatting
• Dynamic Charts and Dashboards with form controls

WHAT’S INCLUDED

• Master Excel Class Manual
• Experienced Instructor

PREREQUISITES

Students need to bring a Laptop and should have intermediate knowledge in Excel.