1. Introduction

2. Formula Overview 00:02:19

3. Copying Formulas 00:10:09

4. Absolute 00:13.23

5. Functions  00:19:36 

1. PMT 00:22:45

2. Statistical 00:28:06

3. SUM 00:32:07

4. AVERAGE 00:32:50

5. IFERROR 00:36:59

2. Lookup Functions 

1. Vlookup 00:42:08

1. False 00:42:05

2. True 00:59:44

3. Table Format 01:11:14

4. Logical Functions 01:19:02

1. IF 01:19:09

2. COUNTIF 01:31:56

6. Circular Reference 01:36:28

7. Removing Duplicates 01:39:28

8. Conclusion 01:40:12

• #N/A Error 00:50:00
• Absolute 00:17:08, 00:54:58
• Cell(s) 00:12:24
• Circular References 01:36:28
• Conditional Formatting 01:35:39
• COUNTIF 01:31:56
• Formula 00:02:39, 00:17:57
• Formula Bar 00:17:23
• Function 00:02:40, 00:19:36
• Fx Button 00:20:51
• IFERROR 00:36:59
• IF Function 01:19:09
• Named Range 01:02:26
• PMT Function 00:22:45
• Range Name 01:01:30
• SUM 00:32:07
• Table Array 00:45:59
• Table Format 01:11:14
• Vlookup 00:42:08, 01:15:09

#N/A Error: Excel displays this error when a lookup function, such as VLOOKUP or MATCH, cannot return the requested information.

Absolute Reference : Absolute references in Excel are a direct link to a specific cell or range of cells that remain fixed if you copy or drag the formula. Absolute references are represented by $ symbols. A $ before a column letter freezes the column, while a $ before the row number freezes the row number. You can freeze the column letter and/or row number when needed.

Cell: In spreadsheet applications, a cell is a box in which you can enter a single piece of data. The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells.

Circular References: A formula in a cell that directly or indirectly refers to its own cell is called a circular reference.

Conditional Formatting: A feature on Excel's Home menu that allows you to dynamically apply formatting such as colors, bolding, icons, data bars, and so on based on criteria that you specify for a given set of worksheet cells.

COUNTIF: Excel COUNTIF function is used for counting cells within a specified range that meet a certain criterion, or condition. For example, you can write a COUNTIF formula to find out how many cells in your worksheet contain a number greater than or less than the number you specify.

Formula: A formula is an expression which calculates the value of a cell.

Formula Bar: A toolbar at the top of the Microsoft Excel spreadsheet window that you can use to enter or copy an existing formula into cells or charts. It is labeled with function symbol (fx). By clicking the Formula Bar, or when you type an equal (=) symbol in a cell, the Formula Bar will activate.

Function: Functions are predefined formulas and are already available in Excel.

Fx Button: Excel Functions (fx) Excel has prewritten formulas called functions to help simplify making complicated calculations. A function takes a value or values, performs an operation, and returns a result to a cell. The values that you use with a function are called arguments.

IFERROR Function: Introduced in Excel 2007, the IFERROR function simplifies crafting formulas that may sometimes return an error, such as #N/A.

IF Function: Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false. So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.

Named Range : A descriptive name — such as Jan_Sales or June_Precip — that is attached to a specific cell or range of cells in a worksheet or workbook. Named ranges make it easier to use and identify data when creating charts, and in formulas.

PMT Function: The PMT function enables you to calculate a loan payment based on providing an interest rate, period of the loan, and amount to be borrowed or lent. The interest rate must be on the same footing as the term of the loan, so if the loan period is expressed in months, be sure to divide the interest rate by 12.

Range Names: A user-defined identifier for a cell or block of cells. Range names simplify formula writing and auditing, as the words SalesTax are readily identifiable within a formula as opposed to $B$1. Range names cannot contain spaces; must begin with a letter; be no longer than 255 characters in length; and can be comprised of only letters, numbers, underscores, periods, and slashes.

SUM: Microsoft Excel defines SUM as a formula that “Adds all the numbers in a range of cells”. This definition clearly points that Sum function has a job to add numbers and the arguments can be supplied using combinations of both numbers and range of cells. =SUM The SUM function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUM function can be entered as part of a formula in a cell of a worksheet

Table Array: A table array is one of the arguments used in Excel's lookup functions, such as VLOOKUP and HLOOKUP. For VLOOKUP (vertical lookup), the table_array must contain at least two columns of data. For HLOOKUP (horizontal lookup), the table_array must contain at least two rows of data.

Table Format: By using the Format as Table icon you instruct Excel to treat a table as a basic database. A table in Excel is a block of cells containing related data that have been formatted as a table

VLOOKUP: An Excel worksheet function that allows you to look up data from a list by specifying criteria, cell coordinates for the list, column number from which to return data, and an indication as to whether you want an exact or approximate match.