Excel Formulas & Functions  In depth
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.
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.
In this Excel Formulas and Functions  In depth video course, Nurture Tech Academy introduces you to the most simplest and logical way to understand most of the Excel functions.
In this Excel training course, you will learn by watching the author actually perform the operation he is instructing on, as he explains step by step how to perform these functions. The training starts with how to write a Formula in Excel and starts with basic formulas like Sum and Count then gradually move to the complex one like IF, IF with And, IF with OR, Nested IF. Then it will cover the whole Lookup Functionality. Financial Functions will be covered there after with Date & Time Functions.
By the completion of this online training course, you will be fully versed, and capable of using most of the Formulas & Functions in Microsoft Excel 2007/2010/2013 in a commercial Environment.
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: An Introduction  

Lecture 1  01:13  
In this whole online tutorial we have tried to explore the most commonly used Excel functions. We will start from the basics of How to write a formula then start performing the functions and gradually move to the complex formulas. This whole tutorial covers the following functions : 1 Sum, SumIF, SumIFs 2 Count, CountIF, CountIFs, CountA, CountBlank 3 Text Functions like, Upper, Proper, Lower, Left, Right, Mid, Text to Column 4 Logical Functions like, IF, IF with AND, IF with OR, Nested IF 5 Lookup Functionality  Vlookup, Hlookup, Lookup, Index & Match 6 Date & Time Functions 7 Financial Functions  PMT, PPMT, IPMT, etc. 8 Misc. Functions 

Section 2: First thing First  How to Write a Formula in Excel  
Lecture 2  06:29  
In this Video i have tried to explain what is the proper way of writing a formula in Excel, because on the basis of this we will able to use any formula in future. 

Section 3: Sum Functions  
Lecture 3  03:44  


Lecture 4  05:54  
SUMIFS is the plural version of SUMIF. Using SUMIFS you can find the sum of values in your data that meet multiple conditions. Syntax
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) 

Section 4: Count Functions  
Lecture 5  05:00  


Lecture 6  02:35  


Lecture 7  04:24  


Lecture 8  05:47  


Section 5: Text Functions  
Lecture 9  07:57  
In this Video we will talk about some basic text functions like: Upper Lower Proper Rept 

Lecture 10  07:52  
In this Video we will talk about some basic text functions like: Left Right Mid concatenate 

Lecture 11  07:15  
Split content based on a delimiterUse this method if your names have a delimited format, such as "First_name Last_name" (where the space between First_name and Last_name is the delimiter) or "Last_name, First_name" (where the comma is the delimiter). 

Section 6: Logical Functions  If Functionality  
Lecture 12  04:55  


Lecture 13  05:59  
In this we will try to merge two functions in excel i.e. IF and AND. An AND function is like if u are sitting in five exams and if you passed in all the exams then only the final result will be Pass and if you got fail in any one of the exams then you will be considered as Fail. 

Lecture 14  06:31  
In this we will try to merge two functions in excel i.e. IF and OR. An OR function is like if u are sitting in five exams and if you passed in any of the exams then the final result will be Pass and if you got fail in all of the exams then you will be considered as Fail. 

Lecture 15  11:01  
A Nested IF function is when a second IF function is placed inside the first in order to test additional conditions. "Nesting" IF functions increases the flexibility of the function by increasing the number of possible outcomes. 

Section 7: Lookup Functionality  Vlookup, Hlookup, Lookup, etc.  
Lecture 16  07:22  
In this lecture we will cover 4 types of references. It is really important to know how excel reacts on references. The references available in excel are as follows: 1. Relative Reference  In this case if you refer a cell address it will not follow that address strictly i.e. it will change if you copy and paste the same on another cell. 2. Absolute Reference  In this case it will follow the same cell address everywhere when we copy and paste the same. 3. Mixed Reference  Under Mixed reference, we can either freeze a column of a row whenever we refer a cell. 4. 3D Reference  Whenever we refer a cell on a different sheet or a different workbook it is known as a 3D reference. 

Lecture 17  10:40  
You can use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range. In this lecture we will learn how to use Vlookup with False or Exact Match. Whenever we have values exactly available in our lookup range we can use Vlookup with False. This will help us to get the final result. 

Lecture 18  05:20  
In the earlier lecture we have learnt how to use Vlookup with False when we have exact lookup values available but what if, we have some approximate values available for lookup. In that case we can use Vlookup with True. We can either choose True from the dropdown list or we can write 1 in that argument. Both will work fine. 

Lecture 19  05:19  
In this lecture we will cover how to use vlookup with Mixed Reference. Let me tell you guys this will help a lot in saving time while working because a number of times we face a situation in which we have to lookup the values from a data range but we have to keep either column or row to be freeze for further references. If you are not clear about how to use Mixed Reference then i'll advice you to please go through with the first lecture again and fully verse with references part and then jump into this lecture. 

Lecture 20  05:18  
With this lecture i assume that you know how to use a logical function like "IF". Let me just give you a brief about IF function that, as per its syntax i.e. (logical_test, [value if true], [value of false]) it judges the condition we give it and on the basis of that it apply the result e.g. if i toss a coin, it will be considered as a "logical_test" , now if i demand heads so this is my condition so if this condition will become true than excel ask us that what we want it to show. It means that i demanded heads so i say that if head comes then this formula will show "You Win" otherwise "You loose" so i'll assign "You Win" to [Value if true] and "You loose" to [Value of false]. So we will club this function with Vlookup and tell excel to lookup values on the basis of some conditions. 

Lecture 21  04:03  
The Microsoft Excel HLOOKUP function searches for value in the top row of table_array and returns the value in the same column based on the index_number The syntax for the Microsoft Excel HLOOKUP function is: HLOOKUP( value, table_array, index_number, [not_exact_match] ) value is the value to search for in the first row of the table_array. table_array is two or more rows of data that is sorted in ascending order. index_number is the row number in table_array from which the matching value must be returned. The first row is 1. not_exact_match is optional. It determines if you are looking for an exact match based on value. Enter FALSE to find an exact match. Enter TRUE to find an approximate match, which means that if an exact match if not found, then the HLOOKUP function will look for the next largest value that is less than value. If this parameter is omitted, HLOOKUP will return an approximate match. 

Lecture 22  04:11  
In this lecture we will see how to use lookup. The Microsoft Excel LOOKUP function returns a value from a range (one row or one column) or from an array. There are 2 different syntaxes for the LOOKUP function: In Syntax #1, the LOOKUP function searches for value in the lookup_range and returns the value in the result_range that is in the same position. The syntax for the Microsoft Excel LOOKUP function is: LOOKUP( value, lookup_range, [result_range] ) value is the value to search for in the lookup_range. lookup_range is a single row or single column of data that is sorted in ascending order. The LOOKUP function searches for value in this range. result_range is optional. It is a single row or single column of data that is the same size as the lookup_range. The LOOKUP function searches for the value in the lookup_range and returns the value from the same position in the result_range. If this parameter is omitted, the LOOKUP function will return the first column of data. 

Lecture 23  09:27  
When deciding between which vertical formula to use in excel, the majority of Excel experts agree that Index & Match is a better formula than Vlookup. However, many people still resort to using VLOOKUP because it’s a simpler formula. One major driver of this problem is that most people still don’t fully understand the benefits of switching from VLOOKUP, and without such an understanding, they are unwilling to invest the time to learn the more complex formula. In this lecture i'll explain you the advantage of using Index and Match togather. 

Section 8: Date & Time Functions  
Lecture 24  11:25  
In this Video we will try to find out that how excel store time and Date. 

Lecture 25  04:41  
Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers, and all dates are manipulated by using this system.


Lecture 26  01:39  
Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers, and all dates are manipulated by using this system.


Lecture 27  03:07  
Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers, and all dates are manipulated by using this system.


Lecture 28  02:04  
Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers, and all dates are manipulated by using this system.


Lecture 29  03:24  
The TODAY function returns the serial number of today's date based on your system clock and does not include the time. TheNOW function returns the serial number of today's date and includes the time. How Dates Are Sorted Based on Serial Number
In Excel, dates are sorted based on the serial number of the date, instead of on the displayed number. Therefore, when you sort dates in Excel, you may not receive the results you expect. 

Lecture 30  04:02  
Instead of entering data manually on a worksheet, you can use the Auto Fill feature to fill cells with data that follows a pattern or that is based on data in other cells. 

Lecture 31  01:40  
Using Keyboard shortcuts will help us in dealing with Date & Time functions more gracefully. 

Lecture 32  02:13  
Formatting Time for Hours will really help in entering Time in excel cells. 

Lecture 33  02:45  
One can easily customize the Date formatting as per our individual requirement very easily. 

Section 9: Financial Functions  
Lecture 34  03:40  
The Excel PMT function computes the periodic payments for a loan, assuming constant payments and a constant interest rate. The syntax of the PMT function is:


Lecture 35  03:08  
If you want, you can use the IPMT or the PPMT function to compute the amount of interest paid each month toward the loan and the amount of the balance paid down each month (called the payment on the principal). To determine the interest paid each month, use the IPMT function. The syntax of the function is:
Except for the per argument, the arguments for the IPMT function are the same as for the PMT function. The perargument indicates the period number for which you're computing the interest. 

Lecture 36  04:08  
Similarly, to determine the amount paid toward the principal each month, use the PPMT function. The syntax of the PPMT function is:


Lecture 37  02:26  
Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value. SyntaxRATE(nper, pmt, pv, [fv], [type], [guess]) 

Lecture 38  03:05  
Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. SyntaxNPER(rate,pmt,pv,[fv],[type]) 

Lecture 39  02:24  
Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender. SyntaxPV(rate, nper, pmt, [fv], [type]) 

Lecture 40  12:34  
In this we will prepare a Loan table. In the Loan table we will able to determine that in which period how much Principal and how much interest we have paid and how much principal left for rest of the months. 

Lecture 41  05:23  
SLN Method:
DB Method:


Section 10: Misc. Functions  
Lecture 42  04:03  
These miscellaneous functions includes : MIN MAX SMALL LARGE 

Section 11: Conclusion  
Lecture 43  00:35  
I hope you have enjoyed the tutorial, if you have any query you can just reach me out through Udemy msgbox itself or through the website www.nurturetechacademy.in. Thanks for Watching, Happy Learning, Lokesh Nurture Tech Academy 
Nurture Tech Academy (http://nurturetechacademy.in/) is Microsoft Authorised Testing Centre, providing blend of instructorled and online training programs in Basic Excel, Advanced Excel, VBAs, Macros, and other MS Office applications. It has a team of Microsoft Certified Trainers who are specialist in their respective fields.