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:02:19
Copying Formulas 00:10:09
Table Format 01:11:14
Logical Functions 01:19:02
Circular Reference 01:36:28
Removing Duplicates 01:39:28
- #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.
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 email@example.com.
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.