Excel Essentials – Level 1, 2 & 3
Learning Outcome
- Learn how to build spreadsheets from scratch, and understand how they work
- Understand how Excel makes it easy to comb through data sets and sort everything into a clean and easy to read spreadsheet
- Explore how to create a fully functioning relational database, and build Gantt charts for project planning using Excel
- Broaden your understanding of coding, and the thought process behind it
- Discover how to create a simple data entry screen and auto-populate complex templates
- Learn to program in VBA from scratch and generate Word documents using VBA code from Excel
- Create emails and PowerPoint presentations in minutes, automatically
- Learn to use keyboard shortcuts to improve your spreadsheet building skills dramatically
- Master the functions and formulas to save time and build your modelling skills
Description
Excel is an incredibly powerful tool as well as one of the most used software in today’s digital world since it offers huge flexibility to its users. In this Excel Essentials course, you will discover essential tips, plenty of helpful tricks, shortcuts, functions and formulas of Excel. You will learn the things you should absolutely know to work with Excel data like a professional, whether you’re a total newbie or an established expert.
This course is led by an experienced instructor, who will help you in mastering all the basic to advanced Excel skills in no time, and you’ll be on the path to becoming a total Excel whiz.
Course Design
The course is delivered through our online learning platform, accessible through any internet-connected device. There are no formal deadlines or teaching schedules, meaning you are free to study the course at your own pace.
You are taught through a combination of
- Video lessons
- Online study materials
Who is This Excel Essentials – Level 1, 2 & 3 for?
This Excel Essentials – Level 1, 2 & 3 course is ideal for complete beginners and moderate users, including those who would like to gain a thorough understanding of Excel or expand their understanding of all the features and tools Excel can offer. There are no learning requirements or formal degrees necessary to begin this course, and it can be studied on a full-time or part-time basis.
How is the course assessed?
To successfully complete the course you must pass an automated, multiple-choice assessment. The assessment is delivered through our online learning platform. You will receive the results of your assessment immediately upon completion.
Will I Receive a Certificate of Completion?
Upon successful completion, you will qualify for the UK and internationally-recognised certificate and you can choose to make your achievement formal by obtaining your PDF Certificate at a cost of £9 and Hardcopy Certificate for £15.
Requirements
The Excel Essentials – Level 1, 2 & 3 is open to all, with no formal entry requirements. All you need is a passion for learning, a good understanding of the English language, numeracy and IT, and to be over the age of 16.
Career Path
Upon successful completion, learners will be equipped with all the essential knowledge and skills to work with Excel and gain more and more confidence in performing professional financial analysis and become Excel power users to land jobs as accountants, financial analysts, or sales managers.
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 | ||
| NEW! – POWER USER – Adding Your Own Lists To Autofill | 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:02:00 | ||
| Closing | 00:02:00 | ||
| Additional Resources | |||
| 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 | ||
| POWER USER – 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:14: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:10:00 | ||
| The Finishing Touch – How Many Records Did I Find | 00:04: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 | ||
| POWER USER – Dynamic Named Ranges | 00:08:00 | ||
| POWER USER – What To Do With Dynamic Names Ranges With Titles | 00:05:00 | ||
| 3035 – POWER USER – Dynamic Charts | 00:10:00 | ||
| 3040 – Horizontal Dynamic Named Ranges For Charts | 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 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 | |||
| How I Created Randomly Generated License Plate Numbers! | 00:07:00 | ||
| Calls Text Data – Or How To Return a Column Title If Value is 1 | 00:07:00 | ||
| Calls Text Data 2 – This Time Using Text! | 00:09:00 | ||
| Extracting Phone Numbers From A Cell | 00:03:00 | ||
| What Is The CHOOSE Function Really Used For | 00:15:00 | ||
| Casing And Text Functions | 00:07:00 | ||
| Dynamic Charting From A Drop Down | 00:10:00 | ||
| SUMIF With Dynamic Sum Range | 00:06:00 | ||
| VLOOKUPs With Pictures! | 00:05:00 | ||
| Data Validation With Dependent Dropdowns | 00:04:00 | ||
| Data Validation With Dependent Dropdowns (Dynamic Named Range Workaround) | 00:09:00 | ||
| Kinda A Vlookup From 2 Drop-down Lists | 00:07:00 | ||
| Tiered Pricing | 00:09:00 | ||
| Worksheet Protection | 00:05:00 | ||
| The Middle Name Problemand Solution! | 00:09:00 | ||
| Finding Matches, And Counting Entries | 00:08:00 | ||
| Fee Calculator, or LOOKUPs That Are True, Not False | 00:06:00 | ||
| Microsoft Excel Essentials Level 3 | |||
| Importing Specific Data From Multiple Files | |||
| Why Should I Learn How To Code | 00:10:00 | ||
| The 15 Golden Rules Of Coding | 00:06:00 | ||
| Introducing The Visual Basic Editor, & Recording Our First Macro | 00:11:00 | ||
| Saving Macro-enabled Workbooks, And Security Settings | 00:03:00 | ||
| Moving Code Around | 00:05:00 | ||
| Stepping Out Well, In Actually – Debugging Made Easy | 00:06:00 | ||
| With And End With | 00:13:00 | ||
| Streamlining You Code, Or, Get Rid Of What You Don’t Need | 00:08:00 | ||
| Combining Your Code | 00:06:00 | ||
| A Little Privacy Please | 00:03:00 | ||
| Keyboard Shortcuts, And Why I Don’t Use Them | 00:02:00 | ||
| Why You Can’t Get By With Just Recording Macros | 00:11:00 | ||
Certificate of Achievement
CPD Accredited Certification
You will be eligible for CPD Accredited Certificates after completing this Course successfully.
Certification is available –
Certification is available –
- In PDF for £2.99
- Hard copy for £8.99
£
Access
Level
Number of Units
Number of Quizzes
Duration