Advanced Excel 2000

1
Advanced Excel 2000

• Provided by CSC
• Instructor Sau

2
Chapter 1 Database Application

• List
• Heading (No Separation)
• Data Same Format
• Blank Row and Column beside it

3
Chapter 1 Database Application

• Form
• Input Record and Query
• Data Validation
• Set Criteria for others to input specified data
  
• Sorting List
• Sort the data in ascending or descending order
• Subtotals
• Sub Group Statistics
• Auto Filter Advanced Filter
• Filter important data

4
Chapter 1 Database Application

• Data Validation
• Set Criteria for others to input specified data
• e.g. Input 1-100 marks only (Whole Number)
• e.g. Input Chinese, English or Computer only
  (List)
• Set Criteria
• Set Input Message
• Error Alert

5
Chapter 1 Database Application

• Data Validation

• Setting Input Message
• Guide users during data entry

• Error Alert
• Set an error message when invalid entry is found

6
Chapter 1 Database Application

• Sorting List
• Sort the data in ascending or descending order
• Sort by first sort
• Order
• Then by second sort

7
Chapter 1 Database Application

• Subtotals
• Sub Group Statistics
• At each change Group
• Use Function
• Add Subtotal toDisplay Location

8
Chapter 1 Database Application

• Auto Filter
• Filter important data
• Data?Filer?AutoFilter
• The quickest way to selectonly those items you
  want to display in a list.

9
Chapter 1 Database Application

• Advanced Filter
• Filter important data
• Data?Filer?Advanced Filter
• Set many criteria
• Same Row And
• Different Row Or

10
Chapter 2 Advanced Number Formatting

• Custom Numeric Format
• Set Data Display Format
• Format Code Structure
• Format Code Condition
• Conditional Formatting
• Set Display Format by simple mouse click
• AutoFormat
• Beautify your List automatically

11
Chapter 0 What IF Function

• What IF Function
• IF (criteria, true statement, false statement)
• Example
• IF (C2gt50, Pass, Fail)

12
Chapter 4 Macros

• Recording a Macro
• Macros stores a series of Excel commands for you
  to use at any time you like
• Automate complex tasks and reduce the number of
  steps required to complete frequently performed
  jobs
• Playing the Macro
• Shortcut Key Ctrl - j

13
Chapter 5 Pivot Table

• Creating a Pivot Table
• Interactive Table (Column Row)
• Display or Hide Data easily
• Statistical Functions
• Create a Pivot Chart
• Interactive Chart (Similar functions)

14
Chapter 6 Importing File to Excel

• Import MS Access File
• Input 2 Tables data into Excel
• Create a Web Page
• Only Microsoft IE 5 or above can view the correct
  format

15
Chapter 0 VLookup Function

• VLookup Function
• VLookup (Input Cell, Lookup Table, Column
  number, Exact match or not)
• Example
• Vlookup (A10, lookup, 2, False)