Excel Agility: Mastering Advanced Formulas
Webinar Details $219
- Webinar Date: August 23, 2022
- Webinar Time: 2:00pm - 3:40pm EDT live
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Finance, Software, Taxation and Accounting
- Credit: CPE 2.0, ATATX 1.5
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Microsoft 365 (formerly Office 365) version of Excel. David draws your attention to any differences in the older versions of Excel (2019, 2016, 2013, and earlier) during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.
Microsoft 365 is a subscription-based product that provides new feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don't change. Perpetual licensed versions have year numbers, such as Excel 2019, Excel 2016, and so on.
Who should attend:
Practitioners seeking to have better control over writing and understanding worksheet formulas in Excel.
• Adding a macro to Excel that adds the ability to display any formula in a cell comment.
• Auditing portions of a formula by using the F9 key to temporarily convert part of a formula to a value.
• Create your own custom worksheet functions without writing any code in Microsoft 365 with the LAMBDA function.
• Creating bookmarks and nicknames for key inputs by way of the Create Names from Selection feature.
• Displaying all formulas in a worksheet at once with the Show Formulas feature.
• Displaying subsets of data dynamically by way of the new FILTER worksheet function.
• Identifying other cells a formula relies on by way of the Trace Precedents feature.
• Identifying the various # sign errors Excel formulas can return.
• Learning about the MINIFS function available in Excel 2019 and Office 365.
• Learning how the Table feature empowers you to improve the integrity of Excel spreadsheets.
• Making notes in the formula bar or preserving prior versions of formulas with the N function.
• List the benefits of using range names.
• Apply the Trace Precedents and Trace Error features to identify linked cells.
• Apply the SUMIF function to summarize data and the SUMIFS function to sum values.
Instructional Method: Self-study
NASBA Field of Study: Computer Software & Applications (2 hours)
Program Prerequisites: Experience with Lookup Formulas Recommended
Advance Preparation: None
- Please Ask Questions Today 00:02:05
- Excel Versions 00:03:29
- Show Formulas Feature 00:05:21
- Shorten Worksheet Names 00:10:25
- Create Range Names from Selection 00:14:03
- Appy Range Names to Formulas 00:18:12
- Use in Formula Feature 00:21:48
- LET Function (Microsoft 365) 00:26:40
- LAMBDA Function (Microsoft 365) 00:37:25
- LAMBDA Function (Microsoft 365) Cont’d 00:43:03
- LAMBDA Function (Microsoft 365) Cont’d 00:47:45
- Table Feature 00:48:07
- Formula Management within a Table 00:54:30
- Remove Table from Worksheet 01:00.:02
- MINIFS Function (Excl 2019+) 01:02:24
- SORT Function (Microsoft 365 Only) 01:07:04
- FILTER Function (Microsoft 365 Only) 01:10:09
- Editing Dynamic Array Functions 01:12:50
- Evaluate Formulas 01:14:16
- Use F9 to Calculate Part of a Formula 01:18:00
- N Function 01:25:20
- FORMULATEXT Function 01:27:19
- Initiating the Formula Comment Tool 01:29:59
- Unhide Personal Macro Workbook 01:34:19
- Edit the Formula Comment Macro 01:35:09
- Copy/Paste Formula Comment Code 01:36:21
- Test the Formula Comment Macro 01:36:43
- Trace Precedents 01:37:47
- Types of Formula Errors 01:39:31
- Trace Error Command 01:40:12
- Thank You For Attending! 01:42:48
- Presentation Closing 01:43:14
- Array Formula 01:06:51
- Cell 00:05:37, 00:14:21
- Column 00:06:53, 00:14:45
- Create from Selection Command 00:14:52
- Dialog Box 00:14:57, 00:17:17, 01:31:37
- Dynamic Array Function 01:07:05, 01:13:00
- Evaluate Formulas 01:14:19
- F9 Key 01:18:16
- FILTER Function 01:10:15
- Formula 00:01:24, 00:05:30
- FORMULATEXT Function 01:27:24
- IFERROR Function 00:27:42, 00:34:35
- LAMBDA Function 00:37:27, 00:40:46, 00:45:43
- LET Function 00:26:45
- Macro 01:31:31
- Macro Recorder 01:31:21
- Microsoft 365 00:03:35, 00:26:37
- MINIFS Function 01:02:09
- N Function 01:25:24
- Page Layout Menu 00:06:42
- PMT Function 00:22:26
- Range Names 00:14:06, 00:19:04, 00:22:04, 01:25:53
- Row 00:06:52, 00:48:59
- Slicer 00:53:03
- SORT Function 01:07:24
- Table Feature 00:48:09
- Total Row 00:49:18
- Trace Precedents 01:
- Visual Basic Editor 01:35:21
- Worksheets 00:19:14
- XLOOKUP 00:38:20
Array Formula: In Excel, an Array Formula allows you to do powerful calculations on one or more value sets. The result may fit in a single cell or it may be an array. An array is just a list or range of values, but an Array Formula is a special type of formula that must be entered by pressing Ctrl+Shift+Enter.
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.
Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.
Create from Selection Command: Quickly name a range or name multiple ranges at once using the "Create from Selection " command.
Dialog Box: A dialog box in Excel is a screen where you input information and make choices about different aspects of the current worksheet or its content, such as data, charts, and graphic images.
Dynamic Array Function: Dynamic Arrays will make certain formulas much easier to write. You can now filter matching data, sort, and extract unique values easily with formulas. Dynamic Array formulas can be chained (nested) to do things like filter and sort. Formulas that return more than one value will automatically spill.
Evaluate Formulas : A feature available on the Formulas tab of Excel 2007 and later and the Tools/Formula Auditing menu in Excel 2003 and earlier that allows you to step through a formula in slow motion.
F9 key: You can use the F9 key to evaluate parts of your formulas. Highlight the portion of the formula that you want to resolve and press the F9 key.Always press the ESC key afterward. Be careful not to press the Enter key as this will result in your formula being permanently changed. This can be used to see the values that a range is actually returning.
FILTER Function: The Excel FILTER function returns a range filtered on criteria you define. It can also handle multiple AND/OR criteria. array is the range or array containing the values you want filtered. include is the logical test that returns a Boolean array (TRUE/FALSE) the same height or width as the array.
FORMULATEXT: The Excel FORMULATEXT function returns a formula as a text string from given reference. You can use FORMULATEXT to extract a formula as text from a cell. If you use FORMULATEXT on a cell that doesn't contain a formula, you'll get an #N/A error.
Formula: A formula is an expression which calculates the value of a cell.
IFERROR Function: Introduced in Excel 2007, the IFERROR function simplifies crafting formulas that may sometimes return an error, such as #N/A.
LAMBDA Function: The LAMBDA function provides a way to create a custom function in Excel. Once defined and named, a LAMBDA function can be used anywhere in a workbook. LAMBDA functions can be very simple, or quite complex, stringing together many Excel functions into one formula. A custom LAMBDA function does not require VBA or macros.
LET Function: The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula. These names only apply within the scope of the LET function. Similar to variables in programming, LET is accomplished through Excel’s native formula syntax.
MINIFS Function: The Excel MINIFS function returns the smallest numeric value that meets one or more criteria in a range of values. MINIFS can be used with criteria based on dates, numbers, text, and other conditions. MINIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.
Macro: One or more lines of programming code that automate tasks. The Macro Recorder allows users to automate tasks without seeing the underlying programming code.
Macro Recorder: A feature in Excel that allows you to transcribe actions you take in Excel into programming code.
Microsoft 365: Microsoft 365, formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.
N Function: A worksheet function that enables you to store notes in the formula bar. Anything contained within the N function typically evaluates to zero.
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.
Page Layout Menu: The Page Layout Tab holds all the options that allow you to arrange your document pages just the way you want them. You can set margins, apply themes, control of page orientation and size, add sections and line breaks, display line numbers, and set paragraph indentation and lines.
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.
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.
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.
Slicer: You can insert slicers in Excel to quickly and easily filter pivot tables. Slicers were introduced in Excel 2010, and they make it easy to change multiple pivot tables with a single click
Table Feature : The Table feature in Excel 2007 and later is an improvement on the List feature in Excel 2003 and earlier. The Table feature provides enhancements that make it much easier to analyze lists of data.
Total Row: A Total row appears below the data where each column has access to several automatic formulas. The default selection for the Total Row is none, meaning no function is selected when you first turn on the Total Row on your Table.
Trace Dependents: A formula auditing feature that enables you to identify which cell or cells at are linked to the active cell.
Visual Basic Editor : Excel’s development interface that can be used to edit and create macros, user forms, class modules, custom worksheet functions, and other enhancements to Excel’s functionality. The programming interface for Microsoft Excel that can be accessed by way of the Visual Basic command on Excel’s Developer tab or by pressing Alt-F11.
Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.
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.