Advanced Excel training

This course aims to give delegates an in-depth knowledge of Excel features.

On completion of the course delegates will have advanced skills in using Microsoft Excel. They will be able to:

• Use a variety of Excel functions
• Name cells and ranges
• Successfully manipulate workbooks
• Audit a worksheet
• Create and use PivotTables and reports
• Use Solver, Scenario Manager and Goal Seek tools
• Create and use macros

 The course is of one day’s duration.

This instructor-led course has a hands-on approach, with plenty of opportunity for delegates to practise the new skills they learn at the computer.

Familiarity with the Windows environment, and a reasonable knowledge of Excel. Prior attendance of the Intermediate Excel course would be an advantage.

• Quick Analysis Tool
• Flash Fill
• New Functions
• Using the new Charting features

• IF, NESTED IF
• AND, OR
• SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS
• VLOOKUP
• IFERROR
• TEXTJOIN
• TODAY, DATEDIF, DAYS
• COLUMN,ROW
• INDEX, MATCH
• INDIRECT, OFFSET

• The Name Box
• Using names instead of absolute cell references
• Using names across worksheets
• Using the Name Manager

• Creating an Excel table
• Creating a PivotTable report
• Creating a PivotChart report
• Grouping data in a PivotTable
• Refreshing data
• Filtering pivot data
• Row and column details
• % of Total
• Creating new fields
• Slicers
• Timelines
• Show report filter pages
• Adding tables to the data model

• Tracing errors
• Tracing dependents
• Tracing precedents
• Adding, removing and editing worksheet comments
• Displaying all formulas in a worksheet

• Sparklines
• Using one-input or two-input data tables
• Goal Seek
• Solver
• Scenario manager

• Custom views
• Applying and removing worksheet and workbook protection
• Data Consolidation

• Recording macros
• Running macros
• Editing macros
• Running macros from keyboard shortcuts
• Adding a macro button to the Quick Access toolbar