Excel Adv

Excel - Adv

Our 1 day Microsoft Excel Advanced course is suitable for those with a sound working knowledge of Microsoft Excel 2007, Microsoft Excel 2010 and Microsoft Excel 2013 and who wish to progress to the most complicated functions and features.

Prerequisites

A working knowledge of Excel or our Introduction to MS Excel course.

Benefits

• At the end of this course you will have a complete understanding of the higher functions of Excel.
• You will be able to perform the most advanced calculations and functions, manipulate data using pivot tables and 'advanced' analysis, record macros and integrate with other office applications.
• You will then be qualified to progress to our Excel VBA course.

Course Content

Advanced Functions

• IF Function & Nested IF Statements
• And & Or Functions
• Lookup Functions (Horizontal And Vertical Lookup)
• Subtotals
• Conditional Formatting

3D Ranges

• Create & Maintain (Within Worksheets In a Single File & Between External Files)
• Consolidate Data (via 3D Ranges & Using The Consolidate Tool)

Sparklines

• Inserting Sparklines
• Customizing Sparklines

 Pivot Tables

• Create Pivot Table
• Work With Row/ Column Fields
• Data Fields
• Customise Table Display

Organising Workbooks

• Create Templates
• Protect Ranges & Worksheets

Data

• Enter & Records Using The Input Form
• Sort Data Into Lists
• Filter Data - Autofilter, Advanced Filter
• Validation Rules

 Macros

• Create Macros – Record & Modify the VBA Code Generated
• Relative Referencing
• Assign To Toolbars/ Keyboard
• Make Available To All New Documents/ Current Document Only

Outlines

• Group Row/ Column Data To Create Outline Levels Based On Worksheet Formulae
• Display Summary Rows And Summary Styles

Custom Views

• Create and Modify
• Include Hidden Rows, Columns, Filter Settings