Excel Formulas and Functions: Make Basic & Advanced Formulas
4.3 (336 ratings)
12,343 students enrolled

# Excel Formulas and Functions: Make Basic & Advanced Formulas

Learn MS Excel Formulas and Functions. Create Basic and Advanced Excel Formulas. Make an Excel Template Project.
4.3 (336 ratings)
12,343 students enrolled
Created by Kawser Ahmed
Last updated 6/2016
English
English
Current price: \$86.99 Original price: \$124.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
• 5.5 hours on-demand video
• 4 articles
• Access on mobile and TV
• Certificate of Completion
Training 5 or more people?

What you'll learn
• Learn Excel Formulas and Functions from Zero.
• Learn how to build a professional level Excel Template using Excel functions.
Requirements
• Microsoft Office Software Installed.
Description

Teaching 11 Courses on Excel and Data Analysis!

OVER 25,000 visitors visit my blog ExcelDemy dot com every month!!

OVER 15,000 successful students have already taken my online courses since November, 2015 with 375 total Reviews!!!

Course Updated:  Nov 01. 2016

Excel formulas and functions are the heart of Microsoft Excel spreadsheet. They are essential to manipulating data and obtaining useful information from your Excel workbooks.

Without Excel formulas and functions, Excel spreadsheet would simply be a fancy word-processing document. Welcome to my Excel Formulas and Functions course.

Let’s discuss now what you will learn from this course.

1. Operators and Precedence of Operators: I will start from zero and if you don’t know about the operators and precedence of operators that Excel uses in its formulas, then it will be tough for you to decode complex Excel formulas. So I have covered them in very details.
2. How to use built-in Excel functions: Then you will learn how to use built-in excel functions into the Excel formulas using the Insert Function and Function Arguments dialog box.
3. Subtotal Function: You will find many uses of Subtotal function if you are using Excel with your finance related jobs. So this function is covered in details at the start of the course.
4. Relative, Absolute and Mixed Cell References: You will work with these three concepts time to time when you will build excel formulas. So, they have got a good time in the course.
5. Formulas with Tables: Understanding how formulas work with Excel tables is very important. I have used two lectures to cover Excel formulas with tables. In the second lecture, structured referencing with Excel tables has been discussed.
6. Then you will learn how to handle circular references with Excel formulas, how named cells and ranges work with Excel formulas, and so on.
7. Mathematical functions are very important in your daily work with finance or any kind of jobs. In this course, you will learn about SUM, AVERAGE, MAX, MIN, INT, MOD, ROUND, RAND, and RANDBETWEEN Excel functions. Counting related excel functions are also covered in this course.
8. Then you will work with 3-D Referencing with Excel Formulas.
9. In Advanced Excel formulas, you will learn: IF, IFERROR, DATE related functions, Text manipulating functions, and many more.
10. Manipulating Text using Excel Functions: There is a section where you will learn how to manipulate text using Excel formulas and functions.
11. Then you have worked with VLOOKUP, INDEX, and Array formulas.
12. And at the end of the course, you will build a professional Excel template where you will learn how to make complex Excel formulas using Excel functions.
13. Here is not the end of the course. Time to time, I shall add more lectures to the course that will teach you how to face real life problems using Excel formulas and functions.

So, if you are interested to learn advanced Excel formulas and functions with a real life project, then this course is for you. Thanks for reading. Enjoy the course!

Who this course is for:
• This course is meant for intermediate level Excel users who are familiar with the general concepts of Excel features.
Course content
Expand all 55 lectures 05:22:30
+ Welcome to the Course! Be a Professional Excel User with this course!
5 lectures 14:17

What you will get from this Excel lecture:

1. This is the download section of the whole course. You will get all the working files, practice problems, Excel keyboard shortcuts PDF, and other Excel resources in one place.
2. You will also get an idea how to instantly contact me when you face an Excel or Course related problem.
00:49
List of sites where you can practice Excel problems
00:46
0102 How to Best View This Course
06:58
0103 How to Review this Course and Obtain Your Certificate of Completion
02:40
+ Formula and Function Basics
14 lectures 01:41:53

In this video lecture,

1. You will be introduced with the operators that are used by Excel.
2. You will also learn how to use different operators in Excel.
Preview 10:28

Operator has working hierarchy. All the operators in a formula does not work at the same time.

In this lecture,

• You have learned all about Excel operator precedence.
Preview 09:31

In this video lecture,

• You will learn how to insert a built-in function into a formula.
0203 How Built in Excel Functions Make Your Job Easier
07:17

Excel is nothing but a word processor if you deduct Excel's capability to use functions. Functions are the hearts of Excel. In this video, you will learn how to insert Excel functions into a formula.

You will also learn how to use effectively Insert Function dialog box.

0204 Inserting Functions into Formulas Effectively
09:40

In this video lecture,

1. You will learn with examples how relative referencing works in Excel formula.
0205 Relative Cell References - Using Relative Cell References to Copy Formulas
05:23

In this video lecture,

• You will learn about SUBTOTAL function.
0208 Subtotal Function - Using Subtotal Function Effectively
06:56

In this video lecture,

• You will learn all the ways to use structured references with Excel tables.
0209 Using Formulas in Tables - Summarizing and Referencing Data in a Table
12:44

In this video lecture,

• You will learn how to use structured references in an Excel table.
Preview 08:31

In this video lecture,

• You will learn how to show the formulas in a worksheet.
0211 Displaying All the Formulas on a Worksheet
03:04

In this video lecture,

• You will learn how to work with Circular Reference in Excel.
0212 Handling Circular References
03:24

In this video lecture,

• You will learn how to give a name to a cell or a range.
0213 Using Named Cells and Ranges in the Formulas
07:48

In this video lecture,

• You will learn how to give a name:
• To a constant
• And to a formula.
0214 Using Names for Constants & Formulas & Applying Names to Existing Reference
08:01

In this video lecture,

• You will learn how to use Union (,) and Intersect ( ) operators in Excel.
0215 Union and Intersection of Ranges
05:28

In this video lecture,

• You will learn 3 Excel formula related techniques. They are:
1. How to use formula bar as a calculator in Excel
2. How to make an exact copy of a formula
3. And how to make formula cells into values cells.
0216 Some Tips on How to Work with Formulas
03:38
+ Useful Mathematical Functions
5 lectures 32:18

In this video lecture,

• You will learn about Excel’s one of the most important functions. The function is SUM function.
0301 SUM(...) Function - The Most Useful Function in Excel
07:51

In this video lecture,

• You will learn Excel’s AVERAGE, MAX and MIN Functions.
0302 Mathematical Functions: Average(), Max() and Min()
06:54

In this video lecture,

1. You will learn about 5 Excel important functions:
1. INT
2. MOD
3. ROUND
4. RAND
5. And RANDBETWEEN Functions.
0303 Mathematical Functions: INT(), MOD(), ROUND(), RAND(), and RANDBETWEEN()
07:10

In this video lecture,

• You will learn about three count related Excel functions. They are:
• COUNT Function
• COUNTA Function
• COUNTBLANK Function.
0304 Count Functions in Excel
03:17

In this video lecture,

1. You will learn
1. How to make a 3D-Referencing,
2. How 3D-Referecning works in different ways
3. And how to define a name for a 3D-Reference.
0305 Learn About 3-D Reference in Excel
07:06
14 lectures 01:19:58

In this video lecture,

1. You will learn how to work with IF function.
2. You will also learn how Nested IF function works.
0401 Using the IF(...) Function
07:54

In this video lecture,

1. You will learn how to handle errors using IF and IFERROR functions.
0402 Using IF(...) & IFERROR(...) function to Hide Excel Errors
06:05

In this video lecture,

1. You will learn how to work with DATEDIF(...) Function.
0403 DATEDIF(...) Function - A Useful Function
10:06

In this video lecture, you will learn how to work with the following functions:

1. LEFT Function
2. RIGHT Function
3. MID Function
4. FIND Function
5. And LEN Function.
0404 LEFT(...), RIGHT(...), MID(...), LEN(...) and FIND(...) Functions
06:59

In this video lecture,

You will learn how to concatenate two or more text strings into one in two ways:

1. Using CONCATENATE() Function
2. And using Ampersand (&) operator
0405 Concatenating Text Using CONCATENATE(...) Function and Ampersand Operator
04:15

In this video lecture,

• You will learn how to work with Excel’s TEXT Function.
0406 Using TEXT(...) Function to Manipulate Numbers
04:55

In this video lecture,

• You will learn how to create a histogram using REPT(...), CHAR(...) and CODE(...) functions.
0407 REPT(...) Function - Using REPT(...) Function to Make a Histogram
06:44

In this video lecture, you will learn several Excel functions related to Date & Time. They are:

1. TODAY Function
2. NOW Function
3. DATE Function
4. YEAR Function
5. MONTH Function
6. DAY and DAYS Functions
7. And NETWORKDAYS Function.
0408 Date Manipulation Functions
09:18

In this video lecture, you will learn about three functions. They are:

1. RANK function
2. RANK.EQ function
3. And RANK.AVG function.
0410 Use of Rank Functions to Calculate Placings
07:44

In this video lecture,

• You will learn how to work with Excel’s WEEKDAY Function.
0412 WEEKDAY(...) Function: Use of WEEKDAY(...) Function
03:38

In this video lecture,

• You will learn about OR() function in Excel.
0413 OR(...) Function: Using OR(...) Function
03:06

In this video lecture,

• You will learn about Excel’s ISTEXT() function.
0414 ISTEXT(...) Function: Using ISTEXT(...) Function
02:48

In this video lecture,

You will learn about two Excel functions. They are:

1. ROW Function
2. And COLUMN Function.
0415 ROW(...) and COLUMN(...) Functions
01:52

In this video lecture:

1. You will learn how to create a dependent list.
2. And to create the dependent list, you have to use the INDIRECT() function.
0416 INDIRECT(...) - Creating a Dependent Drop Down List in Excel
04:34
+ Creating Formulas that Manipulate Text
7 lectures 29:43
1. In this Excel training video, you will learn how you can search and find one or more characters within a text or string.
2. To do that, we have used two Excel functions: SEARCH(...) and FIND(...).
3. These two functions finds out the position of a text or character within another text.
4. Their functionality is almost same but SEARCH(...) function is not case sensitive and FIND(...) function is case sensitive.
0501 Finding and Searching within a Text
06:02
0502 Counting Specific Characters in a Cell
02:57
0503 Counting the Occurrences of a Substring in a Cell
02:26
0504 Extracting the First and Last Words of a String
06:13
0505 Extracting All but the First Word from a Text or String
02:41
0506 Separating the First, Middle, and Last names from the Full Names
04:44
0507 Counting the Number of Words in a Cell
04:40
+ Project - Develop a Professional Excel Template Using Excel Formulas
8 lectures 01:02:37

In this video lecture:

1. You will be introduced with the basic concept of Excel array formula.
2. You will learn how to enter array formula into cells in different ways.
3. And you will learn how to edit array formula.
0601 Introducing Array Formulas - How to Enter Arrays into Worksheet Cells
07:35

In this video lecture:

1. You will see in step by step by procedure how an array formula works internally in Excel.
2. To demonstrate the process, I have used Excel’s AVERAGE() and IF() functions.
0602 Introducing Array Formulas - How to Create Array Formulas with Examples
05:42

In this video lecture,

1. We shall learn the syntax and use of the INDEX() function.
2. INDEX() function is the only function that has two types of syntax; you will work with both of them.
0603 INDEX(...) Function in Excel
08:11

VLOOKUP() is one of the top 10 Excel functions.

In this video lecture: you will learn about VLOOKUP() function with multiple examples.

0604 VLOOKUP(...) Function in Excel
04:32

From this lecture and in the next 3 lectures, I have taught you how a professional and advanced Excel template is built actually. I have showed the process in step by step ways. I skipped some steps for your practicing purposes. Follow these tutorials and you will be able to build your own Excel template for yourself. Thanks for watching my course lectures.

0605 Invoice Template in Excel - Part 1
07:08
0606 Invoice Template in Excel - Part 2
07:38
0607 Invoice Template in Excel - Part 3
10:39
0608 Invoice Template in Excel - Part 4
11:12