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|
Introduction, Meet the Instructor, What to Expect
Course Files – Download these files to follow along
|Section 2: Formulas in a Nutshell|
Getting Started with Formulas
Order of Calculation
Introduction to Functions – SUM, COUNT, AVERAGE, MAX and MIN
|Section 3: Referencing Cells, Sheets and Workbooks in a Formula|
Formula References to other Sheets and Workbooks
Using Defined Names in a Workbook
Formatting a Range as a Table
|Section 4: Using Logical Functions|
The IF Function
Nesting Multiple IF Functions
Using the AND and OR Functions to Test Multiple Conditions
Taking Alternative Action to Error Values
|Section 5: Formulas for Summing Values|
Create a Running Total
Summing Values that Meet Specific Criteria
Summing Only the Negative Values
Summing Values that Meet Multiple Criterion
|Section 6: Counting Formulas|
Counting the Blank Cells in a Range
Counting the Nonblank Cells in a Range
Counting Cells that Meet Specific Criteria
Counting Cells that Meet Multiple Criterion
Counting the Unique Values in a Range
|Section 7: Calculating Averages|
Finding the Mean Average
Calculate the Average of Values that Meet Specific Criteria
Calculating the Average Excluding Zeros
Calculating the Average of Values that Meet Multiple Criterion
Finding the Most Frequently Occurring Value
Calculating the Median (Middle) Value in a Range
Calculating the Trimmed Mean
|Section 8: Specialised Functions for Summing and Counting|
The SUBTOTAL Function
The AGGREGATE Function
The Awesome SUMPRODUCT Function
Summing ‘Every Nth’ Row
|Section 9: Working with Text|
Changing the Case of Text
Removing Leading and Trailing Spaces
Joining Text from Different Cells
Extracting Text from the Start, Middle and End of a String
Extracting Text from Irregular Strings
Extracting Text from the Nth Occurrence of a Character
Counting the Number of Words in a Cell
|Section 10: Date Formulas|
Calculating the Current Date and Date & Time
Calculating the Number of Days Between Two Dates
Calculating the Number of Workdays Between Two Dates
Calculating the Difference Between Two Dates in Years and Months
Adding Months to a Date – EDATE and EOMONTH
Adding Working Days to a Date
Highlighting the Sundays in a List
The DATE Function
|Section 11: Time Formulas|
Calculating the Difference Between Two Times
Calculating the Difference Between Times on Different Dates
Summing Times that Exceed 24 Hours
Converting Decimal Hours and Minutes to Time
|Section 12: Lookup Formulas|
VLOOKUP Function for an Exact Match
Using VLOOKUP to Return the Closest Match
The HLOOKUP Function
Two Way Lookup using VLOOKUP and MATCH
The Versatile INDEX and MATCH Functions
Hiding the #N/A Error
|Section 13: Advanced Lookup Formulas|
Create a Multiple Condition Lookup Formula
Finding the Cell Address of a Value
Case Sensitive Lookup Formula
Looking up a Picture in a List
Using INDIRECT – VLOOKUP with Conditional Lookup Table
Using INDIRECT – Create Dependent Drop Down Lists
HYPERLINK Function – Conditional Hyperlinks
|Section 14: Solving Formula Errors|
Formula Errors Explained
Common Formula Problems
Validating Data Entry
Hiding Formula Errors
Protecting the Formulas on a Worksheet
Viewing Formulas on a Worksheet
Tracing Precedents and Dependents
Evaluating a Formula
Handling Circular References
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.