Advanced Microsoft Excel ®

Do you still think that Excel is a tool that is used only by people in your company’s finance department? If so, you are wrong. It does have fantastic financial modelling capability but it offers so much more than that. Excel is not reserved for financial data. Everyone in middle to senior management manages some kind of data and hence the need to attend this course.

This course is aimed at professionals who have, or will soon have, responsibility for managing and manipulating data using MS Excel on a day to day basis. This Advanced Microsoft Excel training class is designed for students to gain the skills necessary to use pivot tables, audit and analyze worksheet data, utilize data tools, collaborate with others, and create and manage macros and ends with delegates being skilled in using 50+ MS Excel functions, sophisticated data management, charting techniques and advanced data analysis capability.

This course will feature:

• Advanced data analysis

• Both textual and numerical data

• Forecasting

• Advanced charting

• Scenario analysis

By the end of this course, participants will be able to:

• Analyse relationships across information and data using MS Excel
• Generate data forecasts using MS Excel
• Organise your company’s data in a more structured manner
• Analyse your data effectively using various MS Excel techniques
• Select the appropriate chart for your data

This course is suitable to a wide range of professionals but will greatly benefit:

• Administrators using MS Excel at a very basic level
• Administrators with a need to improve data management techniques utilising MS Excel
• New Administrative Staff with no prior knowledge of MS Excel
• HR professionals seeking to use MS Excel to analyse employee data and inventory data
• Students who have little or no familiarity with Microsoft Excel or more experienced Excel users who want to learn the topics covered in this course in the 2013 - 2016 interface.

This course will utilise a variety of proven adult learning techniques to ensure maximum understanding, comprehension and retention of the information presented.

The course will be split up into themes with a series of exercises based on each theme. The approach will also be incremental with each session building on prior knowledge.

Each delegate will be introduced to practical, hands-on learning using MS Excel. Delegates can bring their own Windows or Mac OS laptop also to the sessions, for them to be comfortable with the environment and version of MS Excel that they will be working on.

Day One:

Using Pivot Tables

A. Creating Pivot Tables

I. Preparing Your Data

II. Inserting a Pivot Table

B. Inserting Slicers

C. Working with Pivot Tables

I. Grouping Data

II. Using PowerPivot

III. Managing Relationships

D. Inserting Pivot Charts

E. More Pivot Table Functionality

F. Exercise: Working with Pivot Tables

A. Protecting Worksheets and Workbooks

I. Password Protecting a Workbook

II. Removing Workbook Metadata

B. Exercise: Password Protecting a Workbook

I. Password Protecting a Worksheet

C. Exercise: Password Protecting a Worksheet

I. Password Protecting Ranges in a Worksheet

D. Exercise: Password Protecting Ranges in a Worksheet

E. Tracking Changes

I. Accepting or Rejecting Changes

II. Setting Tracking Options

III. Merging Multiple Workbooks

F. Marking a Workbook as Final

G. Exercise: Tracking Changes

Recording and Using Macros

A. Recording Macros

I. Copy a Macro from Workbook to Workbook

B. Exercise: Recording a Macro

C. Running Macros

D. Editing Macros

E. Adding Macros to the Quick Access Tool bar

II. Managing Macro Security

F. Exercise: Adding a Macro to the Quick Access Tool-bar.

Day TWO:

Working with Others

A. Tracing Precedents

B. Exercise: Tracing Precedents

C. Tracing Dependents

D. Exercise: Tracing Dependents

E. Showing Formulas

Data Management, Manipulation and Analysis using Excel®

Auditing Worksheets

A. Converting Text to Columns

B. Exercise: Converting Text to Columns

C. Linking to External Data

D. Controlling Calculation Options

E. Data Validation

F. Exercise: Using Data Validation

G. Consolidating Data

H. Exercise: Consolidating Data

I. Goal Seek

J. Exercise: Using Goal Seek

Day THREE:

Random Useful Items

A. Sparklines

I. Inserting Sparklines

II. Customizing Sparklines

B. Exercise: Inserting and Customizing Sparklines

C. Preparing a Workbook for Internationalization and

Accessibility

I. Display Data in Multiple International Formats

II. Modify Worksheets for Use with Accessibility Tools

III. Use International Symbols

IV. Manage Multiple Options for +Body and +Heading Fonts

D. Importing and Exporting Files

I. Importing Text Files

E. Exercise: Importing Text Files

I. Exporting Worksheet Data to Microsoft Word

F. Exercise: Copying Data from Excel to Word

I. Exporting Excel Charts to Microsoft Word

II. Exporting Excel Charts to Microsoft Word

G. Exercise: Copying Charts from Excel to Word

Microsoft Excel Features that were new in 2013

A. Using Slicers to Filter Data

B. Exercise: Filtering Data with Slicers

C. Creating a PivotTable Timeline

D. Exercise: Creating a Timeline

E. Creating a Standalone PivotChart

F. Workspaces in Excel 2013

Day FOUR:

Features New in 2016

A. PivotTable Updates

B. Ink Equations

C. Multi-Select Option in Slicers

D. Quick Shape Formatting

E. Sharing with SharePoint or OneDrive