The Anatomy Of A Workbook Where Everything Is, And What It Does!
Course Content
0 / 328 completedWhere Are My Practice Files
Before We Start...Viewing The Lectures, And Following Along On A Single Screen
Intro To Level 1
Opening Excel, and Creating a Shortcut
A Quick Review Of What's Where
Always Do This First! - Save Your New Workbook!
The Anatomy Of A Workbook Where Everything Is, And What It Does!
Let's Enter Some Data
Editing Data
Ooops. I Made A Mistake - Undo and Redo
A Quick Word On Formatting
Changing Appearance Of Text With Formatting - Fonts
Formatting Text - Alignment
Saving Time With Format Painter
POWER USER - Adding Your Own Lists To Autofill
Saving Time With AutoFilling Sequences
Changing The Column Width
Entering Data - A Couple Of Shortcuts
Tidy Large Titles With Merge And Centre
Copying Formulas
Sums - The Old Fashioned Way!
Sums - Using Autosum
SUMming Horizontally
Basic Formulas - Multiplication
Average Function
Basic Formulas - Subtraction
Basic Formulas - Division
POWER USER - Evaluate Formula
The Order Of Mathematical Operation
Inserting New Columns And Rows
Moving Existing Columns And Rows
Hiding Columns And Rows
Cutting, Copying, Inserting And Deleting
ROUNDing Functions
Formatting Numbers
A Primer In Building Complex Formulas
Buliding a Compex Formula
Sorting
Adding A New Worksheet
Wrapping Text And Soft Enter
Creating A Simple Chart
Adding Borders
Customizing the Quick Access Toolbar
Freezing For An Easier View
Simple Printing
Highlighting Cells
Getting Help
Filters
Closing
Bonus 1 Whizzing Around Excel
Bonus 2 Keyboard Shortcuts
A1 Style - Relative Relative
$A1 Style - Absolute Relative
$A$1 Style - Absolute Absolute
A$1 Style - Relative Absolute
Intro To Level 2
Planning Ahead
Proof Of Concept
Creating Our Data Entry Screen
(Custom) Formatting Dates And Time
Simple Calculations With Time
More (Useful) Calculations With Time
Adding Time
It's About Time (And Dates!)
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
Simple Logical Testing And Nested Logical Testing
Building Text Strings With A Formula
Calculating Time Out Of House Using Travel Time
A Tick Box Exercise (Of Sorts)
Auto-populating Check Boxes
Before We Move On...Accessing The Developer Ribbon
PRACTICE EXERCISE 1 - Time To Add A New Entry
PRACTICE EXERCISE 2 - Set Up A Working Area, And Limit User Entry
Defining A Working Area, And Protecting Your Work
Casing And Text Functions
Reverse Engineering A Sample Spreadsheet
Simple VLOOKUPs
Using Data Validation To Get The Right Input
Step 1 - Get Some Data In, And Split It
Let's Build Our Database!
Importing Data From A Text File
Importing Data From A Word File
Pulling Data From Multiple Sources
LOOKUP From A LOOKUP With No Intermediary Step
Data Arrays Don't Have To Start At A!
Using OTHER Look-Ups To Look Up!
One Inherent Flaw In Vlook Up
Some Common Reasons VLook-Ups Fail
POWER USER - A Breakdown Of Looking Up Backwards
Backwards Look-Ups In Action
The Other Way Of Looking Up Backwards
POWER USER - Fuzzy Vlook-Ups
POWER USER - Looking Up Multiple Inputs Using An Array Formula
POWER USER - Dealing With Inconsistencies In User Entry
POWER USER - Vlook-Ups With Multiple Inputs
VLOOKUPs Brother...HLOOKUP
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
POWER USER - The Holy Grail - How To Return Multiple Values From A Single Look U
Putting It All Together
The Finishing Touch - How Many Records Did I Find
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
Horizontal Dynamic Named Ranges For Charts
POWER USER - Dynamic Charts
Welcome to What Can I Have For Dinner or...What Would I Use THAT for
Hyperlinking To A Different Sheet In The Same Workbook
Assigning A Macro To A Button
Creating Our First Macro
Creating A List For Our Dropdown Using A Dynamic Named Range
Copying Conditional Formats And Creating Our Drop-Downs
Building Our Formula...INDIRECT Function
Building Strings For Indirect Sheet And Cell References
Working The Percentages And Adding Traffic Lights
PRACTICE EXERCISE 1 - Fill In The Blanks
Using A Conditional Format To Know When A Value Is Missing
POWER USER - The HYPERLINK Function (And Problem)
PRACTICE EXERCISE 2 - Pretty It Up (With A Macro)
It's A One Or A Zero
PRACTICE EXERCISE 3 - Create A VLOOKUP Using A Built String With INDIRECT
Building The First Part Of Our Logical Test
Creating A Gantt Chart Using A Worksheet
Conditional Formatting...Where The Magic Happens
Gantt Charts Using The Built In Charting Tools
Multiple Logical Tests At Once Using AND
SQA - Gantt Charts With Different Colours For Different Categories
Calls Text Data - Or How To Return a Column Title If Value is 1
Extracting Phone Numbers From A Cell
Casing And Text Functions
What Is The CHOOSE Function Really Used For
Calls Text Data 2 - This Time Using Text!
Dynamic Charting From A Drop Down
SUMIF With Dynamic Sum Range
Extracting a Unique List, And Summing The Money!
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
How I Created Randomly Generated License Plate Numbers!
The 15 Golden Rules Of Coding
Introducing The Visual Basic Editor, & Recording Our First Macro
Saving Macro-Enabled Workbooks, And Security Settings
Moving Code Around
Combining Your Code
Stepping Out. Well, In Actually! - Debugging Made Easy )
Streamlining You Code, Or, Get Rid Of What You Don't Need
A Little Privacy Please
With And End With
Keyboard Shortcuts, And Why I Don't Use Them
Why You Can't Get By With Just Recording Macros
Why Should I Learn How To Code
Introduction To The Coding Section
Getting All The Code For This Section
Changing Your VBE Settings
Protecting Your Code
Understanding The Hierarchy
Objects, Methods And Properties
The ActiveCell Property
The Range Object
The Cells Object
The Offset Property
ACTIVATE vs. SELECT
Between The Sheets
Dynamic Range Selection
The End Property
The CurrentRegion Property
Calling A Sheet By Its VB Name
Sheets Vs. Worksheets
The Value Property - Reading And Writing Data
Getting Around The Workbooks
Copy And Paste
Commonly Used Properties
The Value Property - Writing Data
CODING EXERCISE The Rainbow
The Row and Column Properties
The Address Property
Capturing The Column Letter
More Useful Properties
Even More Useful Properties
Opening Another Workbook Programmatically
Closing Workbooks Programmatically
CODING EXERCISE OpenWriteClose
Introduction To The Programmers Toolbox
Variables - Local Variables
Variables - Local Variables With A Twist
A Neat Trick To Force Variable Declaration
Variables - Module Level Variables
Variables - Project Level Variables
Bonus - Calling A Sub Stored In A DIFFERENT Workbook!
Variables - All The Techie Bits
An Introduction To Looping
Looping With For...Next
Looping With A Stepped For...Next
Looping With Do...Loop
An Introduction To Logical Testing
Looping With While...Wend
Logical Testing - If Then Else
Logical Testing - A Simple If Test
Logical Testing - A Simple If Test Using Cells
Logical Testing - Testing Multiple Criteria
Logical Testing - If Then Else Using Cells
Logical Testing - Testing If One Is True, And One Is False
Logical Testing - Testing If Either Value Is True
Maths - Doing Simple Maths In Code
Logical Testing - Select Case
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
Message Boxes - Simple Message Boxes
Manipulating The User Input With Casing
Arrays - An Introduction
InputBox - Getting User Input Using The InputBox Method
Message Boxes - Testing Which Button Was Pressed
InputBox - Getting User Input Using The InputBox Function
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
A Special Note For Office 2010 Users
Introduction To Report Automation Section
Recording The Bones Of The Code
Deconstructing The Profit By Day Code
Creating The Run Order and Data Capture Subs
Streamlining The Add New Sheet Code
Building Source Data Strings Dynamically At Runtime
Solving That Naming Problem
POWER USER - Sizing Your Charts Precisely
Changing The Chart Title (And Why We Do It Separately)
Titles, Money And Sorting
Butchering One Table, To Create Another
Deconstructing The Pivot Tables (It's Slightly Different)
Adding The Commentary - Building Strings Dynamically At Runtime
Adding The Comentary Using Data From The Sheet We're On
POWER USER - INSTR...A Very Useful Function
POWER USER - How DO You Make Specific Words Bold
INSTR And Paying Attention To Detail
Tidy Up The Title
Prettying Up Our Pie Chart
Easy As Pie (Chart)
Putting It All Together
Introduction To Web Query Section
Data Clean Up
A Simple Find And Replace
Pulling Data From The Internet - Capturing The Data For Rome
Getting To Cancun And London From Rome
Streamlining The Formulas Code
Getting Our Formulas Right
Putting It All Together
POWER USER - Displaying Messages In The Status Bar (Cool)
Intro To The Events Section
WorkBook SheetActivate
WorkBook BeforePrint
WorkBook SheetChange
WorkBook Open - Creating A Splash Screen
WorkBook Open - Calling Other Code
WorkBook Open - Creating An Auto-Back Up
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
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
Intro To Folder Creation Gizmo
Creating A New Folder With A Single Line Of Code
A Single Level Folder Structure
Folders Within Folders
Intro To The Emailing Section
The eMail Loop
Understanding The eMail Routine
Deconstructing How We Capture All The Data
Understanding The Word Routine
Intro To The Word Section
Formula Modifications With Unique Values
Efficient Sorting
Building The Text And Wrap Up
Intro To PowerPoint Section
A Run Through The PowerPoint Base Code
Setting Up The Shell Of The Code
Prettying Up The Formatting (More Lego Coding!)
Adding A Slide With A Logo And Text
Who's Presenting This...
Using Slide 1 To Create Slide 2
Adding Pivot Tables (And Another Chart)
Final Slide, And Wrap Up
Adding A Chart As A Picture
Intro To Importing Data From A Folder Full Of Files
The Folder Picker
Looping Through All Excel Files In A Folder
A More Useful Loop Through Files
The Data Grabber(er)
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
Finding A Search String in Another Workbook With Multiple Sheets
Animated Charts...With A Little Something Extra!
Protecting Specific Cells and Data Validation
Extracting Unique Tables to Unique Sheets From A Big Data Set
Saving Multiple Sheets To A Single Workbook In A Specific Folder
Dynamically Populating A Reusable Array While Looping Through A Table
Extracting Specific Data From A Big File, To A Bunch Of Little Ones
Sequential PDF Creation With Pictures
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!
File Picker, And Report Generator With Intelligent Filing
Adding A Date Stamp If A Change Has Been Made
Vlookups (Or Any Application.WorksheetFunction) Over Ranges
Get Your Next Course Now!