Earn And Excel Courses

Module 1 Practice Workbook

1. The 80:20 principle

2. Introduction to Excel for Professionals

3. Performance of Excel

4. Long Boring Lists

5. Long Boring Lists: Monthly Data

Small, Mid and Large Data Sets

6. Locking Down Cells

7. Finding Information: VLOOKUP

8. Finding Information: INDEX & MATCH

9. Finding Information: Limit Formulas

10. Getting Advanced with Lookup formulas

Multiple Criteria Article

11. Finding Information: putting it all together

12. Rule building with IF

13. Advanced Logic Building

Module 1 Outro

OPTIONAL: Excel Tables & Slicers

Module 2 Practice Workbook

1. Working with Text

2. Text Functions: PROPER, TRIM & CLEAN

3. Text Functions: LEN & SEARCH

4. Text Functions: LEFT, MID, RIGHT

Flash Fill

5. Text functions: Advanced Text analysis by building formulas

6. Text functions: Creating email addresses with SUBSTITUTE and ‘&’

7. Text functions: Creating character and word count limits

8. Advanced Text, Logic and Lookups: Creating a resourcing model

9. Introduction to Summarising

10. Counting

11. Summing

11a. Summing – Circular References explanation

12. SUBTOTAL function

13. Analysis walkthrough with Summing and Counting

14. What are Pivot tables and why they are overrated

15. Advanced List Analysis: Understanding Array Formulas

OPTIONAL: Introduction to Array Formulas

OPTIONAL: Array Formulas for Multiple Criteria

Download Multiple Occurrences Workbook

OPTIONAL: Array Formulas for Multiple Occurrences

16. Advanced Modelling with Array Formula Lookups

17. Dashboard and Reporting Practice for Professionals

18. Advanced Conditional Formatting: Using Formulas

19. Advanced Conditional Formatting: Beyond Highlighting

Why VBA & Macros

Intro: Don’t Fear VBA!

Module 3 Practice Workbook

1. Cells and Ranges

2. Copy and Paste

3. Controlling Fonts and Colouring Cells

4. Multiple Lines, Clearing and Column Widths

5. Building Logic with IF

6. Building Logic with Select Case

7. The Importance of Loops

8. Introduction to Automation

9. Loops and Selecting Ranges

10. Manoeuvring and Macros

11. Constants and Variables

12. The MOST important line in VBA you need to know!

13. Putting together everything so far to demonstrate Automation

14. Macro File Formats

15. Developer Tab and Macro Security

16. Modules, Macros and Relative References

17. A first look at Buttons

18. Charting driven by Macros

19. Why Macros can go wrong and what to do about it

20. Handling Errors and Sheet issues

21. Dynamic Charting: Part 1

22. Dynamic Charting: Part 2

23. Dynamic Charting: Part 3

24. End With

25. Controlling Autofilters

26. Controlling Formulas

Five levels of looping Part 1

Five levels of looping Part 2

Five levels of looping Part 3

Five levels of looping Part 4

Five levels of looping Part 5

VBA alternative: Gathering data with power query

Module 4 Introduction

Download Week 4 Practice Workbook

1. Introduction to Information Gathering

Article: Six data gathering rules that will make you stand out

2. Creating an Input Sheet Template

3. Mass producing Input Sheets 1: Set the scene

4. Mass producing Input Sheets 2: Worksheet Variables

5. Mass producing Input Sheets 3: Autofilter revisited and Pastespecial

6. Mass producing Input Sheets 4: Manipulating filter criteria

6b. Mass producing Input Sheets 4b: Simplifying and bringing together

6c. Mass producing Input Sheets 4c: Creating a worksheet

7. Mass producing Input Sheets 5 Creating a unique list to prepare for looping

8. Mass producing Input Sheets 6 Instantly creating 25 unique input Excel files

9. Collating Data from Input Sheets 1 Starting the new process

10. Collating Data from Input Sheets 2 Open up one file and copy data

11. Introduction to the DO WHILE LOOP

12. Collating Data from Input Sheets 3 Looping through all files in a folder and copying data

13. Collating Data from Input Sheets 4 Setting up new data before we bring into our long boring list

14. Collating Data from Input Sheets 5 Looking up latest data MANUALLY into our long boring list

15. Collating Data from Input Sheets 6 Looking up latest data AUTOMATICALLY into our long boring list

16. Collating Data from Input Sheets 7 Putting it all together

Module 5 Introduction

Download Week 5 Practice Workbook

1. Why PowerPoint?

2. Introduction to the Object Model

3. Programming in PowerPoint 1: Basics

4. Programming in PowerPoint 2: Shapes

5. Programming in PowerPoint 3: Tables

6. Opening PowerPoint from Excel

7. Pasting an Excel range as an Image in PowerPoint

8. Translating Excel data into a table in PowerPoint 1

9. Translating Excel data into a table in PowerPoint 2: Multiple Slides

10. Drawing Shapes in Excel

11. Translating Excel data into Shapes in PowerPoint

Module 6 Introduction

1. Resource Analysis: Good practice for managing data

Download Lessons 1 - 5

2. Dynamic Charts

3. Charting Actual vs Forecast

4. Segmenting for deeper MI

5. Meaningful Resource Analysis

6. Interesting MI Aesthetics, automating folders and PDF files

Download Lesson 6 & 7 Template

7. Automate the creation of a PowerPoint slide deck

8. Combining PowerPoint with Information Gathering

Download Lesson 8 Template

9. Automating the production of a Project Summary front page in PowerPoint

Download Lesson 9 Excel Template

Download Lesson 9 PowerPoint Template

10. Automating the production of a monthly P & L report

Download Profit and Loss Dashboard

Download April Figures Zip File

11. Milestone Reporting Tool for Project Management

Download Milestone Tool

12. Build a Gannt Chart in PowerPoint

Gannt Tool

Goodbye and Next Steps

1. Introduction and Creating an MS Word Invoice from Excel

Download Word Invoice Generator

2. Gathering Data from MS Word documents in to Excel (Change Control Form)

Download Change Control Log

Download Word Forms (Zipped)

3. Purchase Order System: Part 1 – Excel To Word

Download PO Data, Form and Form Final

4. Purchase Order System: Part 2 – The Word Template

Download Files

5. Purchase Order System: Part 3 – Save & Print Button

Download All Files for Part 3

6. Purchase Order System: Part 4 – Word To Excel

Download All Files for Part 4

1. Introduction to Marketing Your Excel Skills

2. How Excel fits in with your overall career

3. Excel careers that can earn you six figures

Download 6 Figure Excel Careers

4. 5 Powerful ideas to use on your CV/Resume to sell your Excel Skills

5. Bringing out Excel skills on a Resume

Download Serena Original CV

Download Serena Rework CV for Reporting Analyst

6. Excel Interview secrets

7. Get an edge on jobs which don’t involve much Excel

8. Final Thoughts on marketing your Excel skills