IIRF Online > Finance & Accounting > Financial Modeling & Analysis > Excel > Excel Essentials: The Complete Excel Series - Level 1, 2 & 3
Excel Essentials: The Complete Excel Series - Level 1, 2 & 3 by Udemy
Excel: The WHOLE Excel Mastery Series In One! From Excel Novice To VBA Programmer - Excel 2013, 2016, 2019 & Excel 365
Course Highlights
- By the end of Level 1:
- Master all the basics of Excel, even if you've never seen a spreadsheet before!
- Build your own spreadsheets from scratch, and actually understand how they work!
- You will be able to build your own simple, AND complex formulas (and know what the difference is!), and use some of Excels built in functions.
- Do in seconds, what a lot of seasoned users take minutes to do!
- Increase your chances of promotion, or a better paying job by understanding the most widely used business software in the world
- By the end of Level 2:
- Learn To Use Excel Like The Professionals
- Get Streets Ahead Of Your Competition
- Build Powerful Advanced Excel Projects From Scratch
- Create a fully functioning relational database using Excel
- Create a simple data entry screen to auto-populate complex templates
- Build Gantt charts in Excel for project planing
- Expoit Excels built in advanced functions to do things "regular" users think are impossible!
- By the end of Level 3:
- Learn to program in VBA from scratch
- Complete routine tasks thousands of times faster! (Yes, see the demo!)
- Confidently tackle any programming project
- Understand coding, and the thought process behind it
- Fully automate Excel using Visual Basic for Applications
- Fully automate report generation
- Program simple "helper" solutions
- Develop complex full blown applications from scratch
- Control other Microsoft applications like Outlook, Word, PowerPoint and even Windows itself!
- Create emails and send them automatically!
- Create Word documents using VBA code right from Excel!
- Create PowerPoint presentations in seconds automatically
- Create entire folder directories in Windows automatically!
Skills you will learn!
Curriculum
2 Topics
Introduction To Level 1
Before We Start...Viewing The Lectures And Following Along On A Single Screen
1 Topic
Where Are My Practice Files?
19 Topics
Opening Excel and Creating a Shortcut
The Anatomy Of A Workbook: Where Everything Is And What It Does!
A Quick Review Of What's Where
Accessing The "F" Keys (Function Keys) On A Laptop
Always Do This First! - Save Your New Workbook!
Let's Enter Some Data
Quiz 1
Editing Data
Ooops. I Made A Mistake - Undo and Redo
Changing Appearance Of Text With Formatting - Fonts
A Quick Word On Formatting
Formatting Text - Alignment
Saving Time With AutoFilling Sequences
POWER USER - Adding Your Own Lists To Autofill
Saving Time With Format Painter
Changing The Column Width
Quiz Number 2
Tidy Large Titles With Merge And Centre
Entering Data - A Couple Of Shortcuts
11 Topics
Sums - The Old Fashioned Way!
Sums - Using Autosum
Quiz Number 3
Copying Formulas
SUMming Horizontally
Basic Formulas - Subtraction
Basic Formulas - Multiplication
Basic Formulas - Division
Average Function
The Order Of Mathematical Operation
POWER USER - Evaluate Formula
5 Topics
Inserting New Columns And Rows
Moving Existing Columns And Rows
Cutting Copying Inserting And Deleting
Hiding Columns And Rows
Quiz Number 4
4 Topics
ROUNDing Functions
Formatting Numbers
A Primer In Building Complex Formulas
Buliding a Compex Formula
14 Topics
Sorting
Wrapping Text And Soft Enter
Adding A New Worksheet
Creating A Simple Chart
Quiz Number 5
Adding Borders
Customizing the Quick Access Toolbar
Simple Printing
Freezing For An Easier View
Getting Help
Filters
Highlighting Cells
Quiz Number 6
Closing
2 Topics
Bonus 1 Whizzing Around Excel
Bonus 2 Keyboard Shortcuts
4 Topics
A1 Style - Relative Relative
$A$1 Style - Absolute Absolute
$A1 Style - Absolute Relative
A$1 Style - Relative Absolute
3 Topics
Reverse Engineering A Sample Spreadsheet
Casing And Text Functions
Level 1 SQA 03 - Charts From A Filtered List
1 Topic
Intro To Level 2
23 Topics
Proof Of Concept
Planning Ahead
Creating Our Data Entry Screen
(Custom) Formatting Dates And Time
Simple Calculations With Time
More (Useful) Calculations With Time
It's About Time (And Dates!)
Adding Time
Creating A Template From An Image
Importing A Template From An Existing Excel File
Converting Time To A Decimal
A Little Bit Of Simple Data Entry
Simple Conditional Formatting For A Cleaner View
Calculating Time Out Of House Using Travel Time
Simple Logical Testing And Nested Logical Testing
Building Text Strings With A Formula
Before We Move On...Accessing The Developer Ribbon
A Tick Box Exercise (Of Sorts)
Auto-populating Check Boxes
Radio Buttons as an Alternative to Check Boxes
PRACTICE EXERCISE 1 - Time To Add A New Entry
Defining A Working Area And Protecting Your Work
PRACTICE EXERCISE 2 - Set Up A Working Area And Limit User Entry
26 Topics
Simple VLOOKUPs
Step 1 - Get Some Data In And Split It
Using Data Validation To Get The Right Input
Let's Build Our Database!
Importing Data From A Text File
Importing Data From A Word File
Pulling Data From Multiple Sources
Using OTHER Look-Ups To Look Up!
LOOKUP From A LOOKUP With No Intermediary Step
Data Arrays Don't Have To Start At A!
Some Common Reasons VLook-Ups Fail
One Inherent Flaw In Vlook Up
POWER USER - A Breakdown Of Looking Up Backwards
The Other Way Of Looking Up Backwards
Backwards Look-Ups In Action
POWER USER - Dealing With Inconsistencies In User Entry
POWER USER - Fuzzy Vlook-Ups
POWER USER - Vlook-Ups With Multiple Inputs
POWER USER - Looking Up Multiple Inputs Using An Array Formula
VLOOKUPs Brother...HLOOKUP
POWER USER - The Holy Grail - Returning Multiple Values From A Single Look U
What To Look For When THAT Formula Didn't Work
The Fastest Way To Modify Your Column Numbers
POWER USER - Vlook-Ups With Moving Columns
Putting It All Together
The Finishing Touch - How Many Records Did I Find
8 Topics
A Simple Static Named Range Using A Single Cell
Creating A Named Range Using A Range Of Cells
Using Row Labels To Name Multiple Ranges
POWER USER - A Magic Trick Using Row And Column Labels
POWER USER - Dynamic Named Ranges
POWER USER - What To Do With Dynamic Names Ranges With Titles
3035 - POWER USER - Dynamic Charts
3040 - Horizontal Dynamic Named Ranges For Charts
15 Topics
Welcome to "What Can I Have For Dinner?" or...What Would I Use THAT for?
Hyperlinking To A Different Sheet In The Same Workbook
Creating Our First Macro
Assigning A Macro To A Button
Creating A List For Our Dropdown Using A Dynamic Named Range
Using A Conditional Format To Know When A Value Is Missing
Copying Conditional Formats And Creating Our Drop-Downs
Building Our Formula...INDIRECT Function
Building Strings For Indirect Sheet And Cell References
It's A One Or A Zero
Working The Percentages And Adding Traffic Lights
POWER USER - The HYPERLINK Function (And Problem)
PRACTICE EXERCISE 1 - Fill In The Blanks
PRACTICE EXERCISE 2 - Pretty It Up (With A Macro)
PRACTICE EXERCISE 3 - Create A VLOOKUP Using A Built String With INDIRECT
6 Topics
Creating A Gantt Chart Using A Worksheet
Building The First Part Of Our Logical Test
Multiple Logical Tests At Once Using AND
Conditional Formatting...Where The Magic Happens
Gantt Charts Using The Built In Charting Tools
SQA - Gantt Charts With Different Colours For Different Categories
1 Topic
How I Created Randomly Generated License Plate Numbers!
20 Topics
Calls Text Data - Or How To Return a Column Title If Value is 1
Calls Text Data 2 - This Time Using Text!
Extracting Phone Numbers From A Cell
What Is The CHOOSE Function Really Used For?
Casing And Text Functions
Dynamic Charting From A Drop Down
Extracting a Unique List And Summing The Money!
SUMIF With Dynamic Sum Range
Vlookups With Pictures!
Data Validation With Dependent Drop-downs
Data Validation With Dependent Drop downs (Dynamic Named Range Workaround)
Using 2 Labels As A Lookup From Drop-downs
Tiered Pricing - SUMming So Many At One Price So Many At Another And So On...
Worksheet Protection
The Middle Name Problem and Solution!
Finding Matches And Counting Entries
Fee Calculator or LOOKUPs That Are True Not False
The IF(s) Functions
Drop Down List Shifting Ranges
Dynamic Calculations From Another Sheet
1 Topic
Why Should I Learn How To Code?
1 Topic
The 15 Golden Rules Of Coding
11 Topics
Introducing The Visual Basic Editor & Recording Our First Macro
Saving Macro-Enabled Workbooks And Security Settings
Moving Code Around
Stepping Out. Well In Actually! - Debugging Made Easy :)
With And End With
Streamlining You Code Or Get Rid Of What You Don't Need
Combining Your Code
A Little Privacy Please
Keyboard Shortcuts And Why I Don't Use Them
Why You Can't Get By With Just Recording Macros
Section 2 Quiz
32 Topics
Introduction To The Coding Section
Getting All The Code For This Section
Changing Your VBE Settings
Protecting Your Code
Objects Methods And Properties
Understanding The Hierarchy
The Range Object
The Cells Object
The ActiveCell Property
The Offset Property
The End Property
Dynamic Range Selection
The CurrentRegion Property
ACTIVATE vs. SELECT
Between The Sheets
Calling A Sheet By Its VB Name
Sheets Vs. Worksheets
Getting Around The Workbooks
The Value Property - Writing Data
The Value Property - Reading And Writing Data
Copy And Paste
Commonly Used Properties
CODING EXERCISE: The Rainbow
The Address Property
The Row and Column Properties
Capturing The Column Letter
More Useful Properties
Even More Useful Properties
Opening Another Workbook Programmatically
Closing Workbooks Programmatically
CODING EXERCISE: Open/Write/Close
Let's See How You're Doing...
40 Topics
Introduction To The Programmers Toolbox
Variables - Local Variables
Variables - Local Variables With A Twist
Variables - Module Level Variables
Variables - Project Level Variables
Bonus - Calling A Sub Stored In A DIFFERENT Workbook!
A Neat Trick To Force Variable Declaration
Variables - All The Techie Bits
An Introduction To Looping
Looping With Do...Loop
Looping With For...Next
Looping With A Stepped For...Next
Looping With While...Wend
An Introduction To Logical Testing
Logical Testing - A Simple If Test
Logical Testing - A Simple If Test Using Cells
Logical Testing - If Then Else
Logical Testing - If Then Else Using Cells
Logical Testing - Testing Multiple Criteria
Logical Testing - Testing If One Is True And One Is False
Logical Testing - Testing If Either Value Is True
Logical Testing - Select Case
Maths - Doing Simple Maths In Code
Maths - Writing Formulas To Single Cells
Maths - Writing Formulas To Ranges Of Cells
Maths - Using Excel's Built-in Functions
Maths - Built-in Functions With Defined Ranges
InputBox - Getting User Input Using The InputBox Function
Manipulating The User Input With Casing
InputBox - Getting User Input Using The InputBox Method
Message Boxes - Simple Message Boxes
Message Boxes - Testing Which Button Was Pressed
Arrays - An Introduction
Arrays - A Simple One Dimensional Static Array
Arrays - A Simple One Dimensional Dynamic Array
Arrays - A Simple Two Dimensional Static Array
Arrays - The Most Efficient Way To Capture An Array
Arrays - Extracting Useful Data Based On User Input
Arrays - Using An Array As A Data Source For A VLookup
Time For A Quick Quiz...
22 Topics
Introduction To Report Automation Section
A Special Note For Office 2010 Users
Recording The Bones Of The Code
Streamlining The "Add New Sheet Code"
Deconstructing The "Profit By Day" Code
Building Source Data Strings Dynamically At Runtime
Creating The Run Order and Data Capture Subs
Solving That Naming Problem
POWER USER - Sizing Your Charts Precisely
Changing The Chart Title (And Why We Do It Separately)
Deconstructing The Pivot Tables (It's Slightly Different)
Titles Money And Sorting
Butchering One Table To Create Another
Adding The Commentary - Building Strings Dynamically At Runtime
Adding The Commentary Using Data From The Sheet We're On
POWER USER - How DO You Make Specific Words Bold
POWER USER - INSTR...A Very Useful Function
INSTR And Paying Attention To Detail
Tidy Up The Title
Easy As Pie (Chart)
Prettying Up Our Pie Chart
Putting It All Together
9 Topics
Introduction To Web Query Section
Pulling Data From The Internet - Capturing The Data For Rome
Getting To Cancun And London From Rome
Data Clean Up
A Simple Find And Replace
Getting Our Formulas Right
Streamlining The Formulas Code
POWER USER - Displaying Messages In The Status Bar (Cool)
Putting It All Together
14 Topics
Intro To The Events Section
WorkBook SheetActivate
WorkBook BeforePrint
WorkBook SheetChange
WorkBook Open - Creating An Auto-Back Up
WorkBook Open - Creating A Splash Screen
WorkBook Open - Calling Other Code
WorkBook BeforeClose
WorkSheet Activate - You Can't Pick This!
WorkSheet Activate - You Might Pick This!
WorkSheet Change
WorkSheet Change - A More Useful Use
WorkSheet Activate - Top Secret Classified Information!
WorkSheet Events - BONUS - 4 New Things To Try
7 Topics
User Defined Functions...What They Are And How You Make Them
Using A UDF To Return Information
Creating A Countdown Timer With A UDF
A Custom UDF For Calculating Volume Discount
A UDF For Getting All Your Sheet Names
Calling A UDF From A Different Workbook
The SuperCountIf And SuperSumIf UDF's
4 Topics
Intro To Folder Creation Gizmo
Creating A New Folder With A Single Line Of Code
A Single Level Folder Structure
Folders Within Folders
4 Topics
Intro To The Emailing Section
Understanding The eMail Routine
Deconstructing How We Capture All The Data
The eMail Loop
6 Topics
Intro To The Word Section
Understanding The Word Routine
Deconstructing How We Capture All The Data
Formula Modifications With Unique Values
Efficient Sorting
Building The Text And Wrap Up
10 Topics
Intro To PowerPoint Section
A Run Through The PowerPoint Base Code
Setting Up The Shell Of The Code
Who's Presenting This...
Adding A Slide With A Logo And Text
Prettying Up The Formatting (More Lego Coding!)
Using Slide 1 To Create Slide 2
Adding A Chart As A Picture
Adding Pivot Tables (And Another Chart)
Final Slide And Wrap Up
5 Topics
Intro To Importing Data From A Folder Full Of Files
Looping Through All Excel Files In A Folder
The Folder Picker
A More Useful Loop Through Files
The Data Grabber(er)
20 Topics
Get rid of rows in a array
Emailing Routine: Adding a Specific Attachment Based On a Criteria
Adding A Date Stamp And Going To The Insertion Point AUTOMATICALLY
Saving An Individual Sheet To A Specific Folder
Saving Multiple Sheets To A Single Workbook In A Specific Folder
Animated Charts...With A Little Something Extra!
Extracting Specific Data From A Big File To A Bunch Of Little Ones
Finding A Search String in Another Workbook With Multiple Sheets
Extracting Unique Tables to Unique Sheets From A Big Data Set
Protecting Specific Cells and Data Validation
Dynamically Populating A Reusable Array While Looping Through A Table
Sequential PDF Creation With Pictures
File Picker And Report Generator With Intelligent Filing
Help! My File Has Got HUGE!
Finding Updated Values In One Workbook And Adding Them To Another Workbook
Gantt Charts...With A Little More Sophistication!
Vlookups (Or Any Application.WorksheetFunction) Over Ranges
Adding A Date Stamp If A Change Has Been Made
Creating a Toggle Custom Keyboard Shortcut
Creating Unique Passwords For Individual Sheets
1 Topic
Get Your Next Course Now!
Excel Essentials: The Complete Excel Series - Level 1, 2 & 3
Related Excel Courses
Thank You!
Your review has been submitted successfully.