Microsoft^® Excel 2013 Advanced Level

This course covers the advance features of Excel. It is suitable for Excel users who wish to enhance their knowledge on more advanced formulas and functions. At the end of this course you will have better understanding of the more advanced features of Excel. You will be able to link and consolidate information from different Excel files, manipulate data lists using filtering, subtotals and pivot tables. You will also be able to use logical and lookup functions.

At the end of the course, participants will be able to:

• Create formulas to link multiple worksheets
• Consolidate data across multiple worksheets
• Create custom list and data form
• Filter data using Advanced Filter feature
• Compute subtotals
• Analyze data using Pivot Table
• Analyze and lookup data using database functions
• Record macros and assign macros to button

Completion of Microsoft Excel (Basic & Intermediate) course or equivalent knowledge.

Module A1: Consolidating Data with Excel

Creating Links

• Linking Sheets Within the Workbook
• Linking to Other Workbooks
• Amending Broken Links

Using Data Consolidate

• Consolidate By Position
• Consolidate By Category

Consolidate Data Using Formulas

Module A2: Auditing a Worksheet

Tracing Formulas and Errors

• Trace cells that provide data to a formula (precedents)
• Trace formulas that reference a particular cell (dependents)

Error Checking

• Correct common formula errors one at a time
• Correct an error value

Watch Window

• Add cells to the Watch Window
• Remove cells from the Watch Window

Protecting Your Files and Worksheets

• Locking & Unlocking Cells
• Setting Multiple Range Passwords
• Protecting The Contents of a Worksheet
• Protecting The Structure of a Workbook
• Encrypting Your Workbook

Module A3: Managing Excel Data

Introducing Excel Tables

• Inserting a Table
• Benefits of A Table
• Removing A Table

Sorting Data

• Performing A Quick Sort
• Performing A Complex Sort
• Using A Custom List
• Sorting Based On Colours

Entering Data Using Forms

• Customising The Quick Access Toolbar
• Entering New Data
• Editing Data
• Performing A Criteria Base Search

Filtering Data

• Using Filters
• Using Custom AutoFilter

Advanced Filtering

• Using Multiple OR To Filter
• Using Multiple AND To Filter
• Using AND & OR To Filter
• Using Wildcards To Filter

Subtotals

• Adding Subtotals
• Adjusting Views with Subtotals
• Removing Subtotals
• Performing Nested Subtotals

Validating Data Entry

• Creating A Drop Down List
• Creating A Customised Error Message
• Controlling Values & Dates
• Using Formulas to Control Data

Importing Text Files

• When to Use Delimited
• When to Use Fixed Width
• Using Text To Column to manipulate data

Module A4: Introduction to Pivot Tables and Pivot Charts

Introduction to PivotTables

• What Are PivotTables?
• Creating a PivotTable
• Customizing Your Report
• Filtering Data

Creating a Pivot Chart

• Inserting A Pivot Chart
• Formatting & Adding Elements
• Removing Buttons From A Pivot Chart

Module A5: Logical & Lookup Category Functions

Using The Function Library

Introduction to Logical Functions

• IF Function
• Nested IF function

Introduction to LookUp Functions

• When to Use The Exact Match
• When to User The Nearest Match
• VLOOKUP
• HLOOKUP
• Problems In VLOOKUP & HLOOKUP

Module A6: What if Analysis

Creating Data Tables

• Creating A One Input Data table
• Creating A Two Input Data table

Using Goal Seek For Simple Problems

Using Solver For Complex Problems

• Using Solver
• Minimising & Maximising Models
• Adding Constraints to Your Model
• Saving Solver Solutions to the Scenario Manager
• Retriving a Solution From thr Scenario Manager

Module A7: Introduction To Macros

Automating Tasks with Macros

• Turning On The Developer Tab
• Viewing The Project Explorer
• Recording a Macro
• Running a Macro
• Editing a Macro
• Assigning Macros to Buttons
• Saving A Macro Enabled Workbook

Appendix A: Excel Keyboard Short Cuts

• Keyboard access to the ribbon
• CTRL combination shortcut keys
• Function keys
• Other useful shortcut keys

• Microsoft® Excel for Intermediate/Advance User
• Microsoft® Excel 2007/2010 – Advanced Level
• Microsoft® Excel 2007/2010 – Intermediate Level
• Microsoft® Excel for Beginners
• Tips & Tricks in Microsoft® Excel Functions & Formulas 2007/2010
• More other Information Technology related courses