# On Demand Webinar

## Excel Formulas and Functions

#### Webinar Details \$219

• Rated:
• Webinar Length: 100 Minutes
• Guest Speaker:   Mike Thomas
• Topic:   Business Administration, Business Skills, Finance, Software, Taxation and Accounting
• Credit:   CPE 2.0, ATATX 1.5, ATAOP 1.5

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
• Introduction
• Formula Overview 00:02:40
• Copying Formulas 00:05:58
• Absolute 00:09:21
• Part Absolute 00:13:53
• Functions 00:32:43
• SUM 00:33:10
• AVERAGE 00:33:21
• LEFT  00:33:29
• RIGHT  00:33:38
• UPPER 00:33:46
• LOWER 00:33:46
• PROPER 00:33:54
• Text Functions File 00:44:42
• CONCATENATE 00:44:48
• Formulas File 00:58:29
• Text to Columns Wizard 00:58:58
• Viewing All Functions 01:00:13
• Removing Duplicates 01:03:01
• SORT 01:04:45
• UNIQUE 01:04:49
• Logic Functions File
• IF Function 01:13:42
• SUMIF 01:21:39
• LEN 01:24:25
• Timesheet - Adding Dates and Times 01:29:33
• Lookup File 01:33:17
• VLOOKUP FALSE 01:33:20
• Presentation Closing 01:41:02
1. Absolute Reference 00:12:43
2. AVERAGE 00:33:21, 00:36:29
3. Cell 00:07:01, 00:30:36
4. Cell Reference 00:07:37, 00:11:10
5. CONCATENATE 00:45:19, 00:49:48, 01:27:34
6. Formula 00:01:26, 00:08:10, 00:32:49
7. Formula Bar 00:13:18, 01:10:20
8. Function 00:01:26, 00:32:43
9. FX Button 01:00:20, 01:10:17
10. IF Function 01:13:42, 01:15:16
11. Keyboard Shortcut 00:12:55
12. LEFT  00:33:29, 00:38:05
13. LEN 01:24:25
14. LOOKUP 01:33:16
15. LOWER 00:33:46
16. PROPER 00:33:54, 00:52:05
17. RIGHT  00:33:38, 00:40:01
18. SORT 01:04:45
19. SUM 00:33:10, 00:34:26
20. SUMIF 01:21:39
21. Table Array 01:36:47
22. Text to Columns Wizard 00:58:58
23. UNIQUE 01:04:49
24. UPPER 00:33:46, 00:40:44, 00:49:44
25. VLOOKUP 01:35:59

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.

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.

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

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.

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.

Keyboard Shortcut: A keyboard shortcut is a series of one or several keys that invoke a software program to perform a preprogrammed action. This action may be part of the standard functionality of the operating system or application program, or it may have been written by the user in a scripting language.

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.

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.

LOWER : =LOWER The Microsoft Excel LOWER function converts all letters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function. The LOWER function is a built-in function in Excel that is categorized as a String/Text Function.

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.

SORT: =Sort - Sorting is the process of arranging objects in a certain sequence or order according to specific rules. In spreadsheet programs such as Excel and Google Spreadsheets, there are several different sort orders available depending on the type of data you're sorting.

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.

Text to Columns Wizard: An Excel feature which allows users to separate data from a single column within an Excel spreadsheet into two or more columns, or to remove unnecessary data from within a column.

UNIQUE: =UNIQUE - The Excel UNIQUE function returns a list of unique values in a list or range.

UPPER: =UPPER The Microsoft Excel UPPER function allows you to convert text to all uppercase. The UPPER 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.

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.

### CPE Credit

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.