Excel Adv (2 day)

Excel - Adv (2 Day)

Our 2 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.
• As well as covering additional topics, attending the 2 day course (as opposed to the 1 day) provides delegates with the opportunity to work their way through more examples & benefit from the extra time to absorb information

Course Content

Advanced Functions

• IF Function & Nested IF Statements
• And & Or Functions
• Lookup Functions (Horizontal And Vertical Lookup)
• Index Function
• Array Function
• 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)

Pivot Tables

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

Organising Workbooks

• Create Templates
• Styles
• Protect Ranges & Worksheets

Data

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

Goal Seeking

• Solver
• Goal Seeking With Charts
• What-If

Scenarios

• Create Scenarios
• Create Summary Report
• Merge Scenarios
• Protect Scenarios

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