Your cart is empty!
Excel Agility: Mastering Advanced Formulas
Human Resources
S2R2024178
100 Minutes
Friday, January 19, 2024
- Wednesday, January 31, 2024
Description
Overview of the webinar
Excel formulas can sometimes grow out of control, or users often inherit spreadsheets from others that they’re to take ownership of. In this insightful presentation, you’ll learn from Excel expert David Ringstrom, CPA, various ways to make sense of complicated formulas in Excel spreadsheets. David shares an abundance of tricks you’ll have at your disposal to quickly decipher even the most complex formulas.
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws your attention to any differences in Excel 2013, 2010, or 2007 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.
Who Would Be Interested in This Course :
Practitioners seeking to gain better control over complicated worksheet formulas in Excel.
Your Benefits of Attending :
• Auditing portions of a formula by using the F9 key to temporarily convert part of a formula to a value.
• Determining whether it’s safe to edit or delete a cell by way of the Trace Dependents feature.
• Displaying all formulas in a worksheet at once with the Show Formulas feature.
• Giving yourself more room to work by expanding the formula bar when needed.
• Identifying other cells a formula relies on by way of the Trace Precedents feature.
• Leveraging Excel’s color coding to detect cells related to a formula, especially in Excel 2013 and later.
• Making copies of formulas safely to provide you with a fallback position.
• Shortening worksheet names, even temporarily, to make formulas easier to comprehend.
• Stepping through formulas in slow motion with the Evaluate Formulas feature.
• Utilizing keyboard shortcuts to identify precedent and dependent worksheet cells.
• Utilizing the FORMULATEXT function in Excel 2013 and later to display a formula from one cell in another cell.
• Using the New Window and Arrange Windows commands to view two different worksheets simultaneously.
Learning Objectives :
• Define how to display all formulas in a worksheet at once with the Show Formulas feature.
• List the benefits of using range names.
• Recall how to use the F9 key to temporarily convert part of a formula to a value.
Table of Contents :
1• Introduction
2• Excel Versions 00:00:35
3• Formula Bar is Expandable 00:01:14
4• Viewing Two Worksheets at Once 00:03:40
5• Viewing Two Worksheets at Once (cont.) 00:09:22
6• Color-Coded Formulas 00:11:23
7• Safely Making Copies of Formulas 00:14:37
8• Evaluate Formulas 00:17:47
9• Use F9 to Calculate Part of a Formula 00:23:55
10• N Function 00:29:10
11• Show Formulas Feature 00:33:12
12• FORMULATEXT Function 00:36:58
13• Initiating the Formula Comment Tool 00:39:55
14• Unhide Personal Macro Workbook 00:45:03
15• Edit the Formula Comment Macro 00:45:37
16• Copy/Paste Formula Comment Code 00:47:17
17• Test the Formula Comment Macro 00:49:05
18• Shorten Worksheet Names 00:50:44
19• Create Range Names from Selection 00:53:12
20• Use in Formula Feature 00:58:04
21• Appy Range Names to Formulas 01:00:51
22• Table Feature 01:07:26
23• Table Formulas: Column Names vs. Cell References 01:12:10
24• Management within a Table 01:13:31
25• Remove Table from Worksheet 01:18:06
26• Trace Precedents 01:19:54
27• Trace Dependents 01:24:42
28• Trace Error Command 01:25:59
29• Formula Auditing Keyboard Shortcuts 01:28:30
30• Types of Formula Errors 01:30:14
31• Introduction to IFERROR 01:31:47
32• ISNUMBER / ISTEXT 01:34:38
33• SUMPRODUCT / ISERROR 01:37:39
Index :
• Ctrl-C 00:15:27
• Ctrl-V 00:15:50
• Evaluate Formulas 00:17:47, 00:19:17
• F9 Key 00:24:05
• Formula Bar 00:01:18
• FORMULATEXT Function 00:36:58
• IFERROR 01:31:52
• ISERROR 01:37:58
• ISNUMBER 01:35:30
• ISTEXT 01:35:45
• Macro 00:40:31
• N Function 00:29:11
• Personal Macro Workbook 00:45:07
• PMT Function 00:26:02, 00:26:45
• Range Names 00:53:18
• Ribbon 00:09:38
• Step In 00:19:26
• Step Out 00:19:26
• SUMPRODUCT 01:37:53
• Table Feature 01:07:26
• Trace Dependents 01:24:56
• Trace Error Command 01:25:59
• Trace Precedents 01:19:57
• Use in Formula 00:58:31
• Visual Basic Editor 00:45:54
• Worksheets 00:03:43
Key Terms :
Ctrl-C: Copies the selection to the clipboard
Ctrl-V: Pastes the clipboard contents
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.
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 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.
IFERROR Function: Introduced in Excel 2007, the IFERROR function simplifies crafting formulas that may sometimes return an error, such as #N/A.
ISERROR: The ISERROR function checks whether a value is an error and returns TRUE or FALSE. The Excel ISERROR function returns TRUE for any error type excel generates, including #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! You can use ISERROR together with the IF function to test for errors and display a custom message or run a different calculation when found.
ISNUMBER : Use the ISNUMBER function to check if a value is a number. ISNUMBER will return TRUE when value is numeric and FALSE when not.
ISTEXT: ISTEXT will return TRUE when value is text.
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.
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.
Personal Macro Workbook: A hidden workbook that typically serves as a repository for macros you wish to always be available in any Excel workbook you have open.
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.
Ribbon: The "ribbon" is the strip of buttons and icons located above the work area that was first introduced in Excel 2007. The ribbon replaces the menus and toolbars found in earlier versions of Excel. Above the ribbon are a number of tabs, such as Home, Insert, and Page Layout.
SUMPRODUCT: The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products.
Step In: Allows you to examine the formula represented by the underlined cell reference. This is not available when the cell being referenced is in another workbook. Allows you to view the actual contents of any referenced cells before applying it to the formula by displaying it in a separate box.
Step Out: Applies the argument to the function and combines the process.
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.
Trace Dependents: A formula auditing feature that enables you to identify which cell or cells at are linked to the active cell.
Trace Error : Trace Error in Excel lets you trace arrows back to cells referenced by a formula if it displays an error. The Trace Error tool is available when auditing a worksheet within a workbook. Note that the formula cell that you select must contain an error to use the Trace Error tool in Excel.
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.
Use in Formula: As with manually typing a cell or a range name, you can use this method anywhere you would normally enter a range or cell reference in a formula. Instead of typing the name, you simply select the Use in Formula command and then select the desired defined name from the drop-down menu. You can access the Use in Formula command by selecting FORMULAS?Defined Names?Use in Formula.
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.
Speaker
David H Ringstrom
David H. Ringstrom, CPA, is a nationally recognized Microsoft Excel expert. He is the president and owner of Accounting Advisors, Inc. based in Atlanta, Georgia. David founded Accounting Advisors in 1991 as a consulting-services business, later he began teaching continuing education classes as well. His mission since is to offer quality training and consulting services on Microsoft Excel via live webcasts, on-demand self-study webcasts, and in-house engagements. David has taught hundreds of webinars on Excel and other topics, in addition to speaking at conferences and in-house engagements. More than 24 providers, located throughout the country as well as overseas, now look to David for their Excel and accounting software training needs. More than 24 providers, located throughout the country as well as overseas, now look to David for their Excel and accounting software training needs.
David’s Excel courses cover the gamut of the software’s features and functions to provide CPAs as well as accounting and financial professionals the knowledge they need to work more efficiently and effectively in Excel. David is known for saying, “Either you work Excel, or it works you.” Based on this belief, he focuses on teaching users what they don’t know but should know about Excel.
His comprehensive yet easy to understand presentations cover Excel 2019, 2016, 2013, and 2010. David’s webcasts are fast-paced, and he welcomes attendees’ questions. In addition, his detailed handouts and slides serve as handy reference tools students can fall back on after participating in his webcasts or taking his self-study courses.