Pragmatic Excel - Internal Control

Who Would Be Interested in This Course:

Practitioners who develop spreadsheets for others and want to prevent unauthorized changes from being made.

Topics Covered:

 • 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:

• State the arguments used within VLOOKUP.

• State what SUMIF returns if a match cannot be found.

• Identify which one of four tasks a user can still potentially perform after a workbook has been protected.

Level:

Intermediate

Format:

Self-Study

Instructional Method:

On-Demand Webcast

Program Prerequisites:

Previous Experience with Excel Spreadsheets

Advance Preparation:

None