On Demand Webinar

Excel expert David Ringstrom, CPA, outlines how to implement internal controls in your spreadsheets in this live presentation. Step-by-step, David explains how to use and benefit from Excel’s Data Validation feature, Conditional Formatting feature, and hide and protect features. In addition, he discusses Excel functions that can be applied to improve the integrity of your spreadsheets and protect them from unauthorized changes.

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 Should Attend:
Practitioners who develop spreadsheets and want to prevent other users from making unauthorized changes.

Topics Typically Covered:

•    Preserving key formulas using hide and protect features.
•    Creating an in-cell list by way of Excel’s Data Validation feature.
•    Using Conditional Formatting to color-code your data, identify duplicates, and apply icons.
•    Improving the integrity of spreadsheets with Excel’s VLOOKUP function.
•    Going behind the scenes in a workbook to check for worksheets that can’t be viewed or hidden from Excel’s primary user interface.
•    Using Excel’s VLOOKUP function to look up an item description based on an input provided by the user.
•    Using Excel's IFERROR function to mask # sign errors like #N/A.
•    Future-proofing VLOOKUP by using Excel’s Table feature versus referencing static ranges.
•    Creating resilient SUM functions that won’t break when users insert additional rows.
•    Using Data Validation to create a rule that ensures dates entered within a cell are greater than or equal to today’s date.
•    Using a custom number format to hide zero amounts within a specific area of a spreadsheet.
•    Overcoming VLOOKUP’s quirks by using SUMIF to look up numeric values.

Learning Objectives:

•    Apply lookup functions to find and access data automatically from lists.
•    Identify how hide and protect features can be used to preserve key formulas.
•    Define how to use Excel’s Data Validation feature to restrict data entry to a list of permissible choices.