Desktop IT Advanced Excel classroom

About the course

This two-day course focuses on advanced methods of data management. Connecting Excel to other data sources and utilising Pivot Tables to analyse and present results from the cornerstone of this course. Each section is reinforced with real-life examples that enable delegates to test their new-found knowledge. There is also a workshop (time permitting) to look at specific individual needs.

Duration

2 Days

Learning objectives

Learn how to summarise large workbooks with Pivot Tables including new and updated options for the modern Office Excel
Understand how to apply the new sparklines features to highlight trends in the data
Be able to use analysis tools to forecast figures based on a range of scenarios and use consolidation to bring together information from separate spreadsheets and workbooks
Gain a complete understanding of the database features including new table features and calculated columns
Know how to apply filters and sort data into key elements including the new slicers introduced in 2010, and understand how you can utilise advanced IF statements and macros to automate repetitive or tedious tasks and customise the ribbon interface

What's covered?

What are Pivot Tables?
Creating a Pivot Table from scratch
Setting up Pivot Charts
Updating Pivot Tables
Utilising the new slicers features
Adding formulas to Pivots
Looking at performance metrics
Conditional formatting a Pivot Table
Advanced IF statements
Utilising COUNT functions
Data consolidation by position
Consolidating data using labels
Goal Seeking
Building your first scenario
Data projection using What-If
Adding sparklines
Setting up data tables
Using the worksheet as a database
Building advanced filters
Using outlining to handle large worksheets
Introduction to macros
Recording macros
Running macros from a shortcut
Editing macros
Creating icons to run macros
Customising the ribbon

Target audience

This course is suitable for those who have attended the Intermediate course or have good knowledge and understanding of Excel and need to discover how Pivot Tables and macros are set up.