Earn And Excel Courses

Mindset 1 - Execution is everything

Mindset 2 -Quick Wins

Mindset 3 -Dopamine will be the death of your Career

Mindset 4 -What about other tools

Mindset 5 -Strip Malls vs Skyscrapers

Mindset 6 - 80:20 Principle & The Paradox of Choice

Mindset 7 -How to use Excel as a Professional

Mindset 8 -One off Analysis vs Modelling

Mindset 9 - Performance of Excel Files

Mindset 10 - Analyst Empathy

Mindset 11 - Educate don't overwhelm

Mindset 12 - Metrics Vs KPIs

Mindset 13 - It's all about MI

Mindset 14 - MI Options with Excel

Mindset 15 - Analyst Toolset

Mindset 16 - Why do analyst roles exist

Mindset 17 - My Career trajectory is useful to understand

Mindset 18 - Beware of Beautiful Dashboards

Introduction to the Beginner to Intermediate Excel Section

1. Intro to Excel, Saving and File Types

2. Excel Sheet and Book structure

3. Orientation to the Ribbon

4. Entering Information

5. Moving and selecting without the mouse

6. Basic Formatting

7. Dragging and Autofill

8. Flash Fill

9. Copy Paste Cut

10. Clear Cells

11. Deleting and Inserting

12. Date Formats are crucial

13. Page and Print Set Up

14. Worksheet Functionality

15. Borders - Advanced

16. Formula Basics

17. Charts - Line

18. Charts - Pie Chart

19. Charts - Bar Chart

20. Data Analysis - Sort

21. Data Analysis - Autofilter

22. Text Analysis

23. Conditional Formatting

24. Custom (Advanced) Data Formatting

25. Data Validation

Exercise Explanation

Week 1 Exercises and Tiny Project

Introduction to Finding Information

1. Absolute and Relative References

2. Vlookup Introduction

3. Vlookup - Define the range

4. Vlookup Variable input

5. Vlookup - Automate Columns

6. Vlookup - Common Errors

7. Vlookup - Suppressing Errors

8. Index and Match

9. Limits and Rank Formulas to Find Information

10. Named Ranges

11. Long Boring Lists are key

12. Long Boring Lists - Monthly Data

13. Finding Information - Mini Lookup Dashboard 1

14. Finding Information - Mini Lookup Dashboard 2 (Exercise)

15. Exercises Part 1

Week 2 - Exercises Part 1 - Finding Information

Week 2 - Part 2 - 16. Building Logic with IF

17. Modelling software bug testing with IF

18. Extending our Logical Inputs with AND OR

19. The Logic behind measuring changes

20. Working with Text

21. Text Formulas - PROPER, TRIM and CLEAN

22. Text Formulas - LEN

23. Text Formulas - LEFT

24. Text Formulas - RIGHT

25. Text Formulas - MID

26. Text Formulas - SEARCH

27. Text Formulas - Extracting Names

28. Advanced Text Analysis

29. Combining Lookups, Logic and Text - The Resource Model

30. The XLOOKUP is superior to VLOOKUP but…

31. Dynamic Array Function - SORT

32. Dynamic Array Function - FILTER

33. Dynamic Array Function - Combining SORT, FILTER with UNIQUE

Week 2 - Part 2 - Exercises Lookup Logic

Week 2 - Part 2 - Exercise Explanations - XLOOKUP

Week 2 - Part 2 - Exercise - XLOOKUP

Week 2 - Part 2 - Exercise Explanations - Filter

Week 2 - Part 2 - Exercise - Filter

Introduction to Summarising Information

1. Introduction to Counting

2. The COUNTIF for conditional counting

3. COUNTIF and UNIQUE are a brilliant combination

4. COUNTIFS for more criteria

5. Deduplication with COUNTIF

6. Introduction to Summing

7. SUBTOTAL Use cases

8. SUMIF and COUNTIF to summarise for Senior managers

9. Summarising with Dynamic Pie Charts

10. Treemaps have their place

11. Using Combination charts to generate Analytical discussion

12. Investigating Anomalies with Time Series Analysis

13. Advanced Conditional Formatting - Using Formulas

14. Creating Dynamic Borders with Conditional Formatting

15. Dynamic Multiple Criteria Pie Charts

Week 3 - Exercise Part 1 - Explanation

Week 3 - Exercise Part 1 - Summarising Information

Before we begin Pivot Tables...

Introduction To Pivot Tables Course

Workbook for Module 1 - Introduction and Data Preparation

1. Introduction to Pivot Tables

2. Pivot Cache

3. Altering the Data Source

4. Unpivot Data To A Long Boring List

5. Moving Row And Column

6. Expand And Collapse Field

Workbook for Module 2 - Layout and Formats

7. Format Error and Empty cells

8. Change The Layout Of Your Pivot Table

9. Place Pivot Table

10. Pivot Table Styles

11. Format Content In Pivot

Module 3 Workbook - Core Pivot Table Analysis

12. Summarise Value By

13. Multiple Subtotal

14. Percentage Of Column Total

15. Difference From Value As Percentage

16. Double Click For Detail

17. Running Total

18. Grouping Year Month Quarter

19. Sorting

Workbook for Module 4 - Filters and Slicers

20. Filtering - Part 1

21. Filtering - Part 2

22. Slicer - Insert Style Property

23. One Slicer Connected To Multiple Pivots

24. Timeline Slicer

Workbook for Module 5 - Advanced Analysis

25. Calculated Field

26. Link Multiple Tables Into One Pivot - Part 1

27. Link Multiple Tables Into One Pivot - Part 2

28. Calculated Field With IF Statement

Module 6 Workbook - Visuals and Reports

29. Conditional Formatting In Pivot - Part 1

30. Conditional Formatting In Pivot - Part 2

31. Pivotcharts Part 1

32. Pivotcharts Part 2 - Linking And Slicers

33. Pivotcharts Part 3 - Formatting

34. Dashboard 1 - What We Will Build

35. Dashboard 2 - Our Dataset

36. Dashboard 3 - Pie Chart

37. Dashboard 4 - Line Chart

38. Dashboard 5 - Bar Chart

39. Dashboard 6 - Map Chart

40. Dashboard 7 - Putting It All Together

Week 3 - Part 2 - Exercise Explanation

Week 3 - Part 2 - Exercise Pivot Tables

A note about Power Query

Note regarding Power Query course materials

Power Query Module 1 materials

1. Introduction to Power Query

2. Getting Familiar with the Power Query Editor

3. First Data Clean Up for a Pivot Table

4. Tweaking our Query Until the Data is Correct

5. Rounding Data

6. Altering Data Types

7. Filtering Out Data Before We Load It

8. Advanced Calculated Columns

9. Grouping Columns

10. Using Columns From Examples

Introduction to Insight Mastery

The Two Types of Visual Analysis

Introduction to the Supply Chain Dashboard

Supply Chain Dashboard exercise File

1. Supply Chain Dashboard 1

2. Supply Chain Dashboard 2

3. Supply Chain Dashboard 3

4. Supply Chain Dashboard 4

5. Supply Chain Dashboard 5

Week 4 Comparison Exercises

1. Introduction to Dials

2. Dial - Initial build

3. Dial - Handling the delta

4. Dial - Labels

5. Dials - Bring it to life

1. Cluster Bar Comparison 1 - Introduction

2. Cluster Bar Comparison 2 - Basic Chart

3. The important art of making fake data

4. Cluster Bar Comparison 3 - Bringing to life

5. Cluster Bar Comparison 3 - Target Line

Week 4 Resource Analysis Exercise

1. Resource Analysis 1 - Using Networkdays

2. Resource Analysis 2 - Dynamic Charting

3. Resource Analysis 3 - Comparison -Actual vs Forecast

4. Resource Analysis 4 - Composition

Week 4 Part 2 - 1. Dashboard Design Principles - Fundamentals

2. Dashboard Design Principles - Colour Blindness Considerations

3. Dashboard Design Principles - Dimensions

4. Dashboard Design Principles - Qualitative vs Quantitative Data

5. Dashboard Design Principles - Excel vs Power BI

6. Dashboard Design Principles - Project Management

The Camera Tool is useful for Dashboarding

7. Project Management 1 - Exercise Explained

8. Project Management 2 - Planning with a Wireframe

9. Project Management 3 - Core Dataset Trend

10. Project Management 4 - Using FILTER to view our core data

11. Project Management 5 - Enhancing Title with the Camera Tool

12. Project Management 6 - Multidimensional Gannt Chart Build

13. Project Management 7 - Optimising the Gannt chart for the front page

14. Project Management 8 - Using a Dial for overall progress

15. Project Management 9 - Placing visuals on the front page

16. Project Management 10 - Closing remarks (Updating the data)

Week 4 Part 2 - Project Management Exercise 1

Bling Leatherwearz Dashboard Exercise 2 Explanation

Week 4 Part 2 - BGL Dashboard Exercise 2

Health For Initiative Dashboard Exercise 3 Overview of our solution

Health For Initiative Dashboard Exercise Introduction

Week 4 Part 2 HFI Dashboard Exercise 3

Week 5 Introduction

Week 5 - Part 1 -Exercises to follow along to

1. Getting started with the VBA Editor

1b. Subs and Comments

Object Based Programming

2. Ranges and Cells

3. Copy and Paste

4. Fonts and Colour

5. Multiple Lines and Step Through

6. Clearing Cells

7. Skipping lines of code

8. Column Width

9. Building Logic with the IF statement

10. Building Logic with SELECT CASE

11a. The Importance of Loops

11b. Introduction to Automation

12. Looping and selecting ranges

13. Manoeuvering and Macros

14. Variables and Constants

15. Detecting Range is CRUCIAL

16. Your first piece of Automation

17. Writing formulas vs VBA in modern Excel

18. Macro file format

19. Developer Tab and Macro Security

20. Modules, Macros and Relative References

21. Your first Button

22. Charting by Macros

23. When Macros go wrong

24. The importance of Error handling

25. A second counter to level up our Automation

26. Using End With makes us better coders

27. Automating Autofilters

28. Automating Formulas

29. Week 5 - Part 1 - Tasks and [Optional] Mini Projects Exercise Explanation Video

30. Week 5 - Part 1 - Tasks and [Optional] Mini Projects Exercise File

Week 5 Part 2 - 5 Levels of Looping Exercise File

5 Levels of Looping Part 1

5 Levels of Looping Part 2

5 Levels of Looping Part 3

5 Levels of Looping Part 4

5 Levels of Looping Part 5

1. Introduction to Gathering Information

2. The Data and Template

3. Understanding the process for the code

4. Replicate the template

5. Find the start point in the dataset

6. Populate the template

7. Save a version of the populated template

8. Make a unique list of template names

9. Put it all together and produce the templates

10a) Mini Project 1 Explanation

10b. Mini Project 1 Exercise File

11. Why we connect to PowerPoint

12. Introduction to the PowerPoint Object Model

13. Programming in PowerPoint 1

14. Programming in PowerPoint 2 - Shapes

15. Programming in PowerPoint 3 - Tables

16. Opening PowerPoint from Excel

17. Pasting an Excel range as an Image in PowerPoint

18a) Mini Project 2 Explanation

18b) Mini Project 2 FIle

Excel Course Outro - Congratulations on making it to the end!

1. Power BI SQL Dataset files

1. Why Learn Power BI, Your Instructor

2. What is Power BI

3. Why should you learn Power BI

4. Power BI in the Modern Data Analysis Stack

0.1 Intro to Power BI Original

0.2 Basic Power BI Block Intro

1. Setting up PBI

2. PBI Desktop Log In _ Dataset Exploration

3. Importing Production Inventory _ Power Query Transformations

4. Power BI Dashboard Inspiration

5. Power BI Table Visual _ Slicer

6. Adding More Data, PQ Transformations _ Data Model

7. Card Visuals, Pie Charts

8. An Overview of Power BI Report Filters

9. More Power Query Transformations, Custom Columns and Data Modelling

10. An Overview of Star Schema Data Modelling

11. Principles of Data Visualization

12. The Basic Bar Chart

13. Line Chart, Treemap, Visual Classes

14. Drill Down, Drill Through Visuals

15. Table Visual vs Matrix Visual

16. Filters Deep Dive

17. Edit Visual Interactions

18. Building a Pop Out Slicer Pane with Bookmarks and Selection Pane

19. Knowledge Check- Power BI Basics

0.1 Intro to Power BI- Basic Dashboard Build

1. Basic Dashboard Build- Design _ Table

2. Basic Dashboard Build- Slicers _ Interactive Card

3. Basic Dashboard Build- Design, Grouping, Buttons, Finalise

4. Publishing your Basic Dashboard

5. Basic Dashboard Challenge Brief

0.1 Intermediate Power BI Demo Dashboard

1. Exploring our Intermediate Data Model

2. What is DAX- Documentation Overview

3. Practical DAX Intro- Measures, Calculated Columns

4. Create a Dimensional Date Table

5. Building our Dax Date Table

6. Commenting our Dax Code

6.5 DAX Calculated Column- Nested IF Statements, Currency

7. DAX Time Intelligence- SAMEPERIODLASTYEAR _ Variance _ Conditional Formatting

8. Adding our first Time Intelligence Measures to Graphic Visuals

9. PREVIOUSDAY _ PREVIOUSMONTH DAX Functions

10. Dynamic Visual Titles using DAX

10.5 Calculate in DAX- Explanatory Video

10.75 Calculate DAX Measures Demo

11. Field Parameters Demo

12. Conditionally Formatting Bar Chart Column Colour by Rules _ DAX

13. Custom Visuals Deep Dive

14. Explaining Append vs Merge

15. Practical Append, Merge, Grouping _ Summary Tables

16. Extracting Text Strings in Power Query with GUI _ M Code

17. Indexing Partitions with Power Query

18. The Advanced Editor in Power Query

19. Power Query Tips Demo

20. 3 Power Query Tips from the Real World

21. Intermediate to Advanced Knowledge Check

0.1 Power BI Final Report

0.2 Icons and Images to download for your Final Project

1. Final Challenge Brief Part 1

2. Design Tips- Flat Icons

3. Navigation Page Build

4. Overview Page Build

5. Details Dashboard Page Build

6. Info Dashboard Page Build

7. Final Challenge Brief Part 2

8. Final Congratulations- Course Completion

1. Additional Resources

1. About Your Instructor

2. What is SQL

3. What is SQL Server

1. Getting Started with SQL Server Brief

2. Downloading SQL Server & SSMS

3. Downloading and Importing the AdventureWorks Sample Database in SQL Server

4. Navigating SSMS

0.5 SQL Fundamentals Brief

1. The SELECT Statement

2. Select Distinct Statement

3. Filtering with the WHERE Clause

4. Ordering Results with the ORDER BY Clause

5. Sampling Data with the SELECT TOP Clause

6. FIltering out NULL Values

7. Commenting SQL Code

8. Concatenating Strings

8.5 Essential String Functions

9. Mathematical Operators

10. Conditional Logic with the Case Expression

11. Aggregation Functions

12. Grouping Aggregation Functions with GROUP BY

13. An Introduction to Subqueries

14. Creating Tables, Inserting & Updating Data

15. SQL Joins Explanation with Venn Diagrams

16. Practical Join Demonstration

1. Basic Challenge Question 1

1.5. Basic Challenge Answer 1

2. Basic Challenge Question 2

2.5. Basic Challenge Answer 2

3. Basic Challenge Question 3

3.5 Basic Challenge Answer 3

1. Intermediate SQL Brief

2. SQL Formatters

3. An Introduction to Common Table Expressions

4. Building Tables with UNION ALL

5. An Introduction to Window Functions

6. Ranking Window Functions

7. Value Window Functions- LAG _ LEAD

8. Creating, Altering _ Querying Views

9. Pivot Tables

10. Creating Tables Efficiently with SELECT INTO

11. SQL Variables

12. The WHILE Loop

13. Conditional Logic with IIF

13.5. An Overview of Temporary Tables

14. A Comprehensive Guide to Stored Procedures

15. Exploring Date Functions

16. Enforcing Data Integrity with Constraints

17. Coding Constraints in T-SQL

1. Intermediate Challenge Question 1

1.5. Intermediate Challenge Answer 1

2. Intermediate Challenge Question 2

2.5. Intermediate Challenge Answer 2

3. Intermediate Challenge Question 3

3.5. Intermediate Challenge Answer 3

1. Advanced SQL Brief

2. Creating Grand Totals in Query Results

3. Deleting Duplicate Records

4. Splitting Substrings into Rows with STRING_SPLIT

5. Email Validator in SQL

6. Monitoring Wide Tables with system views

7. Sorting Salespeople into Buckets with NTILE

8. Glue Two Row Sets Vertically with UNION ALL

9. Thinking Like a Data Analyst

1. Advanced Challenge Question 1

1.5. Advanced Challenge Answer 1

2. Advanced Challenge Question 2

2.5. Advanced Challenge Answer 2

3. Advanced Challenge Question 3

3.5 Advanced Challenge Answer 3

Course Wrap Up

Cheat Sheets to download and print

1. Installing Anaconda

2. Creating Notebooks

3. Navigating Notebooks

Downloads for Python Basics

4. How Python Works

5. Different data types

6. Styling Notebooks

7.1 Data Type Functions I

7.2 Data Type Functions II

8. Comparisons and Assignments

9. Print

10.1 If

10.2 If 2

10.3 While

10.4 For

11. Lists

12.1 Dictionaries

12.2 Dictionaries 2

12.3 Dictionaries 3

13.1 Custom Functions

13.2 Custom Functions 2

13.3 Custom Functions 3

14.1 Importing Libraries 1

14.2 Importing Libraries 2

14.3 Importing Libraries 3

Download for Numpy Notebook

15. Numpy Basics

16. Numpy Slicing

17. Numpy Iterating

18. Numpy Join and Split

19. Numpy Search and Sort

20. Numpy Random Numbers

Download for Pandas Notebook

21. Creating a dataframe

22. Reading CSV File

23.1 View data

23.2 View data II

24. Managing incomplete data

25. Calculation on columns

26. Changing Values

27. Iterating through rows

28. Joining Dataframes

29. Writing Pandas to File

30.1 Line Graphs

30.2 Line Graphs II

31. Scatter Plots

32. Bar and Pie Graphs

33. Pandas data