The Advanced Course deals with advanced functions and tools for working with and reporting on large spreadsheets.
Version 2010
Duration
1 day (groups)
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
- Customising the ribbon
- 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
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
- Using slicers
- 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
