The Advanced Course deals with advanced functions and tools for analysing data and reporting on large spreadsheets.
Version 2003
Duration
1 day
Outcomes
The student will be able to analyse, manage and report on large spreadsheets using advanced functions and data commands
Unit Standards
Not applicable
Prerequisites
The advanced course assumes that you have worked with Excel on a daily basis for some period of time and have used most of the features covered in the Beginners and Intermediate courses.
Course content
Module 1: Customising Defaults
- Changing defaults
- Customising the toolbars
- Range Names
- Setting Data Validation
Module 2: Lists (Databases)
- Creating a list
- Adding & deleting records
- Editing in data form
- Adding/Deleting fields
- Finding information in a list
- Sorting a list
- Filtering a list
- Using sub-totals
- Outlining
- Custom views
Module 3: Advanced functions
- =Vlookup
- =Iserror
- =Sumif and =Countif
- Nested IF statements
- Text functions
- Print formulas
Module 4: What-if analysis
- Data tables
- Goal seek
Module 5: Reports
- Using pivot tables
- Creating and modifying a pivot table
- Formatting pivot tables
- Creating a PivotChart
Module 6: Simple Macros
- Recording a macro
- Relative and Absolute References
- Editing and Deleting macros
- Running a macro
- Assigning macros to objects
- Assigning macros to a toolbar
