- Credits: 2
- Format: Self-Study Video
- Field of Study: Computer Software and Apps
- Author/Speaker: David Ringstrom
|Course ID:||Advanced Preparation:||Experience Level:|
|Published Date:||Program Prerequisites:||Other Course Formats:|
|© June 2020||Basic Understanding of Technology||N/A|
User-proofing Excel spreadsheets don't have to be a tedious task, nor does it have to feel Draconian for spreadsheet users. In this presentation Excel expert David Ringstrom, CPA shares how to implement internal control features within your spreadsheets. Techniques covered include Data Validation, Conditional Formatting, hiding worksheets securely, as well as protecting worksheets and workbooks. You'll also see how to improve the integrity of your spreadsheets with VLOOKUP and SUMIF, as well as Excel's Table feature.
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.
- Using Data Validation to create a rule that ensures dates entered within a cell are greater than or equal to today’s date.
- Protecting sensitive information by hiding formulas within an Excel workbook.
- Using Excel’s IF function to make certain fields appear blank until a required input is provided.
- Creating self-expanding drop-down lists with Excel’s Data Validation feature.
- Toggling the Locked status of a worksheet cell on or off by way of a custom shortcut.
- Creating an in-cell list by way of Excel’s Data Validation feature.
- Ensuring proper VLOOKUP integrity by using Data Validation to create an in-cell drop-down list.
- Utilizing Data Validation to limit percentages entered in a cell to a specific range of values.
- Using a custom number format to hide zero amounts within a specific area of a spreadsheet.
- Using Excel’s IFERROR function to mask # sign errors, such as #N/A.
- Viewing two worksheets at the same time within the same workbook.
- Protecting hidden sheets from within a workbook.
After reading the course material, you will be able to:
- Recall the ribbon tab that the Table feature appears within.
- State what can be included within a formula to make a cell appear blank under certain conditions.
- Identify which input is valid for a data validation rule limiting inputs to whole numbers between 0 and 100.
Who Should Attend:
- All Certified Public Accountants (CPAs)