Excel Formulas and Functions
Please see below for additional instructions and information regarding this program.
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.
AVERAGE : Returns the average (arithmetic mean) of the arguments.
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
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.
COUNT: Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers.
COUNTA Function: The COUNTA function returns the number of blank cells within a given range of cells.
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.
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.
IFERROR Function: Introduced in Excel 2007, the IFERROR function simplifies crafting formulas that may sometimes return an error, such as #N/A.
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.
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.
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)
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.
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.
RIGHT Function : The Excel RIGHT function extracts a given number of characters from the right side of a supplied text string.
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.
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.
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 to users. He is a subject matter expert in a range of Microsoft technologies including Microsoft Office, Lync/Skype for Business and SharePoint. He has extensive experience of working with Cloud-based ... View Full Profile
Aurora Training Advantage is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.
For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or firstname.lastname@example.org.
You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.