Excel Formulas Made Easy - Learn more than 100 Formulas
4.8 (27 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
442 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Excel Formulas Made Easy - Learn more than 100 Formulas to your Wishlist.

Add to Wishlist

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
4.8 (27 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
442 students enrolled
Created by Alan Murray
Last updated 1/2017
English
Current price: $10 Original price: $70 Discount: 86% off
1 day left at this price!
30-Day Money-Back Guarantee
Includes:
  • 7 hours on-demand video
  • 5 Articles
  • 2 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Have a coupon?
What Will I 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
View Curriculum
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

Last Updated in January 2017

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 Course

  • Understand 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 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 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.

Who 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.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 84 Lectures Collapse All 84 Lectures 07:06:51
+
Introduction
2 Lectures 03:11

This first lesson includes;

  • An introduction from your instructor, Alan Murray.
  • What to expect from the course.
  • How you can participate to get the most out of the course.
Preview 03:06

Course Files - Download these files to follow along
00:05
+
Formulas in a Nutshell
4 Lectures 32:55

This lesson is for formula beginners. It explains from the start how a formula is constructed and some 'need to know' formula facts.

The lessons covers;

  • How to write a formula to perform basic arithmetic operations such as add, subtract, multiple and divide.
  • Different operators (symbols) which you may find in a formula.
  • How to edit an existing formula.
Preview 09:37

When entering a formula in Excel you need to be aware of the order of calculation, and how to control it.

This lesson covers;

  • What the order of calculation is and how it could affect you.
  • How to determine the order using parentheses.
Preview 06:02

A common requirement of writing formulas in Excel is to calculate percentages. Examples include to calculate customer discounts, VAT, inflation and salesperson commission.

This lesson covers;

  • Calculating the percentage of a value i.e. 15% of 500.
  • Increasing or decreasing a value by a specific percentage.
  • Calculating the value as a percentage of another value i.e. 70 as a percentage of 500.
  • Finding the value as a percentage increase or decrease of another value i.e. percentage increase of 600 compared to 500.
Preview 06:26

A function is a predefined formula in Excel, and Excel contains over 450 of them. Most formulas you write will include the use of functions so you need to be confident in using them.

This lesson includes;

  • Viewing the different categories of function in Excel.
  • Understanding the basic syntax (anatomy) of a function.
  • Using the 5 aggregate functions of Excel (SUM, COUNT, AVERAGE, MAX and MIN).
  • Different approaches to writing a function.
Introduction to Functions - SUM, COUNT, AVERAGE, MAX and MIN
10:50
+
Referencing Cells, Sheets and Workbooks in a Formula
5 Lectures 32:00

An absolute cell reference is fully locked. When a formula is copied this reference does not change. It is often referred to as a fixed reference, or anchored reference.

This lesson covers;

  • Creating an absolute cell reference by entering dollar signs.
  • Using a keyboard shortcut to make creating absolute references easier.
Preview 03:54

A mixed reference is only partially fixed. There are two types of mixed reference; the row absolute and the column absolute.

This lesson covers;

  • Understanding why and when to use mixed references.
  • Applying mixed references y using the dollar sign the fix the required column or row.
Mixed References
03:23

Often your formula will need to reference cells on another sheet or entirely different workbook. You can create these references in the same way that you do when referencing cells on the same sheet.

However, it is a useful skills to be able to write and understand these references for when you use other user's spreadsheets.

This lesson covers;

  • How to reference a cell on a different worksheet.
  • Handling spaces in a sheet name.
  • Referencing cells on a different workbook.
  • Viewing references to other open or closed workbooks.
Formula References to other Sheets and Workbooks
06:54

The lesson explores the use of Defined Names on a spreadsheet. This practice can simplify the writing and understanding of formulas.

This lesson covers;

  • Advantages to using named ranges.
  • Creating Defined Names and the rules on naming ranges.
  • Using named ranges for navigation of a large workbook.
  • Using named ranges in a formula.
Using Defined Names in a Workbook
08:54

A range of cells can be converted into a table to make common Excel tasks easier. This technique is best applied to a large list such as a list of sales, employees, inventory or expenses.

What makes formatting a range as a table so useful with formulas, is that they provide dynamic and structured references. This can make formulas much easier to create and control over the long term.

This lesson covers;

  • The benefits of using a range formatted as a table.
  • Creating a table.
  • A demonstration of the tables dynamic nature.
  • How this affects the look of your formulas.
  • Using structured references in your formulas.
Formatting a Range as a Table
08:55
+
Using Logical Functions
4 Lectures 24:27

Knowing how to use logical functions is a must if you want to be come proficient with formulas in Excel. The main logical function is IF.

This lesson covers;

  • Understanding when and why to use the IF function.
  • Calculating discounts dependent upon a condition.
Preview 04:47

An IF function can perform two possible actions upon the result of the logical test. These are known as value if true and value if false. If you need more alternative actions, then you may need more IF's.

This lesson covers;

  • Using 2 IF functions to perform 3 possible actions upon the test result.
  • An understanding of how to structure multiple IF functions.
Nesting Multiple IF Functions
05:13

An IF function can only perform one logical test, or condition. If you need to perform multiple conditions then you may need the AND or OR function. These are commonly combined with the IF function to perform actions based on multiple conditions.

This lesson covers;

  • Understanding the difference between the AND and OR functions and what they do.
  • The syntax of AND and OR.
  • How to use these functions with IF.
Using the AND and OR Functions to Test Multiple Conditions
09:41

Formulas will produce error values for many reasons. At times it may be that a value is not available to it yet.

If the error value does not indicate a problem with the formula and require solving, you may wish to perform an alternative action.

This lesson covers;

  • An example of when an error value is not necessarily a bad news.
  • Using the IFERROR function to handle the error value.
Taking Alternative Action to Error Values
04:46
+
Formulas for Summing Values
4 Lectures 14:52

Create a running total to sum the year to date sales. This cumulative total will add the current months total onto the previous total.

This lesson covers;

  • Create a running total for year to date sales.
  • Using the SUM function with a partially absolute range.
Create a Running Total
02:53

Often the values to be summed will be dependent upon a specific condition. For example, you may wish to sum the sales totals only for a specific product, or for a specific region.

This lesson covers;

  • Understanding how to use the SUMIF function.
  • Entering text as the criteria for the sum.
  • Using a cell reference as the criteria for the sum.
Summing Values that Meet Specific Criteria
04:19

You can also perform tests against values in the criteria for the SUMIF function. An example of this could be to sum only the negative values in a range.

This lesson covers;

  • How to enter criteria to test values in SUMIF.
  • When you can omit the last argument of the SUMIF function.
  • Summing only the negative values in a list.
Summing Only the Negative Values
02:44

You can also use a formula to sum values in a list dependent upon multiple conditions. For example you could sum the sales values dependent upon the product and the region sold.

This lesson covers;

  • How to use the SUMIFS function.
  • Use SUMIFS to sum the total expenses for a specific office and expense type.
Summing Values that Meet Multiple Criterion
04:56
+
Counting Formulas
5 Lectures 18:34

You may need to count the number of blank cells in a range. For this, you can use the COUNTBLANK function. This function will count all the empty cells in a range including empty strings generated by IF or IFERROR functions.

Counting the Blank Cells in a Range
01:39

To count all the non-blank cells in a range, you need COUNTA. This function will count all the cells containing values, text and even logical values (TRUE and FALSE).

Counting the Nonblank Cells in a Range
01:49

You may need to count the cells in a range that meet a specific condition. The COUNTIF function can be used for this.

This lesson covers;

  • How to use the COUNTIF function.
  • Entering text into the formula for the criteria.
  • Referring to cells as the criteria for the formula.
  • Testing a value for the criteria of the formula.
Counting Cells that Meet Specific Criteria
06:24

To count cells in a range that meet multiple criterion, the COUNTIFS function can be used.

This lesson covers;

  • Using the COUNTIFS function.
  • Creating a COUNTIFS formula to count values dependent upon two conditions.
Counting Cells that Meet Multiple Criterion
04:02

You may need to count the unique values in a range, but Excel does not have a function to do this.

This lesson covers;

  • Combining the SUM and COUNTIF functions in a formula to count unique values.
  • A detailed explanation to how this works.
  • Counting the unique entry of names in a list.
Counting the Unique Values in a Range
04:40
+
Calculating Averages
7 Lectures 21:07

When people refer to calculating the average, they are normally referring to the mean average. This is calculated by dividing the sum of a range of values by its count.

This process is simplified by using the AVERAGE function in Excel. Blank cells and cells containing text are excluded from the calculation.

Finding the Mean Average
01:43

The AVERAGEIF function can be used to calculate the mean average for a range of values that meet a single criterion.

This lesson covers;

  • Understanding the syntax of the AVERAGEIF function.
  • Calculating the average for values that meet specific criteria.
Calculate the Average of Values that Meet Specific Criteria
03:07

The AVERAGE function despite excluding blank cells in a range will include any zeros in its calculation. To exclude zeros when calculating an average, the AVERAGEIF function be used.

This lessons covers;

  • An example of zeros affecting an average formula.
  • Using AVERAGEIF to exclude the zeros in a range of values.
Calculating the Average Excluding Zeros
02:52

You may need to calculate the average dependent upon multiple conditions.

This lesson covers;

  • Understanding the syntax of the AVERAGEIFS function.
  • Calculating the average of values meeting specific criteria and excluding zeros in a range.
Calculating the Average of Values that Meet Multiple Criterion
03:21

The value that occurs most often in a range is known as the mode, or modal value. Excel has a MODE function to return the most frequently occurring value in a range.

This lesson covers;

  • How the MODE function works.
  • Using the MODE function to find the modal value in a range.
Finding the Most Frequently Occurring Value
04:14

The middle value in a range is known as the median. The MEDIAN function can be used to calculate this.

This lesson covers;

  • How the MEDIAN function finds the middle value.
  • The syntax of the MEDIAN function.
Calculating the Median (Middle) Value in a Range
03:09

One potential problem when calculating averages are outliers. These are values that are much bigger, or much smaller than the other values in the range.

To prevent these outliers from having an impact on your average calculation, you could use TRIMMEAN.

This lesson covers;

  • An example of when TRIMMEAN could be used.
  • How to use TRIMMEAN.
Calculating the Trimmed Mean
02:41
+
Specialised Functions for Summing and Counting
4 Lectures 24:01

The SUBTOTAL function performs subtotals on a list or database. This means you can perform functions such as sum, count and average while ignoring hidden rows.

This lesson covers;

  • Understanding the syntax of the SUBTOTAL function.
  • Using SUBTOTAL to perform functions on a range including hidden rows.
  • Filtering data on a SUBTOTAL list to specify the values to calculate.
The SUBTOTAL Function
04:59

The AGGREGATE function was introduced with Excel 2010 as an enhancement to SUBTOTAL. It can be used to perform aggregates such as sum, count and average, whist ignoring hidden rows or nested subtotals.

The key advantage to AGGREGATE over SUBTOTAL is that it can be used to ignore error values in a range when summing and counting.

This lesson covers;

  • Understanding the syntax of the AGGREGATE function.
  • Using AGGREGATE to sum values whilst ignoring errors in the range.
The AGGREGATE Function
04:42

The SUMPRODUCT function can be used to perform powerful array calculations. It can be used as an alternative to SUMIFS and COUNTIFS for complex summing and counting tasks.

This lesson covers;

  • Using SUMPRODUCT for complex summing and counting.
  • Using both AND and OR logic in a SUMPRODUCT calculation.
  • Using brackets to stipulate the order of calculation.
The Awesome SUMPRODUCT Function
07:31

It could be that the values you wish to sum are not in consecutive cells. For example, your list may be structured in a way that you want to sum every other row, or every 5th row.

This lesson covers;

  • Using the SUMPRODUCT function to sum the values from every 5th row.
  • Using the MOD and ROW functions in the 'every nth' row criteria.
Summing 'Every Nth' Row
06:49
+
Working with Text
7 Lectures 39:41

Excel provides three useful functions for changing the case of text.

This lesson covers;

  • Using the LOWER function.
  • Using the PROPER function.
  • Using the UPPER function.
Changing the Case of Text
02:42

Erroneous spaces at the beginning and end of a text string can be a common cause for data analysis going wrong. Fortunately Excel provides a function to remove the erroneous spaces from cells.

This lesson covers;

  • Understanding the syntax of the TRIM function.
  • Using the TRIM function to remove spaces from the start and end of a string.
Removing Leading and Trailing Spaces
03:05

You may wish to join the text from multiple cells into one cell. There are two ways to do this in Excel.

This lesson covers;

  • Joining text (strings) using the CONCATENATE function.
  • Joining text using the ampersand (&).
Joining Text from Different Cells
04:11

Extracting text from a string is a common use of text functions in Excel. This is the first of a few lessons performing various text extraction tasks.

This lesson covers;

  • An example where different parts of a string need to be extracted.
  • Using the LEFT function to extract text from the beginning of a string.
  • Using the RIGHT function to extract text from the end of a string.
  • Using the MID function to extract text from the middle of a string.
  • Nesting the TRIM function within these formulas to remove erroneous spaces before attempting to extract.
Extracting Text from the Start, Middle and End of a String
07:02

Sometimes you will need to extract text from strings that have good structure and are reliable, and at other times they will be irregular.

This lesson covers;

  • Extracting the first word from a cell.
  • Using the FIND function to determine the length of the word in a cell.
  • Extracting the last word from a cell using RIGHT, FIND and TRIM.
Extracting Text from Irregular Strings
10:06

A string can be different parts that are formed together such as 245-THAJ-013-1, where the hyphens are used to separate each part, however each part also has its own meaning.

This lesson covers;

  • Extracting the characters from the second hyphen in a cell.
  • Using the SUBSTITUTE function with FIND to locate the second hyphen.
  • Using the MID function to perform the extraction.
Extracting Text from the Nth Occurrence of a Character
07:50

Excel does not contain a function to count the number of words in a cell. Tasks such as analysing keyword data entered into search engines could be a reason to count how many words are in a cell.

This lesson covers;

  • A formula to count how many words are in a cell.
  • Combining the LEN, TRIM and SUBSTITUTE function for the task.
Counting the Number of Words in a Cell
04:45
+
Date Formulas
8 Lectures 39:50

If you are going to be writing formulas to perform calculations on dates, something you will often need to know is what the current date is. With the date changing daily, you need a function to always return the current date.

This lesson covers;

  • Using a function to return the current date.
  • Using a function to return the current date and time.
Calculating the Current Date and Date & Time
03:31

There are many reason that you may need to calculate the number of days between two dates. This could be to monitor the length of time between and order being placed and it being dispatched, or the time between an invoice being sent and it being paid.

This lesson covers;

  • How to calculate the number of days between two dates on a worksheet.
  • Possible formatting issues with the formula result.
Calculating the Number of Days Between Two Dates
03:23

It may be that you need to calculate the number of working days between two dates. Possible reasons to do this could be; to track invoice payments are made within a specified number of working days, or to calculate billable days on a task.

This lesson includes;

  • An understanding of the NETWORKDAYS function.
  • Using the NETWORKDAYS function to calculate the days difference excluding weekends.
  • specifying custom non-working days (holidays) to the NETWORKDAYS formula.
  • An explanation of NETWORKDAYS.INTL.
Calculating the Number of Workdays Between Two Dates
06:10

When calculating the difference between two dates, you may want to return the answer in years, months or both. Typical scenarios for this include calculating someone's age, or an employee's length of service.

This lesson covers;

  • The undocumented DATEDIF function in Excel.
  • Understanding the anatomy of the DATEDIF function.
  • Specifying the interval for DATEDIF to return the answer as years, or in months.
  • Concatenating DATEDIF functions to return the answer as years and months.
Calculating the Difference Between Two Dates in Years and Months
07:24

You may need to add months onto a date to calculate a future date. Maybe to view the last date of a contract, or some other expiry date.

This lesson covers;

  • Understanding the EDATE function.
  • Understanding the EOMONTH function.
  • Calculating the last date of a contract.
Adding Months to a Date - EDATE and EOMONTH
04:40

Excel contains a function to calculate a date a specified number of working days in the future, or in the past. This lesson looks at calculating a future date.

This lesson covers;

  • Using the WORKDAY function.
  • Calculating the estimated finish date for task in a list using the start date and estimated duration.
Adding Working Days to a Date
05:36

You may have a requirement to identify the day of the week for a specific date. This lesson looks at how to change the colour of all the cells in a list that contains a date which is a Sunday.

This lesson covers;

  • Understanding the syntax of the WEEKDAY function.
  • Customising the first day of the week in the WEEKDAY function.
  • Writing a formula in a Conditional Formatting rule to apply a fill colour to the Sundays in a list of dates.
Highlighting the Sundays in a List
04:41

The DATE function can be used to convert data on a spreadsheet into a date format. It is also useful for entering dates into other formulas such as IF.

This lesson covers;

  • Understanding the syntax of the DATE function.
  • Converting data in the format of 20150320 that you can receive in file names of photos and videos into a date.
  • Using the LEFT, MID and RIGHT text functions to extract data.
The DATE Function
04:25
7 More Sections
About the Instructor
Alan Murray
4.5 Average rating
295 Reviews
3,379 Students
5 Courses
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.