Overview
Students will customize their workbooks through macros and Data Validation and collaborate with other users. This course provides the experienced Excel client with new knowledge in creating rules for entering data, using drop down lists, customizing conditional formatting, and protecting/sharing your Excel files. We will automate formatting, sorting, and page set-up tasks with three macros and assign the macro shortcuts to keyboard commands and buttons.
Prerequisites
This is an advance session and students should be comfortable with creating and editing formulas and functions and working with large data tables.
Objectives
Upon successful completion of this course, students will be able to:
- Apply custom conditional formatting,
- Add data validation criteria,
- Create, edit, and run macros,
- Protect and restrict editing worksheets and workbooks through passwords.
Course Content
Lesson 1: Data Validation
- Adding Data Validation Criteria, Message boxes, and restrictions to the database
- Using Date Validation and ranges
- Restricting number values
- Creating Drop Down Lists and customizing lists for ease of input and selection
Lesson 2: Macros and Custom Command Buttons
- Creating various Macros, including a Formatting Macro and Multiple Sorts
- Editing a Macro
- Running Macros through Keyboard shortcuts and your custom buttons
Lesson 3: Favorite Advanced Functions
- VLOOKUP and IfError
- IF . . . THEN . . . ELSE with up to seven nested If then statements for values, text, and calculations,
Lesson 4: Collaborating Excel with Other Associates
- Understanding Password protection
- Protecting your Worksheets
- Protecting your Workbook
- Allow certain areas of your workbook for updating/input but restricting other areas
- Sharing Your Workbook