Implementing Excel Spreadsheet Internal Controls

In this presentation author and Excel expert David Ringstrom, CPA, discusses how to use internal control features and functions within Excel spreadsheets. Internal control measures can both improve the ease-of-use of spreadsheets, improve data integrity, and minimize spreadsheet maintenance. Techniques covered include Data Validation for restricting user inputs, comparing VLOOKUP, XLOOKUP, and SUMIF for retrieving data from elsewhere in a workbook, and protecting formulas, worksheets, and workbooks.

David H. Ringstrom, CPA, has more than 30 years of experience as a spreadsheet and accounting software consultant and speaker. He has presented over 2,500 live webinars and is the author or co-author of ten books, including “Microsoft 365 Excel All-in-One for Dummies”, “Microsoft 365 Excel for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and “QuickBooks Online for Dummies”.

In his webinars, David encourages questions throughout the presentation and demonstrates every technique twice: first on a PowerPoint slide with numbered steps and then live in Excel for Microsoft 365 for Windows. He highlights any differences in Excel 2024, 2021, or 2019 during the session and in his detailed handouts. Attendees also receive an Excel workbook containing most of the examples he uses, making it easy to follow along and apply the techniques later. David additionally supports Excel for Mac users by answering their follow-up questions via email.

Who Should Attend
Professionals seeking to create spreadsheets that are easier to use and have improved data integrity.

Topics Covered

• Using Conditional Formatting to identify unlocked cells for data entry
• Tweaking Data Validation settings for a given range of cells
• Removing the Table feature or just clearing its alternate row shading
• Protecting hidden sheets from within a workbook
• Using Data Validation to create a rule that ensures dates entered within a cell are greater than or equal to today’s date
• Creating self-expanding drop-down lists with Excel’s Data Validation feature
• Using Excel’s IF function to make certain fields appear blank until a required input is provided
• Contrasting using menu commands and keyboard shortcuts for applying formatting
• Handing #N/A errors within XLOOKUP
• Overcoming VLOOKUP’s quirks by using the SUMIF function to look up numeric values
• Protecting sensitive calculations by hiding formulas and obscuring links to source data on hidden sheets
• Utilizing Data Validation to limit percentages entered in a cell to a specific range of values

Learning Objectives

• Name what the SUMIFS function returns if a match cannot be found
• Identify one of the uses of Data Validation
• State which column the OFFSET function would return data from

Level
Intermediate

Instructional Method
Group: Internet-based

NASBA Field of Study
Computer Software & Applications (2 hours)

Program Prerequisites
Prior working knowledge of Microsoft Excel is recommended.

Advance Preparation
None