Excel Agility: Breaking Down Formulas
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Finance, Software, Taxation and Accounting
- Credit: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
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.
Topics Covered:
- 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.
Level:
Intermediate
Format:
Self-Study
Instructional Method:
On-Demand Webcast
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
Experience with Excel
Advance Preparation:
None
Introduction
Excel Versions 00:00:33
Formula Bar is Expandable 00:01:13
Viewing Two Worksheets at Once 00:03:39
Viewing Two Worksheets at Once (cont.) 00:09:21
Color-Coded Formulas 00:11:21
Safely Making Copies of Formulas 00:14:35
Evaluate Formulas 00:17:46
Use F9 to Calculate Part of a Formula 00:23:53
N Function 00:29:10
Show Formulas Feature 00:33:11
FORMULATEXT Function 00:36:57
Initiating the Formula Comment Tool 00:39:52
Unhide Personal Macro Workbook 00:45:01
Edit the Formula Comment Macro 00:45:35
Copy/Paste Formula Comment Code 00:47;16
Test the Formula Comment Macro 00:49:04
Shorten Worksheet Names 00:50:43
Create Range Names from Selection 00:53:10
Use in Formula Feature 00:58:02
Appy Range Names to Formulas 01:00:47
Table Feature 01:07:24
Table Formulas: Column Names vs. Cell References 01:12:08
Formula Management within a Table 01:13:29
Remove Table from Worksheet 01:18:04
Trace Precedents 01:19:52
Trace Dependents 01:24:40
Trace Error Command 01:25:57
Formula Auditing Keyboard Shortcuts 01:28:29
Types of Formula Errors 01:30:12
Introduction to IFERROR 01:31:45
ISNUMBER / ISTEXT 01:34:35
SUMPRODUCT / ISERROR 01:37:37
Conclusion 01:39:34
- #DIV / 0! 01:30:26
- #N/A Error 01:30:31, 00:37:28
- #NAME? 01:30:37
- #NULL! 01:30:56
- #NUM! 01:31:10
- #REF! Error 01:31:18
- #VALUE! 01:27:18, 01:31:28
- Ctrl-C 00:15:24, 00:41:34
- Ctrl-V 00:15:49
- CUMIPMT 00:1:31
- Evaluate Formulas 00:17:49, 00:18:33
- F9 Key 00:24:04
- Formula Bar 00:01:16
- FORMULATEXT 00:37:03
- IFERROR 01:31:50
- ISERROR 01:37:55
- ISNUMBER 01:35:27
- ISTEXT 01:35:42
- Macro 00:40:30
- N Function 00:29:41
- Personal Macro Workbook 00:41:57
- PMT Function 00:16:29, 00:26:44
- Print Titles 00:34:41
- Range Names 00:53:15, 01:00:55
- Ribbon 00:09:37
- Step In 00:19:25
- Step Out 00:19:26
- SUMPRODUCT 01:37:51
- Table Feature 01:07:27
- Trace Dependents 01:24:554
- Trace Error 01:25:59
- Trace Precedents 01:19:56
- Undo Command 00:25:08
- Use in Formula 00:58:07
- Visual Basic Editor 00:45:543
- Worksheets 00:03:42
#DIV / 0!: Microsoft Excel shows the #DIV/0! error when a number is divided by zero (0). It happens when you enter a simple formula like =5/0, or when a formula refers to a cell that has 0 or is blank, as shown in this picture.
#N/A Error: Excel displays this error when a lookup function, such as VLOOKUP or MATCH, cannot return the requested information.
#NAME?: When your formula has a reference to a name that is not defined in Excel, you will see the #NAME? error.
#NULL! : #NULL! error values occur when the two or more cell references are separated incorrectly or unintentionally by a space in a formula. In Excel formulas, the space character is used as the intersect operator, which means it is used when listing two or more intersecting or overlapping ranges of data.
#NUM! : Excel shows this error when a formula or function contains numeric values that aren't valid. This often happens when you've entered a numeric value using a data type or a number format that's not supported in the argument section of the formula.
#REF! Error: Excel displays this error when a formula contains an invalid cell reference. For instance, Excel’s VLOOKUP function may return #REF! if the col_index_num argument is incorrect. Other formulas may return #REF! if a user deletes one or more columns and Excel can’t adjust the cell references properly.
#VALUE!: #VALUE is Excel's way of saying, "There's something wrong with the way your formula is typed. Or, there's something wrong with the cells you are referencing." The error is very general, and it can be hard to find the exact cause of it.
CUMIPMT : The CUMIPMT worksheet function enables you to calculate the interest expense (or earnings) on a loan for as little as one period, such as a month or year, or as long as the entire length of the loan.
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.
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.
Print Titles: Excel 2013's Print Titles feature enables you to print particular row and column headings on each page of the report. Print titles are important in multipage reports where the columns and rows of related data spill over to other pages that no longer show the row and column headings on the first page.
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.
Undo Command: The Undo feature in Excel 2010 can quickly correct mistakes that you make in a worksheet. The Redo button lets you “undo the Undo.” The Undo button appears next to the Save button on the Quick Access toolbar, and it changes in response to whatever action you just took; the Redo button becomes active whenever you use Undo.
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.
