Related Courses
Excel Skills for 2021
£299.00
Excel Essentials: The Complete Excel
PRIVATE
Course Curriculum
| Microsoft Excel Essentials Level 1 | |||
| Intro | |||
| Level 1 Welcome | 00:02:00 | ||
| Start Here- All TheBasic | |||
| Opening Excel, And Creating A Shortcut | 00:04:00 | ||
| The Anatomy Of A Workbook | 00:15:00 | ||
| A Quick Review Of What’s Where | 00:06:00 | ||
| Always Do This First! – Save Your New Workbook | 00:02:00 | ||
| Let’s Enter Some Data | 00:02:00 | ||
| Editing Data | 00:02:00 | ||
| Ooops I Made A Mistake – Undo and Redo | 00:01:00 | ||
| Changing Appearance Of Text With Formatting – Fonts | 00:09:00 | ||
| A Quick Word On Formatting | 00:01:00 | ||
| Formatting Text – Alignment | 00:03:00 | ||
| Saving Time With AutoFilling Sequences | 00:05:00 | ||
| 1055PO~1 | 00:04:00 | ||
| Saving Time with Format Painter | 00:03:00 | ||
| Changing The Column Width | 00:04:00 | ||
| Tidy Large Titles With Merge And Centre | 00:03:00 | ||
| Doing The (Simple) Math | |||
| A Couple Of Shortcuts For Entering Data | 00:04:00 | ||
| Sums – The Old Fashioned Way! | 00:03:00 | ||
| Sums – Using Autosum | 00:06:00 | ||
| Copying Formulas | 00:03:00 | ||
| SUMming Horizontally | 00:03:00 | ||
| Basic Formulas – Subtraction | 00:02:00 | ||
| Basic Formulas – Multiplication | 00:02:00 | ||
| Basic Formulas – Division | 00:01:00 | ||
| Average Function | 00:03:00 | ||
| The Order Of Mathematical Operation | 00:07:00 | ||
| POWER USER – Evaluate Formula | 00:05:00 | ||
| Rearranging Things | |||
| Inserting New Columns And Rows | 00:06:00 | ||
| Moving Existing Columns And Rows | 00:02:00 | ||
| Cutting, Copying, Inserting And Deleting | 00:06:00 | ||
| Hiding Columns And Rows | 00:03:00 | ||
| Formulas: Learning The Clever Stuff | |||
| ROUNDing Functions | 00:06:00 | ||
| Formatting Numbers | 00:02:00 | ||
| A Primer In Building Complex Formulas | 00:03:00 | ||
| Buliding a Compex Formula | 00:04:00 | ||
| A Few more Essentials | |||
| Sorting | 00:04:00 | ||
| Wrapping Text And Soft Enter | 00:04:00 | ||
| Adding A New Worksheet | 00:01:00 | ||
| Creating A Simple Chart | 00:04:00 | ||
| Adding Borders | 00:03:00 | ||
| Customizing the Quick Access Toolbar | 00:02:00 | ||
| Simple Printing | 00:04:00 | ||
| Freezing For An Easier View | 00:04:00 | ||
| Getting Help | 00:02:00 | ||
| Filters | 00:06:00 | ||
| Highlighting Cells | 00:04:00 | ||
| Closing | 00:02:00 | ||
| Microsoft Excel Essentials Level 2 | |||
| Welcome To The Course | |||
| Welcome | 00:02:00 | ||
| Project 1 - Creating A Data Entry Screen To populate Multiple Template | |||
| Proof Of Concept | 00:05:00 | ||
| Planning Ahead | 00:02:00 | ||
| Creating Our Data Entry Screen | 00:04:00 | ||
| (Custom) Formatting Dates And Time | 00:06:00 | ||
| Simple Calculations With Time | 00:03:00 | ||
| More (Useful) Calculations With Time | 00:08:00 | ||
| It’s About Time (And Dates) | 00:08:00 | ||
| Adding With Time | 00:04:00 | ||
| Creating A Template From An Image | 00:12:00 | ||
| Importing A Template From An Existing Excel File | 00:02:00 | ||
| Converting Time To A Decimal | 00:06:00 | ||
| A Little Bit Of Simple Data Entry | 00:03:00 | ||
| Simple Conditional Formatting For A Cleaner View | 00:05:00 | ||
| Calculating Time Out Of House Using Travel Time | 00:05:00 | ||
| Simple Logical Testing And Nested Logical Testing | 00:08:00 | ||
| Building Text Strings With A Formula | 00:12:00 | ||
| Before We Move OnAccessing The Developer Ribbon | 00:01:00 | ||
| A Tick Box Exercise (Of Sorts) | 00:07:00 | ||
| Auto-populating Check Boxes | 00:12:00 | ||
| PRACTICE EXERCISE 1 – Time To Add A New Entry | 00:02:00 | ||
| Defining A Working Area, And Protecting Your Work | 00:05:00 | ||
| PRACTICE EXERCISE 2 – Set Up A Working Area, And Limit User Entry | 00:01:00 | ||
| Project 2 - Building A Database With Excel | |||
| Simple VLOOKUPs | 00:04:00 | ||
| Step 1 – Get Some Data In, And Split It | 00:04:00 | ||
| Using Data Validation To Get The Right Input | 00:04:00 | ||
| Let’s Build Our Database! | 00:06:00 | ||
| Importing Data From A Text File | 00:02:00 | ||
| Importing Data From A Word File | 00:03:00 | ||
| Pulling Data From Multiple Sources | 00:04:00 | ||
| Using OTHER Look-Ups To Look Up! | 00:05:00 | ||
| LOOKUP From A LOOKUP With No Intermediary Step | 00:02:00 | ||
| Data Arrays Don’t Have To Start At A1 | 00:03:00 | ||
| Some Common Reasons VLook-Ups Fail | 00:06:00 | ||
| One Inherent Flaw In Vlook Up | 00:01:00 | ||
| POWER USER – A Breakdown Of Looking Up Backwards | 00:07:00 | ||
| The Other Way Of Looking Up Backwards | 00:07:00 | ||
| Backwards Look-Ups In Action | 00:04:00 | ||
| POWER USER – Dealing With Inconsistencies In User Entry | 00:08:00 | ||
| POWER USER – Fuzzy VLOOKUPs | 00:04:00 | ||
| Power User – VLOOKUPs With Multiple Inputs | 00:10:00 | ||
| Power User – Looking Up Multiple Inputs Using An Array Formula | 00:05:00 | ||
| VLOOKUP’s BrotherHLOOKUP | 00:05:00 | ||
| POWER USER – The Holy Grail – How To Return Multiple Values From A Single Look Up | 00:13:00 | ||
| What To Look For When THAT Formula Didn’t Work | 00:04:00 | ||
| The Fastest Way To Modify Your Column Numbers | 00:07:00 | ||
| POWER USER – Vlook-Ups With Moving Columns | 00:03:00 | ||
| Putting It All Together | 00:05:00 | ||
| The Finishing Touch – How Many Records Did I Find | 00:05:00 | ||
| Project 3 - Named Ranges | |||
| A Simple Static Named Range Using A Single Cell | 00:04:00 | ||
| Creating A Named Range Using A Range Of Cells | 00:03:00 | ||
| Using Row Labels To Name Multiple Ranges | 00:03:00 | ||
| POWER USER – A Magic Trick Using Row And Column Labels | 00:05:00 | ||
| Horizontal Dynamic Named Ranges | 00:11:00 | ||
| POWER USER – What To Do With Dynamic Names Ranges With Titles | 00:05:00 | ||
| POWER USER – Dynamic Charts | 00:12:00 | ||
| Horizontal Dynamic Named Ranges | 00:11:00 | ||
| Project 4 - What Can I have For Dinner? | |||
| What This Project is ACTUALLY Used For! | 00:01:00 | ||
| Hyperlinking To A Different Sheet In The Same Workbook | 00:04:00 | ||
| Creating Our First Macro | 00:06:00 | ||
| Assigning A Macro To A Button | 00:04:00 | ||
| Creating A List For Our Dropdown Using A Dynamic Named Range | 00:02:00 | ||
| Using A Conditional Format To Know When A Value Is Missing | 00:05:00 | ||
| Copying Conditional Formats And Creating Our Drop-Downs | 00:03:00 | ||
| Building Our FormulaINDIRECT Function | 00:03:00 | ||
| Building Our FormulaINDIRECT Function | 00:03:00 | ||
| Building Strings For Indirect Sheet And Cell References | 00:07:00 | ||
| It’s A One Or A Zero | 00:03:00 | ||
| Working The Percentages And Adding Traffic Lights | 00:04:00 | ||
| POWER USER – The HYPERLINK Function (And Problem) | 00:03:00 | ||
| PRACTICE EXERCISE 1 – Fill In The Blanks | 00:01:00 | ||
| PRACTICE EXERCISE 2 – Pretty It Up (With A Macro) | 00:02:00 | ||
| PRACTICE EXERCISE 3 – Create A VLOOKUP Using A Built String With INDIRECT | 00:02:00 | ||
| Project 5 - Using Excel For Gantt Charts....Timelines And Project Plans! | |||
| Creating A Gantt Chart Using A Worksheet | 00:08:00 | ||
| Building The First Part Of Our Logical Test | 00:04:00 | ||
| Multiple Logical Tests At Once Using AND | 00:08:00 | ||
| Conditional FormattingWhere The Magic Happens | 00:06:00 | ||
| Gantt Charts Using The Built In Charting Tools | 00:05:00 | ||
| Gantt Charts With Different Colors For Different Criteria | 00:08:00 | ||
| Bonus Section - Just For Fun | |||
| Bonus 001 – How I Created Randomly Generated License Plate Numbers! | 00:07:00 | ||
| Level 2 SQA 01 – Calls Text Data – Or How To Return a Column Title If Value is 1 | 00:07:00 | ||
| Level 2 SQA 02 – Calls Text Data 2 – This Time Using Text! | 00:09:00 | ||
| Level 2 SQA 03 – Extracting Phone Numbers From A Cell | 00:03:00 | ||
| Level 2 SQA 04 – What Is The CHOOSE Function Really Used For | 00:15:00 | ||
| Level 2 SQA 05 – Casing And Text Functions | 00:07:00 | ||
| Level 2 SQA 06 – Dynamic Charting From A Drop Down | 00:10:00 | ||
| Level 2 SQA 08 – SUMIF With Dynamic Sum Range | 00:06:00 | ||
| Level 2 SQA 09 – VLOOKUPs With Pictures! | 00:05:00 | ||
| Level 2 SQA 11- Data Validation With Dependent Dropdowns (Dynamic Named Range Workaround) | 00:09:00 | ||
| Level 2 SQA 11- Data Validation With Dependent Dropdowns (Dynamic Named Range Workaround) | 00:09:00 | ||
| Level 2 SQA 12 – Kinda A Vlookup From 2 Drop-down Lists | 00:07:00 | ||
| Level 2 SQA 13 – Tiered Pricing | 00:09:00 | ||
| Level 2 SQA 14 – Worksheet Protection | 00:05:00 | ||
| Level 2 SQA 15 – The Middle Name Problemand Solution! | 00:09:00 | ||
| Level 2 SQA 16 – Finding Matches, And Counting Entries | 00:08:00 | ||
| Level 2 SQA 17 – Fee Calculator, or LOOKUPs That Are True, Not False | 00:06:00 | ||
| Resources | |||
| Resources : Microsoft Excel Essentials: Level 2 – Intermediate/Advanced | 00:00:00 | ||
| Microsoft Excel Essentials Level 3 | |||
| The Programmers Toolbox...The Techie Stuff, Made Easy (Honest!) | |||
| Intro To Programmers Toolbox | 00:01:00 | ||
| Variables – Local Variables | 00:07:00 | ||
| Variables – Local Variables With A Twist | 00:04:00 | ||
| Variables – Module Level Variables | 00:04:00 | ||
| Variables – Project Level Variables | 00:03:00 | ||
| Bonus – Calling A Sub Stored In A COMPLETELY DIFFERENT WORKBOOK | 00:04:00 | ||
| An Introduction To Looping | 00:01:00 | ||
| Looping With Do Loop | 00:05:00 | ||
| Looping With For Next | 00:03:00 | ||
| Looping With While Wend | 00:07:00 | ||
| Looping With A Stepped For Next | 00:02:00 | ||
| An Introduction To Logical Testing | 00:02:00 | ||
| Logical Testing – A Simple If Test | 00:07:00 | ||
| Logical Testing – A Simple If Test Using Cells | 00:05:00 | ||
| Logical Testing – If Then Else | 00:03:00 | ||
| Logical Testing – If Then Else Using Cells | 00:04:00 | ||
| Logical Testing – Testing Multiple Criteria | 00:04:00 | ||
| Logical Testing – Testing If One Is True, And One Is False | 00:02:00 | ||
| Logical Testing – Testing If Either Value Is True | 00:03:00 | ||
| Logical Testing – Select Case | 00:06:00 | ||
| Maths – Doing Simple Maths In Code | 00:03:00 | ||
| Maths – Writing Formulas To Single Cells | 00:07:00 | ||
| Maths – Writing Formulas To Ranges Of Cells | 00:05:00 | ||
| Maths – Using Excel’s Built-in Functions | 00:04:00 | ||
| Maths – Built-in Functions With Defined Ranges | 00:03:00 | ||
| InputBox – Getting User Input Using The InputBox Function | 00:05:00 | ||
| Manipulating The User Input With Casing | 00:04:00 | ||
| InputBox – Getting User Input Using The InputBox Method | 00:06:00 | ||
| Message Boxes – Simple Message Boxes | 00:04:00 | ||
| Message Boxes – Testing Which Button Was Pressed | 00:05:00 | ||
| An Introduction To Looping | 00:01:00 | ||
| Arrays – A Simple One Dimensional Static Array | 00:06:00 | ||
| Arrays – A Simple One Dimensional Dynamic Array | 00:05:00 | ||
| Arrays – The Most Efficient Way To Capture An Array | 00:05:00 | ||
| Arrays – Extracting Useful Data Based On User Input | 00:08:00 | ||
| Using An Array As A Data Source For A VLookup | 00:03:00 | ||
| Using An Array As A Data Source For A VLookup | 00:03:00 | ||
| Automating All Your Reports! | |||
| Intro To Monthly Reporting | 00:01:00 | ||
| Recording The Bones Of The Code (New) | 00:04:00 | ||
| Streamlining The Add New Sheet Code | 00:06:00 | ||
| Deconstructing The Profit By Day Code2 | 00:02:00 | ||
| Building Source Data Strings Dynamically At Runtime | 00:08:00 | ||
| Creating Run Order and Data Capture Subs | 00:04:00 | ||
| Solving That Naming Problem | 00:03:00 | ||
| POWER USER – Sizing Your Charts Precisely | 00:03:00 | ||
| Changing The Chart Title (And Why We Do It Seperately) | 00:03:00 | ||
| Deconstructing The Pivot Tables (It’s Slightly Different) | 00:08:00 | ||
| Titles, Money And Sorting | 00:05:00 | ||
| Butchering One Table, To Create Another | 00:05:00 | ||
| Adding The Commentary – Building Strings Dynamically At Runtime | 00:08:00 | ||
| Adding The Comentary Using Data From The Sheet We’re On | 00:04:00 | ||
| POWER USER – How DO You Make Specific Words Bold | 00:05:00 | ||
| POWER USER – INSTRA Very Useful Function | 00:05:00 | ||
| INSTR And Paying Attention To Detail | 00:03:00 | ||
| Tidy Up The Title | 00:03:00 | ||
| Easy As Pie (Chart) | 00:06:00 | ||
| Pretty Up Our Pie Chart | 00:03:00 | ||
| Super Web Query - The Data Is Out There...On The Internet, That Is | |||
| Pulling Data From The Internet – Capturing The Data For Rome | 00:04:00 | ||
| Getting To Cancun And London From Rome | 00:06:00 | ||
| Data Clean-Up | 00:04:00 | ||
| Streamlining The Formulas Code | 00:04:00 | ||
| A Simple Find And Replace | 00:02:00 | ||
| POWER USER – Displaying Messages In The Status Bar (Cool) | 00:03:00 | ||
| Workbook Events: You Don't Have To Run Code To Have Code Run! | |||
| Intro To The Events Section | 00:02:00 | ||
| WorkBook SheetActivate | 00:04:00 | ||
| WorkBook BeforePrint (Corrected) | 00:03:00 | ||
| WorkBook SheetChange | 00:01:00 | ||
| WorkBook Open – Creating An Auto-Back Up | 00:05:00 | ||
| WorkBook Open – Creating A Splash Screen | 00:04:00 | ||
| WorkBook Open – Calling Other Code | 00:06:00 | ||
| WorkBook BeforeClose | 00:03:00 | ||
| WorkSheet Activate – You Can’t Pick This | 00:02:00 | ||
| WorkSheet Activate – You Might Pick This | 00:03:00 | ||
| WorkSheet Change | 00:03:00 | ||
| WorkSheet Activate – Top Secret Classified Information | 00:04:00 | ||
| Worksheet Events – BONUS | 00:09:00 | ||
| User Defined Functions...What To Do If The Function You Need Isn't In Excel! | |||
| Using A UDF To Return Information | 00:02:00 | ||
| Creating A Countdown Timer With A UDF | 00:09:00 | ||
| A Custom UDF For Calculating Volume Discount | 00:04:00 | ||
| A UDF For Getting All Your Sheet Names | 00:03:00 | ||
| Calling A UDF From A Different Workbook | 00:02:00 | ||
| SuperCountIf and SuperSumIf UDF’s | 00:10:00 | ||
| Bonus Section: Controlling Windows - Folder Creation Gizmo | |||
| Intro To Folder Creation Gizmo | 00:02:00 | ||
| Creating A New Folder With A Single Line Of Code | 00:01:00 | ||
| A Single Level Folder Structure | 00:05:00 | ||
| Folders Within Folders | 00:06:00 | ||
| Bonus Section: eMail Automation...Why WRITE emails! | |||
| Intro To The Emailing Section | 00:03:00 | ||
| Understanding The eMail Routine | 00:05:00 | ||
| Capturing All The Data | 00:10:00 | ||
| The eMail Loop | 00:05:00 | ||
| Bonus Section: Word Automation - Controlling Word From Excel | |||
| Intro To The Word Section | 00:01:00 | ||
| Understanding The Word Routine | 00:02:00 | ||
| Deconstructing How We Capture All The Data | 00:03:00 | ||
| Efficient Sorting | 00:03:00 | ||
| Building The Text And Wrap Up | 00:05:00 | ||
| Bonus Section: PowerPoint Automation - Create Your Presentation In Seconds! | |||
| Intro To PowerPoint Section | 00:01:00 | ||
| A Run Through The PowerPoint Base Code | 00:09:00 | ||
| Setting Up The Shell Of The Code | 00:03:00 | ||
| Who’s Presenting This | 00:03:00 | ||
| Adding A Slide With A Logo And Text | 00:08:00 | ||
| Prettying Up The Formatting (More Lego Coding!) | 00:02:00 | ||
| Using Slide 1 To Create Slide 2 | 00:05:00 | ||
| Adding Pivot Tables (And Another Chart) | 00:09:00 | ||
| Final Slide, And Wrap Up | 00:02:00 | ||
| Importing Specific Data From Multiple Files | |||
| Mass Getter(er) Intro | 00:03:00 | ||
| A More Useful Loop Through Files | 00:03:00 | ||
| The Data Grabber(er) | 00:13:00 | ||
