Course Description
Excel Formulas can seem daunting and even scary at first when beginning. On this course Excel formulas are made easy as you learn all the essential skills to writing formulas.
Master Excel Formulas Easily with this Comprehensive Course
Understand Excel formulas inside out.
Learn over 55 different Excel functions.
Write complex formulas with ease.
Recognise formula errors and fix common problems.
Learn Everything You Need to Write Formulas with Ease.
Formulas are what drive Excel. They are the muscles allowing users to manipulate, lookup, aggregate and test data with ease.
Learning how to write formulas is an important skill for any Excel user looking to develop their talents.
Content and Overview
This course was designed for anybody who needs to be able to confidently work with formulas in Excel. It is suitable both for complete beginners who want to start from the basics, and for those that have some experience but want to take things further.
The course is broken into different sections. It starts with building an understanding of the different elements of a formula, what the various symbols mean, some basic functions and different techniques for referencing cells.
Each section then focuses on a specific function category such as date or lookup functions. It starts to take things further by tackling mini projects and looking at more advanced formulas.
In over 75 lectures this course will teach you all the necessary skills to write advanced formulas with ease, and make those formula demons a thing of the past.
Working files are provided so that you can follow along and practice. You can also get assistance from myself with any queries you may have.
What are the requirements?
Only a basic use of Excel is required as this course starts from scratch in understanding and creating formulas in Excel.
Excel 2013 is used on the course, but the lessons apply to any version of Excel. When a formula is new to a specific version of Excel this will be stated.
What am I going to get from this course?
Write advanced formulas for summing and counting values in a list.
Use lookup functions to fetch values on a spreadsheet.
Perform calculations with dates and times
Use formulas to clean and prepare data for analysis.
Identify and solve formula errors
What is the target audience?
Newbies to formulas in Excel who need to learn the formulas on their spreadsheets.
Excel users who can write formulas but want to take their skills further and learn some advanced Excel formula techniques.
Section 1: Introduction  

Lecture 1 
Introduction, Meet the Instructor, What to Expect

03:06  
Lecture 2 
Course Files – Download these files to follow along

Article  
Section 2: Formulas in a Nutshell  
Lecture 3 
Getting Started with Formulas

09:37  
Lecture 4 
Order of Calculation

06:02  
Lecture 5 
Calculating Percentages

06:26  
Lecture 6 
Introduction to Functions – SUM, COUNT, AVERAGE, MAX and MIN

10:50  
Section 3: Referencing Cells, Sheets and Workbooks in a Formula  
Lecture 7 
Absolute References

03:54  
Lecture 8 
Mixed References

03:23  
Lecture 9 
Formula References to other Sheets and Workbooks

06:54  
Lecture 10 
Using Defined Names in a Workbook

08:54  
Lecture 11 
Formatting a Range as a Table

08:55  
Section 4: Using Logical Functions  
Lecture 12 
The IF Function

04:47  
Lecture 13 
Nesting Multiple IF Functions

05:13  
Lecture 14 
Using the AND and OR Functions to Test Multiple Conditions

09:41  
Lecture 15 
Taking Alternative Action to Error Values

04:46  
Section 5: Formulas for Summing Values  
Lecture 16 
Create a Running Total

02:53  
Lecture 17 
Summing Values that Meet Specific Criteria

04:19  
Lecture 18 
Summing Only the Negative Values

02:44  
Lecture 19 
Summing Values that Meet Multiple Criterion

04:56  
Section 6: Counting Formulas  
Lecture 20 
Counting the Blank Cells in a Range

01:39  
Lecture 21 
Counting the Nonblank Cells in a Range

01:49  
Lecture 22 
Counting Cells that Meet Specific Criteria

06:24  
Lecture 23 
Counting Cells that Meet Multiple Criterion

04:02  
Lecture 24 
Counting the Unique Values in a Range

04:40  
Section 7: Calculating Averages  
Lecture 25 
Finding the Mean Average

01:43  
Lecture 26 
Calculate the Average of Values that Meet Specific Criteria

03:07  
Lecture 27 
Calculating the Average Excluding Zeros

02:52  
Lecture 28 
Calculating the Average of Values that Meet Multiple Criterion

03:21  
Lecture 29 
Finding the Most Frequently Occurring Value

04:14  
Lecture 30 
Calculating the Median (Middle) Value in a Range

03:09  
Lecture 31 
Calculating the Trimmed Mean

02:41  
Section 8: Specialised Functions for Summing and Counting  
Lecture 32 
The SUBTOTAL Function

04:59  
Lecture 33 
The AGGREGATE Function

04:42  
Lecture 34 
The Awesome SUMPRODUCT Function

07:31  
Lecture 35 
Summing ‘Every Nth’ Row

06:49  
Section 9: Working with Text  
Lecture 36 
Changing the Case of Text

02:42  
Lecture 37 
Removing Leading and Trailing Spaces

03:05  
Lecture 38 
Joining Text from Different Cells

04:11  
Lecture 39 
Extracting Text from the Start, Middle and End of a String

07:02  
Lecture 40 
Extracting Text from Irregular Strings

10:06  
Lecture 41 
Extracting Text from the Nth Occurrence of a Character

07:50  
Lecture 42 
Counting the Number of Words in a Cell

04:45  
Section 10: Date Formulas  
Lecture 43 
Calculating the Current Date and Date & Time

03:31  
Lecture 44 
Calculating the Number of Days Between Two Dates

03:23  
Lecture 45 
Calculating the Number of Workdays Between Two Dates

06:10  
Lecture 46 
Calculating the Difference Between Two Dates in Years and Months

07:24  
Lecture 47 
Adding Months to a Date – EDATE and EOMONTH

04:40  
Lecture 48 
Adding Working Days to a Date

05:36  
Lecture 49 
Highlighting the Sundays in a List

04:41  
Lecture 50 
The DATE Function

04:25  
Section 11: Time Formulas  
Lecture 51 
Calculating the Difference Between Two Times

02:04  
Lecture 52 
Calculating the Difference Between Times on Different Dates

02:15  
Lecture 53 
Summing Times that Exceed 24 Hours

02:23  
Lecture 54 
Converting Decimal Hours and Minutes to Time

03:43  
Section 12: Lookup Formulas  
Lecture 55 
VLOOKUP Function for an Exact Match

09:25  
Lecture 56 
Using VLOOKUP to Return the Closest Match

06:08  
Lecture 57 
The HLOOKUP Function

03:32  
Lecture 58 
Two Way Lookup using VLOOKUP and MATCH

06:28  
Lecture 59 
The Versatile INDEX and MATCH Functions

08:35  
Lecture 60 
Hiding the #N/A Error

05:37  
Section 13: Advanced Lookup Formulas  
Lecture 61 
Create a Multiple Condition Lookup Formula

06:25  
Lecture 62 
Finding the Cell Address of a Value

07:32  
Lecture 63 
Case Sensitive Lookup Formula

06:06  
Lecture 64 
Looking up a Picture in a List

05:11  
Lecture 65 
Using INDIRECT – VLOOKUP with Conditional Lookup Table

06:28  
Lecture 66 
Using INDIRECT – Create Dependent Drop Down Lists

03:25  
Lecture 67 
HYPERLINK Function – Conditional Hyperlinks

07:49  
Section 14: Solving Formula Errors  
Lecture 68 
Formula Errors Explained

Article  
Lecture 69 
Common Formula Problems

Article  
Lecture 70 
Validating Data Entry

08:35  
Lecture 71 
Hiding Formula Errors

04:17  
Lecture 72 
Protecting the Formulas on a Worksheet

06:45  
Lecture 73 
Viewing Formulas on a Worksheet

02:39  
Lecture 74 
Tracing Precedents and Dependents

06:31  
Lecture 75 
Evaluating a Formula

06:22  
Lecture 76 
Handling Circular References

06:14 
Instructor Biography
Alan Murray, Founder of Computergaga
Alan Murray is a Microsoft Office trainer and consultant. He has been training and consulting for the past 15 years for businesses around the world.
He is the founder of Computergaga and regularly writes on the Computergaga blog to share tips, tutorials and templates.
Alan uses a fun and relaxed style of training that gets to the point, and uses real world practical examples uncovered from his experience of training and developing software for businesses.
Files are provided to follow along and exercises used to recap on topics covered. Alan is always eager to help and will be there for you when needed. He will do his best to answer any question or query related to his courses within 48 hours.