Microsoft Excel 2010: Advanced

Course Overview

All of our Microsoft delivery partners providers and specialist Microsoft trainers have met our tough rigorous guidelines and we constantly monitor the quality of our Microsoft training to ensure that your needs are met and only the highest quality Microsoft courses are provided.

Related Courses

• Microsoft Excel 2010: Introduction for Beginners
• Microsoft Excel 2010: Intermediate Level 2
• Microsoft Excel 2007 or 2010: Advanced - Validate & Summarise
• Microsoft Excel 2007 or 2010: Advanced - Formulae & Functions
• Microsoft Excel 2007/2010: Intermediate
• Microsoft Excel 2010: Intermediate
• Microsoft Excel 2010: Visual Basic
• Microsoft Excel 2010: upgrade from Excel 2003 2007
• Microsoft Excel 2010: Expert 2010
• Microsoft Excel 2010: Advanced

Audience

Delegates should have attended the Intermediate course or gained similar knowledge in the workplace; specifically the delegate should be familiar with formula and functions, be able to work with large amounts of data and with multiple worksheets and workbooks.

Skills Gained

Work with complex nested functions. Consolidate, import and export data successfully. Use auditing and validation tools to identify, correct and prevent errors and determine data dependencies. Achieve advanced data management using advanced filtering, queries and outlines. Summarize and analyse data using PivotTables and PivotCharts, the Goal Seek and Solver tools and by building data scenarios. Protect and share files. Record and execute simple macros.

Course Outline

Working With Advanced Functions

• Using nested functions. Using advanced logical functions. Using advanced lookup functions.

Working With External Data

• The Consolidate tool. Using Paste Special. Importing and exporting data. Converting text files to a manageable format. Using hyperlinks.

Data Management

• Using the Advanced Filter tool. Summarizing data using Sub-Totals. Summarizing data using Outlines. Summarizing data using database functions. Querying a database. Connecting to external data sources.

PivotTables and PivotCharts

• Creating a PivotTable
• Using a PivotTable to analyse data
• Modifying the data source for a PivotTable
• Changing PivotTable formats and options
• Adding calculated fields to a PivotTable
• Creating and using a PivotChart report
• Using Slicers

Using Analysis Tools

• Using Goal Seek
• Data Tables
• Installing Add-Ins
• Using Solver
• Creating Scenarios
• Creating Scenario summary reports
• Merging Scenarios

Reviewing and Sharing Workbooks

• Applying worksheet and workbook protection. Using passwords. Adding, editing and reviewing worksheet comments. Sharing a workbook. Tracking changes. Resolving conflicts in a shared workbook. Merging workbooks.

Using Macros

• Recording a macro. Using relative and absolute references with macros. Running a macro from a shortcut key, a toolbar, a menu and a worksheet. Creating global macros. Understanding macro security.