Quick Navigation
Microsoft Excel 2007 Advanced (Level 1) course
Download PDF version of Excel 2010 Advanced Level 1 course outline
Overview
This course provides delegates with an understanding of a number of advanced features as well as introducing more complex formulae and the use of logic in formulae.
Audience
Users who need to enhance their existing knowledge and explore the more advanced features including complex formulae and other useful lesser known features of the product.
Course Objectives
At the end of this course, delegates will be aware of many of the advanced features of Excel.
Prerequisites
Delegates must have attended the Excel Intermediate course (or have equivalent experience). You should be able to follow spoken and written instructions and be comfortable carrying out maths/logic calculations as the course has large elements of maths and technical content.
Course Contents (1 day)
- Using Logic
- IF statements and nested IF’s
- Conditions AND/OR/NOT
- IFERROR
- SUMIF/s & COUNTIF/s & AVERAGEIF/s
- Lookup Tables
- Range names review
- VLOOKUP (range and exact match)
- MATCH & INDEX
- Conditional formatting
- Using logical parameters
- Using date ranges
- Using Formulas
- Data validation
- Using dynamic lists
- OFFSET command
- Useful Functions
- SUMPRODUCT, RANK
- Calculations using Dates and Times
DATEDIF, TEXT, WEEKNUM
- Importing and Exporting Data
- Import data from text files
- Import/Export from other applications
- String Functions
- Concatenation (&)
- Left, Mid, Right, Len, Find, Trim
- Advanced Charting
- Adding non-adjacent data
- Mixing chart types
- Using dual Y axis
- Adding Trendlines
- X-Y charts
- Pie – Bar of Pie
- Advanced Pivot Tables
- Grouping
- Adding calculations
- Using multiple ranges
- Add-in the Pivot table Wizard
Optional Topics (time permitting)
- Useful Add-ins, inc. Close All
- Customising Ribbons
- Data Consolidate
- Printing grouped worksheets and multiple workbooks
- Setting preferences