Excel Formulas and Functions

1. Introduction
2. Formula and Functions Overview 00:02:42
3. Date Formulas 00:09:35
4. Brackets/Parenethesis 00:11:28
5. Absolute Reference 00:18:25
6. Functions - Sum And Average 00:31:36 
7. The SUM Function 00:41:59
8. Dates 00:50:36
9. IF Function 01:05:03
10. LOOKUP Functions 01:25:30
11. XLOOKUP 01:26:07
12. VLOOKUP 01:34:02
13. Presenter’s Closing And Email Address 01:41:28
14. Presentation Closing 01:42:02

• 

  Mike Thomas

  Mike Thomas has worked in the IT training business for 26 years. His expertise and experience covers designing and delivering training courses, creating written training materials (Quick Reference Guides and step-by-step tutorials), recording and editing video-based tutorials and providing support t [...]

  View Full Profile

• Absolute Reference 00:18:25
• AVERAGE 00:12:29, 00:35:44, 00:39:52, 00:46:48
• Cell 00:04:16, 00:06:44, 00:20:10, 00:59:35, 01:13:55
• Cell Range 00:42:29
• Column 01:05:27, 01:30:47, 01:38:50
• Format 00:50:50
• Formula 00:00:06, 00:03:12, 00:05:29, 00:09:17, 00:32:03, 00:56:36, 01:11:24, 01:18:09, 01:23:23, 01:36:01
• Formula Bar 00:06:49
• Function 00:00:07, 00:31:36, 00:34:51, 00:38:34, 00:50:29, 00:58:37
• HLOOKUP 01:25:59
• IF Function 01:05:03, 01:11:33
• LOOKUP 01:25:30, 01:32:51
• Parameter 00:58:26, 01:36:04
• Row 01:30:50
• SUM 00:35:43, 00:38:26
• VLOOKUP 01:25:54, 01:34:02, 01:38:39
• XLOOKUP 01:26:00, 01:30:06, 01:33:54

AVERAGE : Returns the average (arithmetic mean) of the arguments.

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.

Cell Range: A group of cells is known as a cell range. Rather than a single cell address, you will refer to a cell range using the cell addresses of the first and last cells in the cell range, separated by a colon. For example, a cell range that included cells A1, A2, A3, A4, and A5 would be written as A1:A5.

Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.

Format: When we format cells in Excel, we change the appearance of a number without changing the number itself. We can apply a number format (0.8, $0.80, 80%, etc) or other formatting (alignment, font, border, etc). By default, Excel uses the General format (no specific number format) for numbers.

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.

HLOOKUP: HLOOKUP is an Excel function to lookup and retrieve data from a specific row in table. The "H" in HLOOKUP stands for "horizontal", where lookup values appear in the first row of the table, moving horizontally to the right. HLOOKUP supports approximate and exact matching, and wildcards (* ?) for finding partial matches.

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.

LOOKUP: The Microsoft Excel LOOKUP function returns a value from a range (one row or one column) or from an array. The LOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) in Excel.

Parameter: A parameter is a piece of information you supply to a query right as you run it. Parameters can be used by themselves or as part of a larger expression to form a criterion in the query. You can add parameters to any of the following types of queries: Select. Crosstab.

Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.

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

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.

XLOOKUP: The XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. Where a valid match is not found, return the [if_not_found] text you supply.