EXCEL (with Power BI)

Excel (with Power BI)

1. I teach the skills required to understand Excel and Power BI.

2. I demonstrate how Excel can be utilized during Data Analysis.

3. I teach about the components of a Worksheet Formula Model.

4. I teach tactile skills e.g., understanding the Keyboard and Mouse.

5. I teach Short-hand skills e.g., decoding the screen cursor indicators.

6. I present a Curriculum founded on systematic (step-by-step) information.

7. I teach strategies for understanding the intuitiveness of Excel and Power BI.

8. I teach Data Analysis with the PivotTable, Power Query Data Model and Power BI.

9. I teach Data visualization and how to use Excel and Power BI for maximum impact.

10. I demonstrate efficient techniques for Data Modelling, Analysis and Visualization.

11. I teach methodical approaches to cleaning, transforming and importing Data.

12. I teach about Logical Formulas: Comparative Operators, IF, AND, OR and I

13. I teach Data Validation and the Lookup functions; XLOOKUP and FILTER

14. I teach on Conditional Calculations e.g., COUNTIF and SUMIF

15. I include tutorials on LET and LAMBDA functions.

12. I teach about using Formulas to build Reports.

MODULE ONE

Beginners learning Excel need to approach their foundational lessons on three levels: (1) Excel Form, (2) Excel Function, and (3) Excel Navigation. I teach the skills required to establish a standard degree of fluency in Excel. I also demonstrate the Mathematical and Functional Capacity of Excel. Beginners who complete this course will gain the ability to (1) speak the Language of Excel and (2) understand the Computational nature of Excel.

Excel Form

1. Launching Excel
2. Creating a text file
3. Creating an image file
4. Organization of files
5. Rows, Columns, Cells
6. Data and Data Alignment
7. Data organization: Color Coding
8. Establishing a Naming System
9. PivotTable: Number formatting
10. Excel Worksheet Formulas
11. Introduction to Workbooks
12. Workbook Navigation
13. Style Formatting
14. Excel Line Chart

Excel Function

1. Formulas and Functions
2. Sum and Difference Formulas
3. Product and Quotient Formulas
4. Data Alignment to track Errors
5. Using Comparative Operators
6. Creating more Complex formulas
7. Creating Logical Formulas
8. Excel Table and PivotTable
9. Building Data Models with Excel
10. Visualizing with Excel Charts
11. Excel's 3 Core Functions:
Data Calculation
Data Analysis
Data Storage

Excel Navigation

1. The Keyboard and Mouse
2. The Pointers and Controls
3. Tabs in the Excel Ribbon
4. The Contextual Menu tabs
5. Quick Access Toolbar (QAT)
6. The Screen Cursor indicators
7. The In-Ribbon Gallery Dialog box
8. Workbook Navigation
9. Keyboard Shortcuts
10. Alt Keyboard Shortcuts
11. List of Formula Elements
12. Excel F Keys on Keyboard
13. Keyboard Shortcut to Activate or Deactivate F Keys

MODULE TWO

Intermediate users learning how to use Excel with Power BI need to approach their lessons on two levels: (1) Data Organization, and (2) Data Analysis. I teach the skills required to understand and interpret complex information by Organizing and Analyzing Data. In this Module, Power BI is introduced and I teach learners how to use tools in Power BI i.e., Sort, Filter, Flash Fill, Power Query, PivotTable, Charts, Power Pivot. Intermediate learners who complete this module will become proficient at using Excel with Power BI to build User-friendly Data Reports and Visualizations.

Data Organization

1. Sorting and Filtering
2. Understanding Flash Fill
3. Standard PivotTable to Summarize Data
4. Understanding Protocols of Data Modelling
5. Power Query: Import, Clean, Transform and load Data
6. Load Data: Worksheets, Data Models, PivotTable Cache.
7. Understanding how and when to use PowerPivot
8. PowerPivot to import large amounts of Data
9. Introduction to the "Columnar Database"
10. Potential Pitfalls when building a Data Model
11. Transferring Data from Excel to Power BI Desktop
12. Comparison: Power BI Desktop and Power BI online
13. Importing PowerPivot Data Models to Power BI Desktop
14. Integrating Power BI desktop and Power BI Online
15. Integrating Excel and Power BI
16. Understanding the Power BI Dashboard
17. Importing Data: Power BI Desktop and Power Query
18. Import SQL Database to build an Interactive Dashboard
19. Columnar Database: efficiently compress and store High Volume Data (i.e., Data from a SQL Database)
20. Building DAX Date and Time Tables
21. A lesson on Worksheet Date and Time Formulas
22. Creating a Power BI Map Report

Data Analysis

1. Using PivotTable to Analyze Data
2. Visualizing Data and Building Reports
3. Worksheet Formulas and Functions: Data Modelling
4. Trouble-shooting: Incomplete or Incorrect Data Models
5. Standard PivotTable: Visualize Data with Excel Line Chart
6. Using PivotTable, Excel Charts and XLOOKUP Function:
7. DAX Measures: Formulas in Data Model PivotTable
8. Using PowerPivot to create Data Model Pivot Tables
9. PowerPivot to create Relationships between Tables
10. Using PowerPivot to create DAX Measures
11. Re-using and Re-formatting DAX Measures
12. Understanding the "Columnar Database"
13. Columnar Database to Reconstruct Calculations
14. Logical Formulas, Functions, Conditional Calculations
15. Using a Columnar Database to Simplify and Refine High Volume Data (e.g., Data from a SQL Database)
16. How and when to use the DAX Revenue Formula
17. One-Step DAX Revenue Measure
18. Two-Step DAX Revenue Measure
19. Power BI: Interactive Models, Reports, Visualizations
20. Power BI Dashboard: Relevant and Useful Information required for Data Analyses and Conclusions
21. Creating a Power BI Analysis Report

MODULE THREE

This Module is for Excel learners who have a fundamental understanding of Excel with Power BI. This Module is designed to equip learners with tools to deepen their knowledge of of Data Organization, Data Analysis and Data Visualization. I teach the skills required to locate errors and fix discrepancies in Data Models. I demonstrate methodical approaches to Trouble-shoot potential Pit falls that occur during Data Modelling. In this module, (1) I dive deeper into some of the topics covered in Module One and Module Two e.g., Power Query and DAX and (2) I cover new topics e.g., Array Functions and LAMBDA Function.

Deep Dive

1. Data Visualization in Excel and Power BI
2. Building Dashboards in Power BI Desktop
3. Comprehensive Overview of Array Formulas
4. Dashboard Fundamentals in Excel and Power BI
5. Understanding the Principles for Formatting Tables
6. PivotTables: Conditional Formatting for Data Visualization
7. Understanding Chart Types for optimal Data Visualization
8. Building Dashboards in Excel using PivotTables
9. Power Query: Import and Transformation
10. Power Query M Code: Joins, Group By and UNPIVOT
11. Using Power Query and M code to Find Errors and Fix Discrepancies in Data Models
12. Customizing PivotTables
13. DAX Fundamentals

New Topics

1. Array Functions and Array Formulas
2. Using Array Functions to Edit Texts
3. Using Array Functions to Reorient Data
4. Using Array Formulas to Build Dashboards
5. Using Array Functions to create Arrays of Numbers
6. SUMPRODUCT Function to make Array Calculations
7. Array Functions: Statistical and Matrix Algebra Calculations
8. LET, Defined Names and Single Cell Reports
9. Introduction to Excel LAMBDA
9. LAMBDA: Conditional Formatting for Dynamic Reports
10. LAMBDA Helper Functions:
BYCOL, BYROW, MAKEARRAY,
MAP, SCAN, ISOMITTED, REDUCE
10. LAMBDA: Conditional Formatting for Dynamic Reports

How It Works

Teaching Clients how to transform Data into User-friendly Data Reports and Visualizations.

EXCEL (with Power BI)