Excel Agility: Mastering Advanced Formulas
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic:   Business Administration, Business Skills, Finance, Software, Taxation and Accounting
- Credit:   CPE 2.0
Excel expert David Ringstrom, CPA, shows you various ways to make sense of complicated Excel formulas in this insightful presentation. Sometimes your formulas can grow out of control, or, more likely, you’ve inherited spreadsheets from others that you’re to take ownership of. You’ll have many tricks at your disposal to quickly decipher even the most complex Excel formulas after attending this webcast.
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.
Topics typically covered:
- 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.
- Creating bookmarks and nicknames for key inputs by way of the Create Names from Selection feature.
- Determining whether formulas within tables use cell references or field names.
- 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.
NASBA Field of Study:
Computer Software & Applications (2 hours)
Experience with Lookup Formulas Recommended
- Please Ask Questions Today 00:02:13
- Excel Versions 00:03:56
- Show Formulas Feature 00:05:20
- Shorten Worksheet Names 00:12:19
- Create Range Names from Selection 00:16:17
- Appy Range Names to Formulas 00:22:26
- Use in Formula Feature 00:27:37
- Table Feature 00:30:50
- Table Formulas: Column Names vs. Cell References 00:39:46
- Formula Management within a Table 00:43:37
- Remove Table from Worksheet 00:47:21
- MINIFS Function (Excl 2019+) 00:56:45
- SORT Function (Microsoft 365 Only) 01:02:12
- FILTER Function (Microsoft 365 Only) 01:08:03
- Editing Dynamic Array Functions 01:10:49
- Function Arguments Dialog Box 01:15:27
- Excel For Mac Function Builder 01:17:40
- Evaluate Formulas 01:18:04
- Use F9 to Calculate Part of a Formula 01:20:02
- N Function 01:22:55
- FORMULATEXT Function 01:26:05
- Initiating the Formula Comment Tool 01:28:14
- Unhide Personal Macro Workbook 01:30:42
- Edit the Formula Comment Macro 01:31:10
- Copy/Paste Formula Comment Code 01:32:37
- Test the Formula Comment Macro 01:32:07
- Trace Precedents 01:36:45
- Formula Auditing Keyboard Shortcuts 01:
- Types of Formula Errors 01:38:42
- Trace Error Command 01:39:14
- Presentation Closing 01:41:52
- Array Formula 01:01:07
- Cell 00:08:10
- Column 00:08:01
- Create from Selection Command 00:17:20
- Dialog Box 01:15:31
- Dynamic Array Function 01:02:42
- Evaluate Formulas 01:18:08
- F9 Key 01:20:14
- FILTER Function 01:08:05
- Formula 00:05:28, 00:12:45, 00:39:56
- Formula Bar 01:23:09
- FORMULATEXT Function 01:26:20
- Macro 01:28:20
- Macro Recorder 01:28:31
- MINIFS Function 00:56:48
- Name Box 00:17:46
- N Function 01:23:00
- PMT Function 00:15:15
- Range Names 00:16:17, 00:22:54
- SORT Function 01:03:25
- SUBTOTAL 00:41:29
- SUM 00:41:20
- Table Feature 00:30:57
- Total Row 00:33:44
- Trace Precedents 01:36:54
- Worksheets 00:07:18
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.
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.
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.
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.
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.
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.
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.
Trace Precedents: A formula auditing feature that enables you to identify which cell or cells that the active cell is linked to elsewhere in the workbook.
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.
Sort Function: =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.
SUBTOTAL: A worksheet function that allows you to sum, average, count, and other otherwise analyze data on just the visible cells within a given range.
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.
Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.
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.
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.
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.
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
Create from Selection Command: Quickly name a range or name multiple ranges at once using the "Create from Selection " command.
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.
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.
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.
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.
Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.
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.