Microsoft Excel 2013 – Level 2 Advanced (In-House Training)IT Short Courses

Introduction

Excel has become very much more than just a simple spreadsheet. Although its roots are still in number management, today Excel’s uses cover a wide gamut – from simple data entry to complicated financial models and analysis.

Key Takeaways

Upon completion of this workshop, participants will be able to;

• This course adds on to the participants’ knowledge of Microsoft Excel.
• Formulae form an integral part of the course and participants will learn how to use complex functions to model their data and to generate a variety of answers and reports

Who Should Attend

Those seeking to further their knowledge of Excel would benefit from this course. Those needing to create spreadsheet models & data analysis are especially encouraged to attend.

Programme Outline

Module 1: Using Range Names

• Assigning Names
• Jumping to Named Ranges
• Create Range Names from Headings
• Managing Names
• Using Names in Formulas and Functions

Module 2: Database / List Operations

• Introduction to Excel Databases
• Using Tables
• Creating a Table
• Modifying Tables
• Removing Duplicates
• Filtering
• Using the Autofilter
• Advanced Filters
• Using Filter Criteria
• Using Comparison Operators
• Database Functions
• Create and Edit Database Functions
• Summarise Data Using Subtotals
• Grouping and Outlining Data

Module 3: Data Validation

• Setting Data Validation
• Restricting Entry to Data Type
• Determine Data Entry Range
• Displaying Input Message and Error Alert
• Circling Invalid Data

Module 4: Data Consolidation

• 3D Formulae: Using Multi-Dimension Worksheets and Workbooks
• Consolidating Data
• By Position
• By Category

Module 5: Auditing Worksheets

• Tracking Errors in Excel
• Understanding Precedents and Dependencies
• Viewing and Hiding Tracer Arrows
• Resolving Circular References
• Using the Watch Window
• Evaluate Formulas

Module 6: Advanced Functions

• Nesting of Functions
• Nesting if Conditions
• Boolean Criteria Using And and Or
• Conditional Sum, Count and Average
• Lookups and Referencing
• Vlookup
• Hlookup
• Combining Functions

Module 7: Data Analysis

• What-If Analysis
• One-Input Data Tables
• Two-Input Data Tables
• Using Goal Seek
• Running Goal Seek
• Solver
• Loading and Using Solver Add-In
• Analysing in Solver
• Set Conditions and Permutations
• Using the Scenario Manager
• Saving and Displaying Options
• Creating a Scenario Summary

Module 8: Pivot Tables

• Understanding Pivot Tables
• Building a Pivot Table
• Pivot Table Formatting
• Table Layout
• Field Options
• Changing Numeric Formats
• Using Styles
• Creating Pivot Charts
• Printing a Pivot Report

Module 9: Collaborating

• Sharing Workbooks
• Creating Shared Workbooks
• Tracking Changes
• Showing History of Changes
• Merging Workbooks

Methodologies

A good foundation in Excel is essential for this course. Participants should be familiar with the program and be able to write basic formulae. Having attended an essentials course is advantageous.

A combination of lectures, explicit teaching, practical demonstrations and peer teaching is applied for this course.

Experiential learning approach is adopted where the learners will be required to consider the experience as a learning process joined with critical reflection which further qualifies for this theory to be applied for this course because, at each segment, the learners are introduced to some form of experience after which they are asked to reflect on these experiences before sharing and putting ideas into practice.