Excel Formulas Made Easy – Learn more than 100 Formulas

S$80.00
Take This Course
Excel Formulas Made Easy – Learn more than 100 Formulas

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.

Curriculum

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

Course Features

  • Lectures
    Fatal error: Uncaught Error: Call to undefined method LP_Course::get_lessons() in /home2/waf6oo0adwwo/public_html/wp-content/themes/eduma/inc/learnpress-v2-functions.php:624 Stack trace: #0 /home2/waf6oo0adwwo/public_html/wp-content/themes/eduma/learnpress-v2/single-course/content-landing.php(64): thim_course_info() #1 /home2/waf6oo0adwwo/public_html/wp-content/plugins/learnpress/inc/lp-template-functions.php(2492): include('/home2/waf6oo0a...') #2 /home2/waf6oo0adwwo/public_html/wp-content/themes/eduma/learnpress-v2/content-single-course.php(65): learn_press_get_template('single-course/c...', Array) #3 /home2/waf6oo0adwwo/public_html/wp-content/plugins/learnpress/inc/lp-template-functions.php(2492): include('/home2/waf6oo0a...') #4 /home2/waf6oo0adwwo/public_html/wp-content/plugins/learnpress/inc/lp-template-functions.php(1563): learn_press_get_template('content-single-...') #5 /home2/waf6oo0adwwo/public_html/wp-includes/class-wp-hook.php(286): learn_press_content_single_course('') #6 /home2/waf6oo0adwwo/public_html/wp-i in /home2/waf6oo0adwwo/public_html/wp-content/themes/eduma/inc/learnpress-v2-functions.php on line 624