Excel Formulas Made Easy - Learn More Than 100 Formulas

Learn Excel formulas with this comprehensive course taking you from the fundamentals to writing advanced formulas

What you'll learn

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

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.

Description

Excel Formulas can seem daunting and even scary at first. In this course Excel formulas are made easy as you learn all the essential skills to writing formulas.Master Excel Formulas Easily with this Comprehensive CourseUnderstand Excel formulas inside out.Learn over 60 different Excel functions.Write complex formulas with ease.Recognise formula errors and fix common problems.Hear What Some of our Students have Been Saying"Great instructor and excellent way of explaining.""Brilliant course! Finally a course that leaves you understanding everything without needing to review a dozen times. I have reviewed and learned much more from this easy to follow course due to the step by step tutorials and its clear, engaging delivery by an excellent and knowledgeable instructor. It has helped me understand more formulas in excel that can help me with my business. Many thanks to all involved and keep up the great work.""straight to the point, clear explanation and demo, use common sense language to interpret the formula, and simple and easy illustrative example."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 OverviewThis 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 80 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.

Overview

Section 1: Introduction

Lecture 1 Introduction, Meet the Instructor, What to Expect

Lecture 2 Course Files - Download these files to follow along

Section 2: Formulas in a Nutshell

Lecture 3 Getting Started with Formulas

Lecture 4 Order of Calculation

Lecture 5 Calculating Percentages

Lecture 6 Introduction to Functions - SUM, COUNT, AVERAGE, MAX and MIN

Section 3: Referencing Cells, Sheets and Workbooks in a Formula

Lecture 7 Absolute References

Lecture 8 Mixed References

Lecture 9 Formula References to other Sheets and Workbooks

Lecture 10 Using Defined Names in a Workbook

Lecture 11 Formatting a Range as a Table

Section 4: Using Logical Functions

Lecture 12 The IF Function

Lecture 13 Nesting Multiple IF Functions

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

Lecture 15 Taking Alternative Action to Error Values

Section 5: Formulas for Summing Values

Lecture 16 Create a Running Total

Lecture 17 Summing Values that Meet Specific Criteria

Lecture 18 Summing Only the Negative Values

Lecture 19 Summing Values that Meet Multiple Criterion

Section 6: Counting Formulas

Lecture 20 Counting the Blank Cells in a Range

Lecture 21 Counting the Nonblank Cells in a Range

Lecture 22 Counting Cells that Meet Specific Criteria

Lecture 23 Counting Cells that Meet Multiple Criterion

Lecture 24 Counting the Unique Values in a Range

Section 7: Calculating Averages

Lecture 25 Finding the Mean Average

Lecture 26 Calculate the Average of Values that Meet Specific Criteria

Lecture 27 Calculating the Average Excluding Zeros

Lecture 28 Calculating the Average of Values that Meet Multiple Criterion

Lecture 29 Finding the Most Frequently Occurring Value

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

Lecture 31 Calculating the Trimmed Mean

Section 8: Specialised Functions for Summing and Counting

Lecture 32 The SUBTOTAL Function

Lecture 33 The AGGREGATE Function

Lecture 34 The Awesome SUMPRODUCT Function

Lecture 35 Summing 'Every Nth' Row

Section 9: Working with Text

Lecture 36 Changing the Case of Text

Lecture 37 Removing Leading and Trailing Spaces

Lecture 38 Joining Text from Different Cells

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

Lecture 40 Extracting Text from Irregular Strings

Lecture 41 Extracting Text from the Nth Occurrence of a Character

Lecture 42 Counting the Number of Words in a Cell

Section 10: Date Formulas

Lecture 43 Calculating the Current Date and Date & Time

Lecture 44 Calculating the Number of Days Between Two Dates

Lecture 45 Calculating the Number of Workdays Between Two Dates

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

Lecture 47 Adding Months to a Date - EDATE and EOMONTH

Lecture 48 Adding Working Days to a Date

Lecture 49 Highlighting the Sundays in a List

Lecture 50 The DATE Function

Section 11: Time Formulas

Lecture 51 Calculating the Difference Between Two Times

Lecture 52 Calculating the Difference Between Times on Different Dates

Lecture 53 Summing Times that Exceed 24 Hours

Lecture 54 Converting Decimal Hours and Minutes to Time

Section 12: Dynamic Array Functions - Formulas Have Changed Forever

Lecture 55 Introduction to Dynamic Arrays

Lecture 56 The UNIQUE Function

Lecture 57 The SORT Function - Sort Formula Results Automatically

Lecture 58 The SORTBY Function - Sort by Any Column

Lecture 59 The FILTER Function - Lookup and Return Multiple Results

Lecture 60 The SEQUENCE Function

Section 13: Lookup Formulas

Lecture 61 VLOOKUP Function for an Exact Match

Lecture 62 Using VLOOKUP to Return the Closest Match

Lecture 63 The HLOOKUP Function

Lecture 64 Two Way Lookup using VLOOKUP and MATCH

Lecture 65 The Versatile INDEX and MATCH Functions

Lecture 66 Hiding the #N/A Error

Lecture 67 Return Non-Adjacent Columns in an Array with INDEX and MATCH

Lecture 68 XLOOKUP Function - The Successor to VLOOKUP

Lecture 69 Multi-Column Lookup with XLOOKUP

Lecture 70 Two-Way Lookup with XLOOKUP

Section 14: Advanced Lookup Formulas

Lecture 71 Create a Multiple Condition Lookup Formula

Lecture 72 Finding the Cell Address of a Value

Lecture 73 Case Sensitive Lookup Formula

Lecture 74 Looking up a Picture in a List

Lecture 75 Using INDIRECT - VLOOKUP with Conditional Lookup Table

Lecture 76 Using INDIRECT - Create Dependent Drop Down Lists

Lecture 77 HYPERLINK Function - Conditional Hyperlinks

Section 15: Solving Formula Errors

Lecture 78 Formula Errors Explained

Lecture 79 Common Formula Problems

Lecture 80 Validating Data Entry

Lecture 81 Hiding Formula Errors

Lecture 82 Protecting the Formulas on a Worksheet

Lecture 83 Viewing Formulas on a Worksheet

Lecture 84 Tracing Precedents and Dependents

Lecture 85 Evaluating a Formula

Lecture 86 Handling Circular References

Section 16: New Functions in Excel 2016

Lecture 87 Use TEXTJOIN to Easily Concatenate Multiple Strings

Lecture 88 CONCAT is Here, Say Goodbye (Almost!) to CONCATENATE

Lecture 89 Easily Write Nested IF's with the New IFS Function

Lecture 90 Test a List of Values with SWITCH, another Logical Function

Lecture 91 Return the Max Value that Meets Multiple Criterion with MAXIFS

Lecture 92 Return the Min Value that Meets Multiple Criterion with MINIFS

Section 17: Formula Challenges - Test your Skills with Real World Scenarios

Lecture 93 Excel Formula Challenge 1

Lecture 94 Excel Formula Challenge 2 - Calculate Postage cost

Section 18: Answers for the Formula Challenges

Lecture 95 Excel Formula Challenge 1 Answer

Lecture 96 Excel Formula Challenge 2 Answer - Calculate Postage Cost

Section 19: More Learning

Lecture 97 Bonus Lecture: Download your FREE eBook on 40 Incredible Excel Tricks

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.