What you’ll learn

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
• Inputting 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()

Managing documents with workbooks

• Arrange All
• New Window

Consolidation with several worksheets

• Consolidating and combining several spreadsheets using the operation addition, subtraction
• Synchronizing the consolidated tables 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

Building A Pivottable

• Prepare Your Worksheet Data.
• Create a Table for a PivotTable Report.
• Build a PivotTable from an Excel Range.

Manipulating Your Pivottable

• Turn the PivotTable Field List On and Off.
• Customize the PivotTable Field List.
• Remove a PivotTable Field.
• Refresh PivotTable Data.
• Add Multiple Fields to the Row or Column Area.
• Add Multiple Fields to the Data Area.
• Add Multiple Fields to the Page Area.
• Delete a PivotTable.

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
• Creating a pull down box to facilitate the data entry process

What-If Analysis

• Using Scenario Manager
• Defining your own scenario
• Preview the result of scenario
• Editing a scenario
• Using Goal Seek
• Using Goal Seek to solve problems

Inserting a hyperlink to a workbook

• Creating a hyperlink
• Editing a hyperlink
• Creating a menu system using hyperlink

Creating and using Macros