Find online courses made by experts from around the world.
Take your courses with you and learn anywhere, anytime.
Learn and practice realworld skills and achieve your goals.
Last Updated in November 2016
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
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.
Not for you? No problem.
30 day money back guarantee.
Forever yours.
Lifetime access.
Learn on the go.
Desktop, iOS and Android.
Get rewarded.
Certificate of completion.
Section 1: Introduction  

Lecture 1  03:06  
This first lesson includes;


Lecture 2 
Course Files  Download these files to follow along

Article  
Section 2: Formulas in a Nutshell  
Lecture 3  09:37  
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;


Lecture 4  06:02  
When entering a formula in Excel you need to be aware of the order of calculation, and how to control it. This lesson covers;


Lecture 5  06:26  
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;


Lecture 6  10:50  
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;


Section 3: Referencing Cells, Sheets and Workbooks in a Formula  
Lecture 7  03:54  
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;


Lecture 8  03:23  
A mixed reference is only partially fixed. There are two types of mixed reference; the row absolute and the column absolute. This lesson covers;


Lecture 9  06:54  
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;


Lecture 10  08: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;


Lecture 11  08:55  
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;


Section 4: Using Logical Functions  
Lecture 12  04:47  
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;


Lecture 13  05:13  
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;


Lecture 14  09:41  
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;


Lecture 15  04:46  
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;


Section 5: Formulas for Summing Values  
Lecture 16  02:53  
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;


Lecture 17  04:19  
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;


Lecture 18  02:44  
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;


Lecture 19  04:56  
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;


Section 6: Counting Formulas  
Lecture 20  01:39  
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. 

Lecture 21  01:49  
To count all the nonblank cells in a range, you need COUNTA. This function will count all the cells containing values, text and even logical values (TRUE and FALSE). 

Lecture 22  06:24  
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;


Lecture 23  04:02  
To count cells in a range that meet multiple criterion, the COUNTIFS function can be used. This lesson covers;


Lecture 24  04:40  
You may need to count the unique values in a range, but Excel does not have a function to do this. This lesson covers;


Section 7: Calculating Averages  
Lecture 25  01:43  
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. 

Lecture 26  03:07  
The AVERAGEIF function can be used to calculate the mean average for a range of values that meet a single criterion. This lesson covers;


Lecture 27  02:52  
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;


Lecture 28  03:21  
You may need to calculate the average dependent upon multiple conditions. This lesson covers;


Lecture 29  04:14  
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;


Lecture 30  03:09  
The middle value in a range is known as the median. The MEDIAN function can be used to calculate this. This lesson covers;


Lecture 31  02:41  
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;


Section 8: Specialised Functions for Summing and Counting  
Lecture 32  04:59  
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;


Lecture 33  04:42  
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;


Lecture 34  07:31  
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;


Lecture 35  06:49  
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;


Section 9: Working with Text  
Lecture 36  02:42  
Excel provides three useful functions for changing the case of text. This lesson covers;


Lecture 37  03:05  
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;


Lecture 38  04:11  
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;


Lecture 39  07:02  
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;


Lecture 40  10:06  
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;


Lecture 41  07:50  
A string can be different parts that are formed together such as 245THAJ0131, where the hyphens are used to separate each part, however each part also has its own meaning. This lesson covers;


Lecture 42  04:45  
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;


Section 10: Date Formulas  
Lecture 43  03:31  
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;


Lecture 44  03:23  
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;


Lecture 45  06:10  
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;


Lecture 46  07:24  
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;


Lecture 47  04:40  
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;


Lecture 48  05:36  
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;


Lecture 49  04:41  
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;


Lecture 50  04:25  
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;


Section 11: Time Formulas  
Lecture 51  02:04  
If you are using an Excel spreadsheet to record times, a common requirement may be to calculate the difference between the two times. This lesson covers;


Lecture 52  02:15  
This lesson looks at calculating the difference between times on two different dates. This can create some problems if the date is not recorded in the same cell as the time. This lesson covers;


Lecture 53  02:23  
You can sum times in Excel just you sum any other values. However you can encounter a problem when summing times that exceed 24 hours. This lesson covers;


Lecture 54  03:43  
You might have cells on a spreadsheet that contain a number of hours or minutes stored in decimal form. For example; 9.25 hours, or 400 minutes. These can be converted to a time format for easier reading and further analysis. This lesson covers;


Section 12: Lookup Formulas  
Lecture 55  09:25  
The VLOOKUP function is the most commonly used lookup function of Excel. This vertical lookup is used to look for and return a value from a table. This lesson covers;


Lecture 56  06:08  
The VLOOKUP function can also be used to look for a value in a range and use the closest match. This lesson covers;


Lecture 57  03:32  
The HLOOKUP function works in the same way as VLOOKUP apart from it looks horizontally along a row for the lookup value. This lesson covers;


Lecture 58  06:28  
Create a lookup formula that can lookup information down a column and across a row. This two way lookup can be created by combining the powers of the VLOOKUP and MATCH functions. This lesson covers;


Lecture 59  08:35  
By using the INDEX and MATCH functions together you can create a very versatile lookup formula. These two functions complement each other nicely and provide a nice alternative to VLOOKUP. This lesson covers;


Lecture 60  05:37  
Lookup formulas such as VLOOKUP, HLOOKUP and MATCH return the #N/A error when they cannot find a match for the lookup value. This error message is ugly and can cause problems with other formulas on your spreadsheet. This lesson covers;


Section 13: Advanced Lookup Formulas  
Lecture 61  06:25  
This lesson looks at how to create a lookup that is based on the values in two columns. The same technique can be used for lookups based on multiple columns. This lesson covers;


Lecture 62  07:32  
You can use a lookup formula to return the address of a cell instead of its value. You would usually be doing this to feed another function with the address. This lesson covers;


Lecture 63  06:06  
Lookup functions such as VLOOKUP and MATCH are not case sensitive. This lesson looks at how you can create a case sensitive lookup formula. This lesson covers;


Lecture 64  05:11  
Lookup formulas are typically used to return values. This lesson looks at how to create a lookup formula to return a picture from a list. This lesson includes;


Lecture 65  06:28  
You may have multiple lookup tables for a VLOOKUP, and want the required lookup table to be selected by a user. This lesson includes;


Lecture 66  03:25  
Creating lists to assist data entry is a common technique in Excel. If your list is large, you may want to break it into multiple dependent lists. This lesson covers;


Lecture 67  07:49  
The HYPERLINK function is used to create hyperlinks to other cells, files or webpages. It's brilliance is evident when used with other functions such as IF and VLOOKUP for conditional hyperlinks. This lesson covers;


Section 14: Solving Formula Errors  
Lecture 68  Article  
Not all formulas will return an error message when there is a problem. Some problems will work but return the wrong answer because they are written correctly, but the logic is wrong. These can be harder to diagnose. Fortunately Excel displays an error message whenever there is a problem with the way a formula is written. This lesson covers;


Lecture 69  Article  
This lesson looks at some common formula problems and how they can be identified and resolved. The lesson includes;


Lecture 70  08:35  
No matter how good are you are at writing formulas, if mistakes have been made when entering data onto the worksheet your formulas may stop working. Excel has a feature called Data Validation that can be used to try and prevent mistakes such as typos and spelling errors on data entry into cells. This lesson covers;


Lecture 71  04:17  
Not all formula error messages will indicate a problem. The formula may be fine, but the data it uses may not be available to it yet. This is quite typical with lookup formulas and formulas that divide values, but is not limited to those. This lesson covers;


Lecture 72  06:45  
Protecting the formulas on a worksheet is a good idea to protect them against accidental damage. This lesson covers;


Lecture 73  02:39  
When troubleshooting formulas, it may be helpful if they were all visible on the worksheet. By viewing the formulas on a worksheet, the formula text is shown instead of its result. This lesson covers how to view the formulas on a worksheet and also to hide them again when finished. 

Lecture 74  06:31  
When trying to diagnose the problem with a formula, it can help if you understand its relationship with the other cells of the worksheet. Excel provides a formula auditing tool to visualise these links between cells to make them easier to understand. This lesson covers;


Lecture 75  06:22  
The Evaluate Formula tool is Excel's main tool for debugging large and complex formulas. It enables you to step through a formula, evaluating each part individually to identify the cause of the problem. This lesson covers;


Lecture 76  06:14  
When working with formulas in Excel, you have probably come across a circular reference error. These are normally encountered after making a simple mistake and can easily be corrected. This lesson covers;


Section 15: New Functions in Excel 2016  
Lecture 77  04:20  
The TEXTJOIN function makes it easy to join together the text from multiple cell values. All we need to to is enter a delimiter, the range of values to join and whether we wish to have blank cells ignored or not. A beautiful new function to use. 

Lecture 78  02:39  
In Excel 2016 the CONCAT function has been introduced to replace CONCATENATE from a previous lecture of this course. NOW CONCATENATE still exists so is relevant to still know. It has been left for compatibility reasons, whilst CONCAT is now here to be used. 

Lecture 79  03:52  
The IFS function has been introduced in Excel 2016 to improve the way that we write nested IF's. Writing nested IF's to perform multiple tests and perform resulting actions can create long and complicated formulas. This new IFS function is a great addition to the Excel formula library and really condenses the formula down into something much easier to construct and manage. 

Lecture 80  04:36  
The SWITCH function is a new logical function to Excel 2016. It is used to test an expression (cell value) against a list of values and perform a resulting action. In this lesson this function is used to test for a particular office destination against a list of offices and return the resulting distance of that office. 

Lecture 81  04:30  
The MAXIFS function can return the maximum value from a range when multiple criterion are met. In this lesson it is used to return the largest order of a specific product category from a customer in a specific country. 

Lecture 82  03:02  
The MINIFS function can return the minimum value from a range when multiple criterion are met. In this lesson it is used to return the smallest order of a specific product category from a customer in a specific country. 

Section 16: More Learning  
Lecture 83 
Bonus Lecture: Download your FREE eBook on 40 Incredible Excel Tricks

Article 
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.