Business Intelligence with Microsoft Excel

About Course

Master the art of data analysis and reporting with our Business Intelligence with Advanced Excel course. Designed for professionals aiming to elevate their skills, this course covers advanced Excel functionalities, dynamic Power Query transformations, and robust data modeling with Power Pivot and DAX.

Throughout the course, you’ll explore essential tools and techniques, including advanced LOOKUP functions, conditional formulas, dynamic ARRAY functions, and powerful What-If analysis. You’ll dive deep into the capabilities of Power Query to clean, transform, and consolidate data, and learn to build efficient data models in Power Pivot with advanced DAX functions for precise analytics.

Hands-on projects are a key highlight of this course. Create a modern Excel dashboard, apply best practices in data visualization, and develop an interactive reporting solution for real-world scenarios. Whether you’re preparing for business decisions, financial forecasting, or operational insights, this course equips you with the tools and confidence to succeed in data-driven environments.

Advanced Excel Proficiency: Master complex Excel functions, including LOOKUP, ARRAY, and conditional formulas, to solve analytical challenges efficiently.
Dynamic Data Manipulation: Utilize advanced ARRAY functions like FILTER, SORT, and UNIQUE to manage and analyze data dynamically.
What-If Analysis: Gain expertise in Scenario Manager, Goal Seek, and Data Tables to forecast outcomes and support data-driven decision-making.
Data Analysis ToolPak: Leverage built-in Excel tools for statistical analysis, regression modeling, and hypothesis testing.
Power Query Essentials: Clean, transform, and consolidate data from diverse sources with Power Query, automating repetitive tasks for improved productivity.
Advanced Data Transformations: Perform complex operations like merging, appending, and creating custom columns using Power Query.
Data Modeling with Power Pivot: Develop robust data models by building relationships between tables and designing a Star Schema for reporting.
DAX Mastery: Write and apply advanced DAX formulas for custom calculations, time intelligence, and multi-fact table analysis.
Interactive Dashboards: Design visually compelling dashboards in Excel, incorporating PivotTables, slicers, and advanced visualization techniques.
Real-World Projects: Apply learned concepts to hands-on projects, such as creating a modern Excel dashboard and a comprehensive reporting solution for business insights.

Course Content

Excel Fundamentals

Freeze Panes, Gridlines, Formula Bar, Headings, Split Window, Comments
Freeze Panes, Gridlines, Headings, Split Window, Comments
Sort and Filter
Filter, Add current selection to filter, * symbol
Remove Duplicates with Sort
Hide, Unhide with Filter
Insert Row, Delete Row with Sort
Grouping and Freeze Panes
Find, Replace, * symbol
Replace, Format Cells, Find in comments
Delete Comments, Delete Row, Go to Special, Filter
Horizontal Sort
Hyperlink
Introduction to Formulas: Mathematical Calculation
Introduction to Absolute and Relative References, $ signs
Copy + Paste special
Fill Handle and Flash Fill

Mathematical Functions

SUM Function
SUM, AVERAGE Functions
SUM, Reference Cells from Other Worksheets
SUM, Working on Multiple Worksheets at the Same Time
AVERAGE, Working on Multiple Worksheets at the Same Time
COUNT Function
COUNT, COUNTA, COUNTBLANK Functions
COUNT, SUM Functions
COUNT, Reference Cells from Other Worksheets
SUM, COUNT, COUNTA Functions
SUM, COUNT, COUNTA with Filter and Sort
MIN, MAX Functions
SUM, ABS Functions
ABS, Paste Special
PRODUCT Function
PRODUCT, $ Signs
LARGE, SMALL Functions
RANK.AVG, RANK.EQ Functions
SUBTOTAL, AGGREGATE Functions
SUBTOTAL, AGGREGATE with Filter and Group
ROUND, ROUNDUP, ROUNDDOWN Functions
SUMIFS Function
Name Manager
Name Manager with SUM, AVERAGE
Name Manager with SUMIFS, AVERAGEIFS and * Symbol
Name Manager, AVERAGEIFS, YEAR Functions
Name Manager, AVERAGEIFS with > & < Symbols
Name Manager, COUNTIFS with > & < Symbols
Name Manager, MINIFS, MAXIFS with > & < and * Symbols
Name Manager, SUMIFS, MAXIFS
SUMPRODUCT Function
Name Manager, SUMPRODUCT with * Symbol

Text Functions

CONCATENATE Function / & symbol
SUM, AVERAGE, CONCATENATE / & symbol
LEFT, RIGHT Functions
LEFT, CONCATENATE / & symbol
FIND / SEARCH Functions
MID Function
LEN Function
LEN, SUM Functions
LEFT, RIGHT, LEN, FIND Functions
LEFT, MID, FIND, CONCATENATE / & symbol
REPLACE Function
REPLACE, RIGHT, FIND Functions
SUBSTITUTE Function
LEN, SUBSTITUTE, LEFT, FIND, SUM Functions
TRIM Function
SUBSTITUTE, TRIM, CONCATENATE / & symbol
SUBSTITUTE, FIND, MID Functions
UPPER, LOWER, PROPER Functions
PROPER, TRIM Functions
LEFT, FIND, UPPER, MID, SUBSTITUTE Functions
REPT Function
N, T Functions
CONCAT Function
TEXTJOIN Function
Text to Columns
VALUE, MID, FIND, SUM, Filter, Select Visible Cells
FORMULATEXT Function
FORMULATEXT, VALUE, MID, FIND, SUM Functions

Date and Time Functions

DATE Function
DAY, MONTH, YEAR Functions
DATE, MONTH, DAY Functions
DAY, MONTH, YEAR, DATE, EDATE Functions
EOMONTH Function
TODAY Function
TODAY, Filter, Select Visible Cells
TODAY, EOMONTH, EDATE with $ Signs
TEXT Function
TEXT, CONCATENATE / & Symbol
WEEKDAY Function
WORKDAY.INTL Function
WORKDAY.INTL, CONCAT Functions
NETWORKDAYS.INTL Function
NETWORKDAYS.INTL, TODAY Functions
TIME Function
HOUR, MINUTE, SECOND Functions

Logical Functions

Comparison Operators
IF Function
Comparison Operators with SUM
Double IF Function
OR Function
IF, OR Functions
Double IF, OR Functions
IF, OR, YEAR Functions
Double IF, OR, YEAR Functions
AND Function
AND, OR Functions
IF, AND Functions
IF, AND, OR, YEAR, MONTH Functions
Double IF, AND, OR Functions
EXACT, PROPER Functions
EXACT, SUMPRODUCT Functions
IFERROR Function
IFS, IFERROR, OR, AND, MONTH Functions
SWITCH Function
SWITCH, OR, AND Functions
LAMBDA Function
LAMBDA, IFERROR, IFS Functions
SCAN, LAMBDA Functions
SCAN, LAMBDA, IF, SUM Functions
REDUCE, SCAN, LAMBDA Functions
REDUCE, LAMBDA, SEQUENCE, TRIM, SUBSTITUTE, CHAR Functions
LET, IF Functions
LET, SUMIFS, IFS, IFERROR Functions
Form Control, LET, SUMIFS, IF, IFS Functions

Lookup Functions

VLOOKUP, HLOOKUP Functions
LOOKUP Function
LET, SUMIFS, IFERROR, LOOKUP Functions
MATCH, VLOOKUP Functions
INDEX, MATCH Functions
XLOOKUP Function
INDIRECT, ADDRESS Functions
INDIRECT, ADDRESS, ROW, COLUMN Functions with Combo Box
INDIRECT, ADDRESS, MATCH Functions
OFFSET Function
SUM, OFFSET Functions
INDEX, MATCH, XLOOKUP, OFFSET Functions
SUM, OFFSET, MATCH Functions

Data Validation and Conditional Formatting

Data Validation Whole Number, Date, Decimal
Data Validation Text Length, VLOOKUP, IF
Data Validation List
Data Validation List, Remove Duplicates
Data Validation List, OFFSET, MATCH, COUNTA
Data Validation Error Text and Types
Data Validation, AND
Data Validation, MIN, MAX, XLOOKUP
Conditional Formatting Cells Values
Conditional Formatting Duplicate and Unique Values
Conditional Formatting Color Scale
Conditional Formatting Data Bar
Conditional Formatting Icon Set
Conditional Formatting, Comparison Operator
Conditional Formatting, Comparison Operator, Data Validation
Conditional Formatting, comparison operator, MAX, AND
Conditional Formatting, Comparison Operator, WEEKDAY, Check Box

Combined Functions

SUMIFS, VLOOKUP, & Functions
SUMIFS, INDEX, MATCH Functions
SUM, SUBTOTAL, OFFSET, IF, ROW Functions
SUMIFS, Check Box, Conditional Formatting, Sparklines
CONCAT, COUNTIFS, MATCH, Filter
CONCAT, COUNTIFS, INDEX, MATCH, LARGE Functions
SUMIFS, OFFSET, MATCH Functions
SUMIFS, OFFSET, INDIRECT, ADDRESS Functions
UNIQUE, TRANSPOSE, LET, SUMIFS, IF, OFFSET, MATCH Functions
OFFSET, MATCH, Name Manager

Mastering Basic Charts & Graphs

Bar & Column Charts
Histogram & Pareto Charts (Office 365 and 2016+)
Line Charts & Trendlines
Area Charts
Pies, Donuts & Race Tracks
Scatter Plots
Bubble Charts
Box & Whisker Charts (Office 365 and 2016+)
Tree Maps & Sunburst Charts (Office 365 and 2016+)
Waterfall Charts (Office 365 and 2016+)
Funnel Charts (Office 365 and 2016+)
Radar Charts
Stock Charts
Heat Maps
Surface & Contour Charts
Basic Combo Charts
Sparklines

Next-Level Data Visualization Exercises

Custom Image Overlay Charts
Adding Binary Values to Highlight Date Ranges
Automation with OFFSET, COUNTA & Named Ranges
Adding Scroll & Zoom Functionality with Form Controls
Animating Changes Over Time
Building a Dynamic Dashboard
Value-Based Dynamic Formatting
Dynamically Highlighting a Data Series
Building a Custom Pacing Chart
Designing a Custom Gauge Chart
Visualizing a Percentage Using Array Functions

Formula Auditing and Protection

Data Modelling

Dynamic Arrays Functions

Formulas Spill & Other Excel Dynamic Array Characteristics
Excel FILTER Function (The New Excel Power Multi-Lookup)
SORT & SORTBY Functions in Excel
UNIQUE Function (Get a Unique List of Values in Excel)
Practical Combination of UNIQUE, SORT & FILTER in Excel
SEQUENCE Function in Excel
RANDARRAY Function in Excel
‘@ Prefix for Compatibility (in Excel Formulas)
Create an Excel Summary Report FAST
XLOOKUP Function in Excel with Practical Examples
Backward & Forward Compatibility with Excel Dynamic Arrays
Absolute & Mixed Cell Referencing – No Longer Needed in Excel?
SUMIFS & COUNTIFS Improved! (More than one criteria in the same column)
VLOOKUP with Excel Dynamic Arrays
IF Function & Excel Dynamic Arrays
Referencing Spilled Data Ranges in Other Excel Sheets
TRANSPOSE & FREQUENCY Functions Made Easier with Excel DA
Data Analysis with Legacy Excel Formulas & Dynamic Arrays
Data Validation List using Spill Range (and Excel Table)
Excel Drop-down List (Excludes Blank Cells)
Dependent Drop-down Excel Data Validation (Unique & Sorted)
Conditional Formatting & Excel Dynamic Arrays
Excel Charts with Dynamic Arrays
Dynamic Dependent Data Validation Excel Report
Interactive Excel Dashboards with Radio Buttons
Boolean Logic (TRUE & FALSE) in Excel Formulas Explained
Excel FILTER to Exclude Blanks, Zero-Value Cells & Text
FILTER Function for Multiple Criteria (AND, OR Logical Test in Excel)
Dynamic Array Filter Like a Pro
FILTER Function with Array Constants for IF EMPTY Argument
Top 3 Values with Excel LARGE Function

Financial Functions

Interactive Reports Made Easy with Excel Dynamic Arrays

Data Analysis Project

Project Introduction
Preparing for Scale
Using Name Manager for Filter Values
Calculating KPIs
Applying Conditional Formatting Rules
Inserting Linked Pictures
Using Absolute Structured References
Creating a Line Chart
Hiding Zero Values in a Line Chart
Creating Complementary Bar Charts
Dynamically Sorting Data
Creating Top-Bottom Tables
Setting Up the Dashboard Layout
Using Color Strategically
Using Titles to Tell a Story
Sharing the Dashboard Online
Formula Based Formatting

Pivot Tables Fundamentals

Why Pivot Tables?
Structuring Source Data for Analysis in Excel
Creating Your First Pivot Table
IMPORTANT: Add to Data Model
Navigating the Pivot Table Field List
Manipulating the Pivot Table Field List
Exploring Pivot Table Analyze & Design Options
Selecting, Clearing, Moving & Copying Pivot Tables
Refreshing & Updating Pivot Tables
Dealing with Growing Source Data
Removing & Reviving Data from the Pivot Table Cache
How Excel Pivot Tables ACTUALLY Work

Formatting Data with Pivot Tables

Sorting, Filtering & Grouping Data with Pivot Tables

Sorting Data with Pivot Tables
Fixing Incorrect Alphabetical Sorting
Filtering Data with Pivot Table Label & Selection Filters
Pivot Table Label Filters with Wildcards
Filtering Data with Pivot Table Value Filters
Enabling Multiple Pivot Table Filters
Grouping & Segmenting Data with Pivot Tables
Enabling & Disabling Automatic Date Grouping
Filtering Data with Pivot Table Slicers & Timelines
Breaking Out Pivot Table Report Filter Pages

Enriching Data with Pivot Table Calculated Values & Fields

Aggregating & Summarizing Data with Pivot Tables
Avoiding the Pivot Table “Count Of” Trap
Defining Value Calculations with Pivot Tables
Calculating Pivot Table Values: % of Column/Row
Calculating Pivot Table Values: % of Parent
Calculating Pivot Table Values: Difference From
Calculating Pivot Table Values: Running Total
Calculating Pivot Table Values: Rank
Calculating Pivot Table Values: Index
Pivot Table Value Calculations
Defining Calculated Fields with Pivot Tables
Creating Calculated Fields in Pivot Tables vs. Source Data
Pivot Table Calculations Using Count Columns
Inserting Pivot Table Calculated Items
Generating Solve Order & List Formulas Reports

Visualizing Data with Excel Pivot Charts

Intro to Excel Pivot Charts
Pivot Chart: Column Chart
Pivot Chart: Pie & Donut Charts
Pivot Chart: Clustered Bar Chart
Changing Pivot Chart Types
Pivot Chart: Stacked Area Chart
Customizing Pivot Chart Layouts & Styles
Applying Slicers & Timelines to Multiple Pivot Charts
Building Dynamic Dashboards with Pivot Tables & Pivot Charts

What-If Analysis

Data Analysis ToolPak

Power Query: Introduction and Essentials

Power Query: Utilizing the Power of Power Query

Power Query: Transformations

Power Query: Appending and Combining Data Source

Power Query: Custom Column & Basic M Manipulation

Advanced Problem Solving using the Power Query Interface

PROJECT – Create a Modern Excel Dashboard with Power Query & Pivot Tables

Power Pivot, DAX & Data Modelling Essentials

Power Pivot: Getting Started with Data Modelling

Power Pivot: DAX Measures and Syntax

Power Pivot: Calculated Columns V/s Measures

Power Pivot: DAX Functions & Multiple Fact Tables

Power Pivot: Advanced DAX Functions

Power Pivot: DAX Time Intelligence

Dashboard PROJECT: 365 Boot Camp Headquarters Reporting

Student Ratings & Reviews

No Review Yet

About Course

What Will You Learn?

Course Content

Excel Fundamentals

Freeze Panes, Gridlines, Formula Bar, Headings, Split Window, Comments

Freeze Panes, Gridlines, Headings, Split Window, Comments

Sort and Filter

Filter, Add current selection to filter, * symbol

Remove Duplicates with Sort

Hide, Unhide with Filter

Insert Row, Delete Row with Sort

Grouping and Freeze Panes

Find, Replace, * symbol

Replace, Format Cells, Find in comments

Delete Comments, Delete Row, Go to Special, Filter

Horizontal Sort

Hyperlink

Introduction to Formulas: Mathematical Calculation

Introduction to Absolute and Relative References, $ signs

Copy + Paste special

Fill Handle and Flash Fill

Mathematical Functions

SUM Function

SUM, AVERAGE Functions

SUM, Reference Cells from Other Worksheets

SUM, Working on Multiple Worksheets at the Same Time

AVERAGE, Working on Multiple Worksheets at the Same Time

COUNT Function

COUNT, COUNTA, COUNTBLANK Functions

COUNT, SUM Functions

COUNT, Reference Cells from Other Worksheets

SUM, COUNT, COUNTA Functions

SUM, COUNT, COUNTA with Filter and Sort

MIN, MAX Functions

SUM, ABS Functions

ABS, Paste Special

PRODUCT Function

PRODUCT, $ Signs

LARGE, SMALL Functions

RANK.AVG, RANK.EQ Functions

SUBTOTAL, AGGREGATE Functions

SUBTOTAL, AGGREGATE with Filter and Group

ROUND, ROUNDUP, ROUNDDOWN Functions

SUMIFS Function

Name Manager

Name Manager with SUM, AVERAGE

Name Manager with SUMIFS, AVERAGEIFS and * Symbol

Name Manager, AVERAGEIFS, YEAR Functions

Name Manager, AVERAGEIFS with > & < Symbols

Name Manager, COUNTIFS with > & < Symbols

Name Manager, MINIFS, MAXIFS with > & < and * Symbols

Name Manager, SUMIFS, MAXIFS

SUMPRODUCT Function

Name Manager, SUMPRODUCT with * Symbol

Text Functions

CONCATENATE Function / & symbol

SUM, AVERAGE, CONCATENATE / & symbol

LEFT, RIGHT Functions

LEFT, CONCATENATE / & symbol

FIND / SEARCH Functions

MID Function

LEN Function

LEN, SUM Functions

LEFT, RIGHT, LEN, FIND Functions

LEFT, MID, FIND, CONCATENATE / & symbol

REPLACE Function

REPLACE, RIGHT, FIND Functions

SUBSTITUTE Function

LEN, SUBSTITUTE, LEFT, FIND, SUM Functions

TRIM Function

SUBSTITUTE, TRIM, CONCATENATE / & symbol

SUBSTITUTE, FIND, MID Functions

UPPER, LOWER, PROPER Functions

PROPER, TRIM Functions

LEFT, FIND, UPPER, MID, SUBSTITUTE Functions

REPT Function

N, T Functions

CONCAT Function

TEXTJOIN Function

Text to Columns