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!
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
Tidy Large Titles With Merge And Centre
Entering Data - A Couple Of Shortcuts
Sums - The Old Fashioned Way!
Basic Formulas - Subtraction
Basic Formulas - Multiplication
Basic Formulas - Division
The Order Of Mathematical Operation
POWER USER - Evaluate Formula
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!)
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
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
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
3035 - POWER USER - Dynamic Charts
3040 - Horizontal Dynamic Named Ranges For 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 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
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
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
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
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...
The Middle Name Problem and Solution!
Finding Matches And Counting Entries
Fee Calculator or LOOKUPs That Are True Not False
Drop Down List Shifting Ranges
Dynamic Calculations From Another Sheet
Introducing The Visual Basic Editor & Recording Our First Macro
Saving Macro-Enabled Workbooks And Security Settings
Stepping Out. Well In Actually! - Debugging Made Easy :)
Streamlining You Code Or Get Rid Of What You Don't Need
Keyboard Shortcuts And Why I Don't Use Them
Why You Can't Get By With Just Recording Macros
Introduction To The Coding Section
Getting All The Code For This Section
Changing Your VBE Settings
Objects Methods And Properties
Understanding The Hierarchy
The CurrentRegion Property
Calling A Sheet By Its VB Name
Getting Around The Workbooks
The Value Property - Writing Data
The Value Property - Reading And Writing Data
CODING EXERCISE: The Rainbow
The Row and Column Properties
Capturing The Column Letter
Even More Useful Properties
Opening Another Workbook Programmatically
Closing Workbooks Programmatically
CODING EXERCISE: Open/Write/Close
Let's See How You're Doing...
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 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 - 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
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)
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
Prettying Up Our Pie Chart
Introduction To Web Query Section
Pulling Data From The Internet - Capturing The Data For Rome
Getting To Cancun And London From Rome
A Simple Find And Replace
Getting Our Formulas Right
Streamlining The Formulas Code
POWER USER - Displaying Messages In The Status Bar (Cool)
Intro To The Events Section
WorkBook Open - Creating An Auto-Back Up
WorkBook Open - Creating A Splash Screen
WorkBook Open - Calling Other Code
WorkSheet Activate - You Can't Pick This!
WorkSheet Activate - You Might Pick This!
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
The SuperCountIf And SuperSumIf UDF's
Intro To The Word Section
Understanding The Word Routine
Deconstructing How We Capture All The Data
Formula Modifications With Unique Values
Building The Text And Wrap Up
Intro To PowerPoint Section
A Run Through The PowerPoint Base Code
Setting Up The Shell Of The Code
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)
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