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
Event Information
Event Date | 15-04-2024 |
Event End Date | 19-04-2024 |
Individual Price | $1,200.00 |
Location | Nairobi, Kenya |