VALIDATING, PROTECTING AND AUDITING FORMS & TEMPLATES
Using Data Validation And Tracing Invalid Data
- Validate Data Input
- Compose A Data Input Message
- Create An Error Alert Message
- Circle Invalid Data
Creating A New Workbook Base On A Template
- Create A New Workbook Base On A Template
- Create A New Template
- Create A New Workbook Base On Your Own Template
- Edit The Template
Protecting Worksheet Styles, Contents And Elements
- Protect The Worksheet
- Unprotect The Worksheet
- Unlock Cells For Editing In A Protected Sheet
- Hide A Cell Formula
- Set A Password To Edit A Specific Range
- Grant Selected Users Permission To Edit A Range Without A Password
- Protecting Workbook Contents
- Protect And Unprotect The Workbook Structure
Protecting Your Workbook From Unauthorized Users Access
- Set A Password To Open A Worksheet
- Remove A Password From A Workbook
- Set A Password To Modify A Worksheet
Auditing Worksheets And Tracing Cell References
- Find Cells That Provide Data To An Active Cell (Precedents)
- Find Cells That Are Referred To By An Active Cell (Dependents)
- Correct Calculation Errors
- Set Error Checking Options
- Scan And Correct All Errors
- Use The Watch Window
USING ADVANCED FUNCTIONS
Using Financial Functions
- Use The PMT Function
- Use The ABS Function Using Logical Functions
- Use A Logical Test
- Use The IF Function To Return A Text
- Use The IF Function To Return A Value
- Use A Nested IF Function
- Use The OR Function
- Use The AND Function
- Use the COUNTIF Function
Using The VLOOKUP Function
- Use VLOOKUP To Find Data
- Limit The Lookup Value For An Exact Match
- Use The Closest Match For The Lookup Value
|
CREATING A PIVOTTABLE AND ANALYZING DATA
Creating A PivotTable
Updating A PivotTable
- Update The Information In A PivotTable
- Change The Layout Of A PivotTable
- Add An Information Field To A PivotTable
- Arrange Information In A PivotTable
- Filter The Report In A PivotTable
- Filter Specific Records
Working With Calculated Fields In A PivotTable
- Change The Calculated Field Value Settings
- Insert A Calculated Field
- Delete Calculated Fields
- Change A PivotTable Options
Creating A Chart From A PivotTable
- Create A Chart From A PivotTable
Exploring What-If Tables
- Create A One Variable What-If Table
- Create A Two Variables What-If Table
Using Scenarios
- Create Scenarios
- Apply A Scenario
Implementing Problem Solving
- Use The Goal Seek Tool
- Load Add-Ins
- Use The Solver Tools
USING WORKGROUP FEATURES
Using Comments
- Add A Comment Into A Cell
- Show/Hide Comments Permanently
- Show/Hide Comment Indicators
- Inspect A Workbook
- Apply Password Protection To The Editable Cells
Sharing Workbooks
- Enable Workbook Sharing
- Merge Copies Of Shared Workbooks
- Accept Or Reject Changes In A Merged Workbook
- Protect A Shared Workbook
- Track Changes In The Same Workbook
- Accept Or Reject Changes From Different Users
AUTOMATING ROUTINE TASKS WITH MACROS
Understanding Macros
- Record A New Macro
- Run A Macro
- Create A Run Macro Button On The Quick Access Toolbar
- Show The Developer Tab
- Create A Macro Command Button On A Worksheet
- Edit The Command Button
Editing A Macro
- Edit Macro Coding
- Rename A Macro
- Delete A Macro
Using A Relative Reference Macro
- Record A Relative Reference Macro
- Run A Relative Reference Macro
|