VALIDATING, AUDITING FORMULAS, PROTECTING DATA & TEMPLATES
Using Data Validation
- Validate Data Input
- Compose Data Input Message
- Create Error Alert Message
Creating A New Workbook Using Templates
- Create A New Workbook Using Built-In Template On Your Computer
- Create A New Workbook Using Template On Office Online
- Create A New Template
- Create New Workbook From Own Template
- Edit The Template
Auditing Worksheets And Tracing Cell References
- Select Cells That Contain Formula
- Watch Formulas
- Find Cells That Provide Data To An Active Cell (Precedents)
- Find Cells That Referred By An Active Cell (Dependents)
- Hide All The Auditing Arrows
Tracing Errors
- Circle And Remove Circle For Invalid Data
- Correct Calculation Errors
- Set Error Checking Options
- Scan And Correct All Errors
Protect The Worksheet
- Unprotect The Worksheet
- Unlock Cell For Editing In Protected Sheet
- Hide Cell Formula In Protected Sheet
- Set Password To Edit Specific Range
- Grant Selected Users Edit Range Without Password
Protecting Workbook Contents
- Protect The Workbook Structure
- Unprotect The Workbook Structure
- Protecting Your Workbook From Unauthorised Users Access
- Set Password To Open
- Remove Password To Open
- Set Password To Modify
USING ADVANCED FUNCTIONS
Using Financial Functions
- Use PMT Function
- Use ABS Function
Using Logical Functions
- Use Logical Test
- Use IF Function To Return A Text
- Use IF Function To Return A Value
- Use Nested IF Function
- Use OR Function
- Use AND Function
- Use COUNTIF Function
Using The VLOOKUP Functions
- Use VLOOKUP To Find Data
- Limit The Lookup Value For Exact Match
- Use The Closest Match For The Lookup Value
|
Using Simple Database Functions
- Find The Sum Of Specific Records Using Paste Function
- Find The Average Of Specific Records
- Find The Maximum / Minimum Value Of Specific Records
- Find The Number Of Records
CREATING A PIVOTTABLE AND ANALYSING DATA
Creating A Pivot Table
- Create A PivotTable
- Create A PivotTable From An Access Database
Updating Pivot Table
- Update Information In Pivot Table
- Add Information In Pivot Table
- Arrange Information In Pivot Table
- Remove Information In Pivot Table
- Create Page In Pivot Table
- Display Pages On Separate Sheets
Modifying A Calculated Field in Pivot Table
- Use Other Functions In Calculated Fields
- Insert Calculated Fields
- Hide Calculated Fields
- Delete Calculated Fields
Creating A Chart From A Pivot Table
- Create Chart From A Pivot Table
WHAT IF TOOLS
Exploring Data Tables
- Create One Variable Data Table
- Create Two Variable Data Table
Using Scenarios
- Create Scenarios
- Apply Scenario
- Edit Scenario
- Delete Scenario
Using Goal Seek
AUTOMATING ROUTINE TASKS WITH MACROS
Understanding Macros
- Record A New Macro
- Run Macro With Menu
- Create A Run Macro Button On Toolbar
- Create Macro Command Button On Worksheet
- Edit Command Button
Editing A Macro
- Edit Macro Coding
- Rename A Macro
- Delete A Macro
Using Relative Reference Macros
- Record A Relative Reference Macro
- Run Relative Reference Macro
|