The Most Useful Excel Formulas
4.0 (25 ratings)
2,037 students enrolled
Wishlisted Wishlist

# The Most Useful Excel Formulas

Learn the most useful Excel formulas that you will use time and again in business
4.0 (25 ratings)
2,037 students enrolled
Created by Andrew de Bruyn
Last updated 9/2016
English
Current price: \$10 Original price: \$40 Discount: 75% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
• 1.5 hours on-demand video
• 6 Articles
• 40 Supplemental Resources
• Access on mobile and TV
• Certificate of Completion
What Will I Learn?
• Make yourself more marketable by mastering the most useful Excel functions
• Use Excel productively by understanding and applying the most useful formulas
• Know how to use powerful lookup functions like VLOOKUP and SUMIF
• Confidently use logical functions like IF, AND, OR
• Perform financial calculations
View Curriculum
Requirements
• Excel 2013 or 2016
• Basic Excel knowledge - how to open, name and save spreadsheets
• Enthusiasm and willingness to learn!
Description

**** 2000+ Students enrolled ****

To get the most out of Excel in the quickest time, you should learn the most useful formulas first. This course is aimed at teaching you the functions that you are most likely to use and that you will get the most benefit from.

The course is divided into 5 sections:

1. Basic functions like SUM and SUBTOTAL
2. Fixed and variable formula references
3. Date and Text functions like LEFT,MID,RIGHT
4. Lookups like VLOOKUP and SUMIF
5. Logical functions like IF,AND,OR
6. Financial formulas

Each lecture includes the spreadsheet used in the lecture, which you can download and use. The course also includes a summary and quiz at the end of each section.

This course is not aimed at advanced users, but rather at those new to Excel or those who are still not confident about using functions in Excel such as lookups (VLOOKUP, SUMIF) or logical tests (IF,AND,OR).

Each lecture is concise and to the point - we don't waste time on theoretical discussions, but concentrate on building essential Excel skills.

Who is the target audience?
• Suitable for students, business owners or those in the corporate world
• Anyone who wants to improve their understanding of Excel functions or formulas
• Best suited for those relatively new to Excel or not fully confident with Excel functions like VLOOKUP, SUMIF
• Not suited for very advanced Excel users
Students Who Viewed This Course Also Viewed
Curriculum For This Course
38 Lectures
01:46:22
+
Introduction
1 Lecture 00:28
Preview 00:28
+
Basic Calculations
5 Lectures 14:16

The SUM formula - the most basic and widely used formula.

Preview 04:01

SUBTOTAL - how and when to use this formula, that is better than SUM when we deal with subtotals.

Preview 03:07

A very useful function for counting items in a list.

Preview 04:02

AVERAGE, MIN and MAX - functions that you will want to use frequently when analysing a table of figures.

AVERAGE MIN & MAX-finding the average, highest and lowest values
02:39

Summary of the concepts learned so far.

Basic Functions Summary
00:27

Basic Functions Quiz
4 questions
+
Relative and Fixed Formula References
5 Lectures 11:26

Understand how Excel using relative formula referencing and how to use this function.

Relative Formula References
02:14

How and when to fix the row reference when copying a formula

Fixed formula references - fixing the row reference
03:55

How and when to fix the column reference for a formula

Fixed formula references - fixing the column reference
02:17

How and when to fix both the column and row references for a formula

Fixed formula references - fixing both row and column references
02:08

Summary-Fixed and Relative References
00:52

Quiz-Relative and Fixed Formula References
3 questions
+
Date and Text Functions
8 Lectures 21:16

DAYS-a very useful function for payroll or other business calculations based on 2 dates

DAYS
03:59

A useful function that allows us to work out days worked and to automatically deduct holidays/vacations simply by listing the dates.

NETWORKDAYS
04:06

Two very useful functions to convert Arabic to Roman numerals and vice versa.

ROMAN and ARABIC
01:36

Functions that are valuable when we need to correct the format of text.

LOWER,UPPER and PROPER
02:03

Useful functions for extracting data from a string of text or figures.

LEFT and MID
03:33

More useful data extraction tools: LEN and RIGHT

LEN and RIGHT
02:50

CONCATENATE - joining different bits of text

CONCATENATE
01:56

Summary-Date and Text Functions
01:13

Quiz - Date and Text Functions
4 questions
+
Lookups
7 Lectures 23:05

Using VLOOKUP with the TRUE condition

VLOOKUP with TRUE condition
02:54

Using VLOOKUP with the FALSE condition to find an exact value

VLOOKUP with FALSE condition
02:57

Using HLOOKUP for horizontal lookups

HLOOKUP
03:25

SUMIF - for adding numbers based on a specified condition

SUMIF
04:48

SUMIFS-adding a range of numbers based on two or more conditions.

SUMIFS
03:03

OFFSET - a function that lets us select a range eg create a table for a year-to-date profit and loss statement

OFFSET
03:42

A summary of this section; VLOOKUP to OFFSET.

Summary - Lookups
02:16

Quiz-Lookups
4 questions
+
Logical Functions
7 Lectures 21:18

One of the most useful functions in Excel - the logical IF function

IF
04:35

Taking the IF statement a step further and adding another condition

Nested IF formula
04:54

Formatting cells based on specified conditions

Conditional Formatting
03:02

AND - the logical test that requires more than one condition

AND
02:39

OR-the logical function that looks for more than one possible condition

OR
02:33

Using IFERROR to avoid errors when we divide by zero

IFERROR and dividing by zero
01:35

Summary-Logical Functions
02:00

Quiz - Logical Functions
4 questions
+
Financial Functions
5 Lectures 15:29

PMNT-finding the payment for a loan based on certain inputs

PMT
03:34

Finding the PV of a loan - useful for determining the size of a loan that can be afforded

PV-Present Value
02:43

NPER-number of periods of an investment - useful for finding out how long an annuity will last

NPER
03:59

FV-future value, used for finding the future value of an investment

FV
03:35

Summary-Financial Functions
01:38

Quiz-Financial Functions
4 questions