Advanced Excel Training

INTRODUCTION

---

Excel has powerful tools that enable busy users to set up systems that simplify data analysis and interpretation. Effectively using Excel allows users to devote their time more profitably to making decisions about and acting on results, with more effective outcomes. Participants actively learn through a series of practical simulations which ensures that they maximize their learning and can use the tools immediately when they return to the office. In addition to the practical component, all participants also receive a manual describing all the techniques learned.

WHO SHOULD ATTEND

---

This Microsoft Excel training is ideal for people wanting to use more complex functions within Excel, including tools for analyzing, linking and exporting data. Participants will need to have attended or be familiar with the basics of Microsoft Excel.

DURATION

---

5 days

COURSE OBJECTIVES

---

• Protect a workbook and save with a backup
• Understand and use advanced functions
• Consolidate data, link and export data
• Use What-If Analysis tools such as Goal Seek, Solver and Scenarios
• Create and modify Pivot Tables and Pivot Charts
• Create and modify Microsoft Excel Macros

TOPICS TO BE COVERED

---

Module 1: File Protection/ Cell Protection and Circular References

• Attaching a Password
• Modifying a Password
• Protecting Cells
• Circular Error Messages
• The Circular Reference Toolbar

Module 2: Referencing in Excel

• Absolute referencing
• Relative referencing
• Structured reference

Module 3: Working with Structure tables

• Why you should use tables
• Elements of an Excel table
• Managing data in an Excel table
• Using structured references with Excel tables

Module 4: Formulas and Functions

• Function Overview
• Math and Trig Functions
• Financial Functions
• Logical Functions
• The AND Function
• The OR Function
• Nesting Functions
• The VLOOKUP Function
• The HLOOKUP Function
• Concatenating Functions
• Index, offset and Match Functions
• Using Help for Functions

 Module 5: Goal Seek/ Solver/ Scenarios and Linking

• Using Goal Seek
• Saving a Scenario
• Solver Reports
• Adding a Scenario
• Showing Scenarios
• Worksheet Linking
• Workbook Linking

Module 6: Working with data

• Data Validation
• Data Consolidation
• Importing Data
• Importing CSV Files
• Text Import Wizard
• Exporting Data
• Choosing a Specific Format
• Exporting to Word
• Pasting Data with a Link
• Copying a Graph with a Link

Module 7: Pivot Table Reports

• Pivot Table Layout
• Rearranging Data
• Filtering a Report
• Adding Fields
• Field Settings
• Refreshing Data
• Formatting a Pivot Table
• Showing Detail
• Create a Calculated Field
• Advanced Data Field Settings

Module 8: Pivot Chart Reports

• Creating a Pivot Chart
• Creating Pivot Chart Reports from Scratch
• Adding Fields to a Pivot Chart

Module 9: Macros

• Recording a Macro
• Editing a Macro
• Relative/Absolute Recording
• Stepping through a Macro
• Macro Buttons
• Customizing the Toolbar
• Deleting a Macro