The Advanced Course deals with advanced functions and tools for working with and reporting on large spreadsheets.
Version 2007
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
There are no unit standards available yet for this course.
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 QuickAccess toolbar
- Creating and using Range Names
- Setting Data Validation
Module 2: Tables (Lists)
- Creating a table
- Modifying a table
- Formatting a table
- Sorting a list
- Filtering a list
- Using sub-totals
- Outlining
- Custom views
Module 3: Advanced functions
- =Vlookup
- =Iserror and IFerror
- =Sumif and =Countif
- =Sumifs and =Countifs
- Nested IF statements
- Text functions
- Text to Columns
- Print formulas
Module 4: What-if analysis
- Data tables
- Goal seeek
Module 5: Reports
- Data consolidation
- 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 the Quick Access toolbar
