Excel Agility: Spreadsheet Internal Control
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Skills, Business Administration, Finance, Software, Taxation and Accounting
- Credit: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
In this insightful presentation, you’ll learn from Excel expert David Ringstrom, CPA, how to best utilize a variety of internal control features available in Excel. David explains how several Excel features—Data Validation, Conditional Formatting, and hide and protect features—can be used to control users’ actions and protect your worksheets and workbooks from unauthorized changes. In addition, David discusses the benefits of the VLOOKUP, SUMIF, and CELL functions.
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 who develop spreadsheets for others and want to prevent unauthorized changes from being made.
Your Benefits of Attending:
- Toggling the Locked status of a worksheet cell on or off by way of a custom shortcut.
- Utilizing Data Validation to limit percentages entered in a cell to a specific range of values.
- Creating an in-cell list by way of Excel’s Data Validation feature.
- Using a custom number format to hide zero amounts within a specific area of a spreadsheet.
- Using Conditional Formatting to identify unlocked cells into which data can be entered.
- Improving the integrity of spreadsheets with Excel’s VLOOKUP function.
- Using Data Validation to create a rule that ensures dates entered within a cell are greater than or equal to today’s date.
- Ensuring proper VLOOKUP integrity by using Data Validation to create an in-cell drop-down list.
- Using Conditional Formatting to color-code your data, identify duplicates, and apply icons.
- Overcoming VLOOKUP’s quirks by using SUMIF to look up numeric values.
- Using Excel’s VLOOKUP function to look up an item description based on an input provided by the user.
- Preserving key formulas using hide and protect features.
Learning Objectives:
- Define how hide and protect features can be used to preserve key formulas.
- State what SUMIF returns if a match can’t be found.
- Recall how to use the Data Validation feature to create an in-cell list.
Level:
Intermediate
Format:
Self-Study
Instructional Method:
On-Demand Webcast
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
Previous Experience with Excel Spreadsheets
Advance Preparation:
None
Introduction
Excel Versions 00:00:58
Internal Control Goals 00:01:36
Data Validation for Date Fields 00:06:30
Using Data Validation for Percentages 00:12:51
Using Data Validation for Whole Numbers 00:18:09
Correcting Data Validation Settings 00:20:30
Using Table Feature for Seld-Expanding Lists 00:23:16
Naming Table and Cell Ranges 00:25:58
Undoing the Table Feature 00:31:44
Using VLOOKUP to Return Item Descriptions 00:34:24
Invalid VLOOKUP Formulas Return #REF! 00:38:17
Empty Cells Cause VLOOKUP to Return #N/A 00:40:32
Handling the #N/A Error Returned by VLOOKUP 00:43:50
Data Validation for Item List 00:46:36
Using SUMIF Function to Return Prices 00:51:55
Making Unit Prices Field Appear Empty 00:57:53
Amount Field Formula 01:00:32
Using Custom Number Format to hIde Zeros 01:01:45
Future-Proofing SUM Function 01:05:15
Unlocking Data Validation Cells 01:07:59
Lock/Unlock Cell Toolbar Icon 01:11:28
Unlocking Input Cells 01:16:33
Adding Items to the List 01:17:54
Hiding Worksheet Formulas 01:20:02
Using Color to Audit Unlocked Cells 01:22:38
Removal Conditional Formatting 01:25:58
Protecting the Invoice Workisheet 01:26:37
Allow Certain Users to Override Prices 01:27:59
Hiding and Protecting the Items Worksheet 01:
Identify Overwritten Data Validation 01:33:58
Identify Overwritten Data Validation (cont.) 01:35:15
Presentation Closing 01:40:40
- #N/A Error 00:41:25
- #REF! Error 00:38:25
- CELL Function 01:23:48
- Data Validation 00:02:07, 00:06:58, 00:12:56, 01:08:24
- Error Alert 00:14:23
- IFERROR Function 00:44:45
- IF Function 00:58:10
- Name Box 00:27:45
- Name Manager 00:28:47
- Protect Sheet Command 01:26:49
- Range Name 00:03:57
- SUM 00:05:27, 01:05:17
- SUMIF 00:52:03
- Table Feature 00:03:48, 00:23:22, 00:31:44
- TODAY Function 00:07:46
- VLOOKUP 00:04:33, 00:34:35
#N/A Error: Excel displays this error when a lookup function, such as VLOOKUP or MATCH, cannot return the requested information.
#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.
CELL Function: The CELL function is a built-in function in Excel that is categorized as an Information Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the CELL function can be entered as part of a formula in a cell of a worksheet.
Data Validation : An Excel feature that allows users to assign data entry rules to one or more cells within an Excel worksheet.
Error Alert: This lets you display a message to the user when the value entered into a cell is invalid.The message will be activated when the user presses Enter or when they try to select a different cell.
IF Function: Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false. So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.
IFERROR Function: Introduced in Excel 2007, the IFERROR function simplifies crafting formulas that may sometimes return an error, such as #N/A.
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.
Name Manager: The Excel Name Manager is specially designed to manage names: change, filter, or delete existing names as well as create new ones.
Protect Sheet Command: To prevent other users from accidentally or deliberately changing, moving, or deleting data in a worksheet, you can lock the cells on your Excel worksheet and then protect the sheet with a password.
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.
SUMIF: A look-up function in Excel that allows you to add up numbers based upon a criterion that you specify. Unlike VLOOKUP, the SUMIF function can add up two or more values and returns zero (instead of #N/A) if no match is found.
TODAY Function: The TODAY function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook. It is also useful for calculating intervals.
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.
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.
