Course Content
Naming cells and ranges
- Creating and defining names
- Making a name list
- Advanced technique of using names in formulas
- Using Name Manager
- Navigating spreadsheet with names
Database
- The database components
- Using Excel Form feature
- Inputing data
- Deleting data
- Finding records
- Using menu commands to find records
Advanced data sorting and subtotal
- Multi-level sorting
- Restoring data to original order after performing sorting
- Sort by icons
- Sort by colours
- Multi-level subtotal
Using database functions
- DSum()
- DMax()
- DMin()
- DAverage()
- Dcount()
Creating and using an array formula
Managing documents with workbooks
Consolidation with several worksheets
- Consolidating and combining several spreadsheets using the operation addition, subtraction
- Synchronizing the consolidated table with the source data
Data table
- One-Input table
- Two-Input table
Lookup table
- Lookup()
- Vlookup()
- Hlookup()
- Application of exact match and approximate match
- Creating an order form using vlookup function
Document protection
- Files protection
- Protecting cells/documents
- Unprotecting documents
File linking
- Paste link
Filter and advanced filter
- Defining single and multiple criteria
- Combining search criteria
- Deleting criteria
- Extracting records
Pivot table
- Steps to create a pivot table
- Creating pivot table from Excel
- Consolidating data from multiple ranges into a pivot table
Conditional format
- Highlighting data using cell colours, font colours
- Highlighting data using icons
Data validation
- Define the data input type
- Define the warning message
- Define the error message
- Circle invalid data
Sharing and tracking worksheets
- Turning on and let the other users can edit the worksheet
- Cancel changes made by the others
Using Scenario Manager
- Defining your own scenario
- Preview the result of scenario
- Editing a scenario
What-If Analysis
- How to apply What-If Analysis
- Inserting a hyperlink to a workbook
- Creating a hyperlink
- Editing a hyperlink
- Creating a menu system using hyperlink
Importing data from Internet
Creating a pull down box to facilitate the data entry process
Creating and using Macros
