Excel Agility: Understanding Error Prompts
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Taxation and Accounting, Software, Finance
- Credit: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
Excel expert David Ringstrom, CPA, delves into the intricacies of Excel to give you the how and why behind error prompts you might frequently encounter while preforming seemingly simple actions. David also outlines in detail what actions to take when Excel appears to go awry.
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 use Excel more effectively by understanding and mitigating Excel error prompts.
Your Benefits of Attending:
- Understanding how to manage the Protected View prompt in Excel 2010 and later.
- Utilizing the Error Checking command to locate cells that contain errors within a worksheet.
- Using a simple macro to eliminate redundant prompts that appear when you save a file in text or .CSV format.
- Learning the nuances of circular references in Excel.
- Mastering the nuances—and the power—of Excel’s Transpose feature.
- Improving the integrity of spreadsheets with Excel’s VLOOKUP function.
- Discovering new worksheet functions available in Excel 2016.
- Resolving the issue that prevents you from being able to insert rows or columns.
- Using range names to streamline formulas and bookmark key inputs within a workbook.
- Determining at a glance whether a spreadsheet contains links to other workbooks.
- Improving the stability of Excel by deleting accumulations of temporary files in Windows.
Learning Objectives:
- Recall how to improve the odds of Excel creating a backup copy of your work so you can recover easily from program crashes.
- State why certain features, such as slicers, sparklines, and even charts, are disabled in some workbooks.
- Identify the worksheet function that eliminates extraneous spaces from text within a worksheet.
Level:
Basic
Format:
On-demand webcast
Instructional Method:
Self-study
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
None
Advance Preparation:
None
Introduction
Excel Versions 00:00:43
History Worksheet Name 00:01:46
Unable to Insert Rows/Columns 00:07:43
Too Many Different Cell Formats Error 00:17:54
Transpose Data 00:22:18
Types of Formula Errors 00:27:54
Worksheet Function Evolution 00:32:24
Manage Error Checking Prompts 00:34:59
Manage Error Checking Prompts (cont.) 00:39:39
Error Checking Commands 00:41:25
Trace Error Command 00:46:24
Introduction TO IFERROR 00:50:40
Spaces or Blank Cells and #VALUE! Error 00:55:51
Missing Data Triggers #N/A in VLOOKUP 00:59:28
Extraneous Spaces Triggers #N/A in VLOOKUP 01:04:56
Circular References 01:12:32
Locating Circular References 01:15:32
Diagnosing Broken Range Names 01:18:44
Confirming if a File Has Links 01:23:00
Another Place that Links Sometimes Hide 01:26:31
Data Security Prompts 01:28:18
Protected View 01:29:45
Protected View Settings 01:32:13
Macro Security Notification Prompts 01:34:28
Repairing Damaged Workbooks 01:35:19
Delete Temporary Files 01:36:39
Installing Microsoft Service Packs 01:40:07
Wayward Security Patches 01:40:42
Launch Excel In Safe Mode 01:41:53
Remove Unnecessary Add-Ins 01:43:04
Presentation Closing 01:44:14
- .XLK 00:17:54
- .XLS 00:17:54
- #DIV / 0! 00:28:14
- #N/A 00:28:34, 011:00:22
- #NAME? 00:29:53
- #NULL 00:31:08
- #NUM! 00:31:21
- #REF! 00:32:04
- #VALUE! 00:32:06, 00:55:59
- Add-Ins 01:43:04
- Circular Reference 01:12:32
- CSV File 01:
- Disk Clean-up 01:37:40
- Error Checking Prompts 00:34:59, 00:41:24
- IFERROR 00:50:40
- ISERROR 00:51:27
- Macro 01:34:28
- Name Manager 01:19:17, 01:27:28
- Range Names 01:18:46
- Safe Mode 00:42:03
- SUM 01:13:19
- Trace Error 00:46:24
- TRANSPOSE 00:22:52
- TRIM Function 01:06:12
- Visual Basic Editor 00:08:29, 00:11:17
- VLOOKUP 00:59:28
- XLStylesTool 00:19:22, 00:20:56
#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.
.XLK: This file extension connotes an Excel Backup Workbook generated by the Always Create Backup setting for a given workbook. This setting must be enabled on an individual workbook basis. Such files cannot be opened in Excel for Mac unless you change the file extension to XLS or XLSX.
.XLS: Spreadsheets compatible with Excel 2003 and earlier have a .XLS extension. Such spreadsheets can be used in Excel 2007 and later, but certain features will be disabled unless you convert the document to a newer format, such as .XLSX, .XLSM, or .XLSB.
Add-In: An Excel Add-In is a file (usually with an .xla or .xll extension) that Excel can load when it starts up. The file contains code (VBA in the case of an .xla Add-In) that adds additional functionality to Excel, usually in the form of new functions.
Circular References: A formula in a cell that directly or indirectly refers to its own cell is called a circular reference.
Disk Clean-up: Disk Clean-up (cleanmgr.exe) is a computer maintenance utility included in Microsoft Windows designed to free up disk space on a computer's hard drive. The utility first searches and analyzes the hard drive for files that are no longer of any use, and then removes the unnecessary files.
Error Checking Prompts : Error checking prompts are little green triangles that appear in the left-hand corner of cells where Excel perceives that formulas may have one or more errors. An exclamation mark icon appears when you click on a cell containing such a prompt, which you can click on to display a set of error handling options.
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.
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.
Name Manager: The Excel Name Manager is specially designed to manage names: change, filter, or delete existing names as well as create new ones.
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
Safe mode: Safe mode lets you safely use Excel without encountering certain startup programs. You can open Excel in safe mode by pressing and holding Ctrl while you start the program or by using the "/safe" option (that is, excel.exe /safe) when you start the program from the command line.
TRANSPOSE: The Microsoft Excel TRANSPOSE function returns a transposed range of cells. For example, a horizontal range of cells is returned if a vertical range is entered as a parameter. Or a vertical range of cells is returned if a horizontal range of cells is entered as a parameter.
TRIM Function : The TRIM function removes extraneous spaces from a cell or string of text once space is kept between each word.
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.
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.
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.
XLStylesTool: XLStyles Tool is an Open XML based application, which can analyze and clean out corrupted cell styles in your Excel workbook. XLStyles Tool will also perform analysis of named ranges and report potential issues. User of the tool is able to take actions to remove identified corrupted file content. Files processed by the XLStyles Tool in most cases will show noticeable improvement in file size, opening times, restored ability to support cell format edits (including copy/paste operations) and better overall performance.
