Excel Formulas and Functions
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: Mike Thomas
- Topic:   Business Administration, Business Skills, Finance, Software, Taxation and Accounting
- Credit:   CPE 2.0
Although it is perfectly possible to use Excel without ever creating a formula or using a function, if you don't, you're missing out on a huge part of the application's power. In this 100-minute session, with the help of real-world examples, you'll learn about some of Excel's most important and most useful functions that will save you time, reduce errors and help you to construct powerful spreadsheets.
Your Benefits of Attending:
- Learning the difference between formulas and functions
- Understanding more than one way to do a LOOKUP
- Using functions to manipulate text
- Using the IF function to automate data entry
- Using functions to manipulate date and time entries
- Creating complex functions and breaking them apart
- Formula Overview 00:01:42
- Copying Formulas 00:15:22
- Absolute 00:22:25
- Functions 00:33:01
- SUM 00:34:44
- AVERAGE 00:39:43
- MAX 00:40:52
- COUNT/COUNTA 00:41:47
- Naming Cells 00:44:20
- Converting Case 00:50:40
- LEFT/RIGHT 00:55:38
- MID 00:58:49
- CONCATENATE 01:01:18
- LEN 01:17:37
- VLOOKUP FALSE 01:19:41
- IFERROR 01:37:14
- VLOOKUP TRUE 01:40:19
- Presentation Closing 01:43:32
- Absolute Reference 00:22:40
- AVERAGE 00:39:43
- Cell 00:09:29, 00:17:4300:44:48
- Cell Reference 00:44:41
- CONCATENATE 01:01:18
- COUNT 00:41:47
- COUNTA Function 00:41:47
- Flash Fill 01:16:08
- Formula 00:01:42, 00:15:22
- Formula Bar 00:25:26
- Function 00:01:48, 00:33:01
- IFERROR 01:35:45
- LEFT 00:55:38
- LEN 01:17:37
- MID Function 00:58:49
- Name Box 00:44:50
- PROPER 00:52:58
- RIGHT 00:55:38, 00:58:16
- SUM 00:34:44, 00:38:23
- Table Array 01:29:49
- Vlookup 01:19:51
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.
Formula: A formula is an expression which calculates the value of a cell.
Function: Functions are predefined formulas and are already available in Excel.
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.
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 Reference: A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. There are three types: Relative, Absolute, and Mixed
Name Box: The Name Box is the box to the left of the formula bar that displays the cell that is currently selected in the spreadsheet. If a name is defined for a cell that is selected, the Name Box displays the name of the cell. You can use the Name Box to define a name for a selected cell as well.
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.
PROPER: =PROPER The Microsoft Excel PROPER function sets the first character in each word to uppercase and the rest to lowercase. The PROPER function is a built-in function in Excel that is categorized as a String/Text Function. It can be used as a worksheet function (WS) in Excel.
IFERROR Function: Introduced in Excel 2007, the IFERROR function simplifies crafting formulas that may sometimes return an error, such as #N/A.
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.
COUNTA Function: The COUNTA function returns the number of blank cells within a given range of cells.
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
LEN Function: The Excel LEN function returns the length of a given text string as the number of characters. LEN will also count characters in numbers, but number formatting is not included.
Flash Fill: Flash Fill automatically fills your data when it senses a pattern. For example, you can use Flash Fill to separate first and last names from a single column, or combine first and last names from two different columns. Note: Flash Fill is only available in Excel 2013 and later.
LEFT Function: The Microsoft Excel LEFT function is a function which allows you to extract a substring from a string and starts from the leftmost character. This is a built-in function in excel which has been categorized as a String/Text Function.
MID Function: The Excel MID function extracts a given number of characters from the middle of a supplied text string. For example, =MID("apple",2,3) returns "ppl". Extract text from inside a string. The characters extracted. =MID (text, start_num, num_chars)
AVERAGE : Returns the average (arithmetic mean) of the arguments.
COUNT: Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers.
RIGHT Function : The Excel RIGHT function extracts a given number of characters from the right side of a supplied text string.
CONCATENATE Function : The CONCATENATE function in Excel is designed to join different pieces of text together or combine values from several cells into one cell.