Essential Excel Formulas and Functions
4.3 (140 ratings)
1,849 students enrolled

# Essential Excel Formulas and Functions

Overcome your Excel formula phobias with this clear and easy to follow guide
4.3 (140 ratings)
1,849 students enrolled
Created by Grant Gamble
Last updated 3/2017
English
English [Auto-generated]
Current price: \$12.99 Original price: \$19.99 Discount: 35% off
7 hours left at this price!
30-Day Money-Back Guarantee
This course includes
• 2.5 hours on-demand video
• Access on mobile and TV
• Certificate of Completion
Training 5 or more people?

What you'll learn
• Understand the key elements which are used to create Excel formulas and feel confident about building formulas of their own.
Requirements
• Excel 2016, 2013, 2010 or 2007 running under Windows. Mac users will also benefit from this course, but there are a few differences between versions.
Description

Formulas and functions are at the core Microsoft Excel and learning to use them effectively is an essential step in becoming proficient in Excel. This course provides clear and easy-to-follow instructions on how to build formulas for analysis, projection and data cleansing.

• The course starts by discussing all of the different components that can be used to create a formula. You will be given clear demonstrations of the use of relative, mixed and absolute references, the use of dollar signs and how to name cell ranges to add clarity to your formulas.
• Naturally, Excel functions are discussed in depth and many examples of using functions to analyze your data, beginning with every day functions like SUM, AVERAGE, MIN and MAX.
• We then examine the use of text functions to clean up data prior to creating reports; functions like TRIM, UPPER, LOWER, PROPER, LEFT, RIGHT, MID and CONCATENATE.
• Next, we turn our attention to conditional functions like IF, IFERROR, AND and OR. These functions allow you to make the value displayed in a cell dependent on the results of logical tests.
• We then see how conditional functions can be combined with mathematical functions; using COUNTIF and SUMIF to produce calculations which only include those cells which match certain criteria.
• And, naturally, no discussion of Excel functions would be complete without lookup functions. We discuss the use of VLOOKUP and, also, how the INDEX and MATCH functions can be used to lookup up data whose format makes the use of VLOOKUP impossible.

When you have completed this course, you will feel more confident about creating your own Excel formulas and taking advantage of Excel's wide variety of powerful functions.

This course was last updated on 25th March 2017, with the addition of a new project: the creation of an invoicing model.

Who this course is for:
• Excel users both inexperienced and experienced who don't feel confident about using Excel formulas.
Course content
Expand all 32 lectures 02:32:26
+ 1. Excel Formula Essentials
6 lectures 47:30

Formulas are one of the key components of an Excel worksheet; this is, after all what makes Excel so useful; the fact that it can perform calculations on the data that you enter in your worksheets. In this video, we examine the key elements that can be included in a formula by entering the formulas required to create an invoice template.

Preview 08:41

In the last video, we saw how you can create a formula once and then copy it into other cells; and have Excel automatically modify the formula, based on the new location. In this video, we'll get some more practice on doing that and discuss, in a bit more depth, exactly how Excel arrives at the correct conclusions.

Preview 07:55

In this video, we'll look at an occasion where it is not useful to have Excel modify cell references when a formula is copied; and how you can tell Excel which cell references should not be changed when you copy a formula.

Preview 07:26

Having examined relative and absolute references, in this video, we'll look at  a third type of reference called mixed references. With mixed references, when you copy the formula, you want only one component to change; either the column letter or the row number, but not both.

4. Mixed References
06:38

As well as using parentheses to enclose function arguments, Excel uses parentheses in another context; and this usage coincides exactly with the use of parentheses in mathematics: to indicate the order of precedence when carrying out calculations.

5. Operator Precedence
07:45

Almost all Excel formulas contain cell references; and, if you are building a worksheet that contains a lot of formulas, it can become quite difficult to make sense of the worksheet formulas. One way to introduce a bit of clarity into your formulas is to use named ranges.

6. Using Named Ranges
09:05
+ 2. Text functions
6 lectures 22:10

Very often, if all you want to do is to clean up data, or to modify the way that has been entered, you can simply use Flash Fill as an alternative to using formulas.

1. Flash Fill Revision
02:41

The TRIM function is a very simple but useful function which removes unwanted space from cell entries; and it is typically used when you are cleaning up text before producing a report.

2. Using The TRIM Function
03:00

Excel has three functions for changing the case of your text, two of which you should be able to guess with no problem: LOWER and UPPER, which of course correspond to upper and lower case. The third one is PROPER; this is what is sometimes called title case, whereby the first letter of each word is capitalized.

3. Changing The Case Of Text
04:16

The LEFT and RIGHT functions are used, in Excel, to extract information, starting from the left or starting from the right.

4. The LEFT And RIGHT Functions
03:09

The MID function is used to extract characters from a string of text, starting from any position. In this example, we have a customer code and we want to extract the two-letter country code in the middle of the string.

5. The MID Function
02:34

Excel's CONCATENATE function allows you assemble different textual components into one cell by stringing them together. In this example, we are starting with three separate columns containing "Title", "First Name" and "Last Name"; and we are looking to assemble them into a single cell; and this is exactly what CONCATENATE does.

6. The CONCATENATE Function
06:30
+ 3. Conditional functions
5 lectures 27:01

The IF function in Excel is used to make the value in a cell dependent on the result of a logical test; a logical test being one which can only produce the values true or false.

1. Using the IF Function
05:16

The IF function is very versatile in Excel; by contrast, IFERROR has a very limited usage: its role is to suppress error values and replace them with something more user-friendly.

2. Using the IFERROR Function
03:01

In the first video of this section, we had a look at a basic IF statement; sometimes, however, you want to cater for more than two eventualities; and, in this scenario, one approach is to use what is called a nested IF statement. This is where you use more than one IF statement; and Excel requires you to put one IF statement inside another, as its argument.

3. Using Nested IF Statements
06:39

Excel's OR function is used to create a composite logical test; one in which you have a series of possibilities, only one of which needs to be true in order for the overall test to be true.

4. Using the OR Function
06:46

The AND function in Excel is used to create a composite logical test in which several possibilities all have to be true in order for the overall test to be true.

5. Using the AND Function
05:19
+ 4. Conditional number crunching functions
2 lectures 09:08

Excel's COUNTIF function is used to count the cells within a given range in which a certain condition is satisfied. It also has a "partner in crime", COUNTIFS, which does exactly the same thing, but allows you to specify two or more criteria.

1. Using the COUNTIF Function
05:54

The SUMIF function is obviously a combination of the SUM and IF functions; it allows you to calculate a conditional total.

2. Using the SUMIF Function
03:14
+ 5. Lookup functions
3 lectures 15:23

Excel VLOOKUP function is used to retrieve a value from what is called a lookup table. In this example we use VLOOKUP to retrieve the business sector description which matches a given sector code.

1. Using VLOOKUP Exact Match
07:08

As well as retrieving information by making a specific match to a given value, the VLOOKUP function can be used to retrieve information by making an approximate match.

2. Using VLOOKUP Approximate Match
02:44

Using the INDEX and MATCH functions in combination provides a great deal of flexibility when retrieving information from an ordinary worksheet, as opposed to a specially constructed lookup table.

3. Using INDEX and MATCH
05:31
+ 6. Project - Creating an invoice template
8 lectures 26:03

In this video, we examine the key features of the completed template.

Preview 02:03

We begin by entering formulas to calculate line totals, sub-totals, VAT and grand total.

2. Creating basic formulas
04:22

In this video, we take a break from creating formulas and create a drop-down list to display the names of all customers.

3. Creating a drop-down list
02:23

In this video, we use the VLOOKUP function, in conjunction with the concatenation operator, to pull in the address of the selected customer.

4. Using VLOOKUP and concatenation
04:25

The CHAR function can be used to insert non-alphanumeric characters into a cell. In this video, we use it to insert line breaks after each line of the customer address.

5. Using CHAR to insert line breaks
02:27

As it stands, if the VLOOKUP function returns a null, our formula will insert a blank address line. In this video, we use an IF statement to prevent blank lines from being generated.

6. Using IF to suppress blank address lines
03:23

In this video, we protect the invoice worksheet; so that users can only enter values in cells which do not contain formulas.

7. Protecting the worksheet
03:25

We end this project by saving our invoice model as an Excel template.

8. Saving the model as a template
03:35