ADVANCED EXCEL SIMPLIFIED FOR FINANCIAL ANALYSIS & MANAGEMENT REPORTING
Live-Online Training: N175,000
Classroom Training: N 215,000
3 - 4 participants: 5% discount
5 or more participants: 10% discount
(Available also for Customised Training by Duration, Venue & Fee)
(PARTICIPANTS SHOULD BRING THEIR LAPTOP)
Programme Description
Advanced Excel is for people who already can create moderately complex
spreadsheets and still want to add more sophistication and automation.
This course will equip participants with relevant tools and functions in MS Excel for the development of automated systems to replace certain manual and repetitive processes.
Learning Objectives
By the end of this course participants will be able to:
Use outlines, range names, databases and the data form
Use AutoFilter & advanced filters
Work with Data Series
Use advanced functions
Modify charts
Use Scenarios & Goal Seek
Use templates, data tables & solve problems
Use automatic formatting & styles
Work with views, consolidate worksheets
Create & revise PivotTables
Use, record & edit macros
Prerequisite
Participants should already be using Excel on a regular basis. Alternatively, they must have a good knowledge of Excel introductory topics.
Course Contents
DAY ONE
Work with Outlines
Apply an Outline,
Collapse/Expand an Outline, Modify Outline Settings,
Clear an Outline, Use Auto Outline, Create Subtotals in a List, Remove Subtotals from a List.
Use Multiple Workbooks
Open and Arrange Multiple
Workbooks, Save a Workspace, Create and Maintain Links.
Use Range Names
Jump to a Named Range, Assign
Names, Use Range Names in Formulas,
Create Range Names from Headings, Apply Range Names, Delete Range
Names, Use Range Names in 3-D Formulas, Create 3-D Range Names, Use
3-D Range Names in Formulas.
Work with Tables
Use Tables, Create a Table from
Existing Data, Change the Table Name,
Change the Table Style, Change Table Style Options, Create a Total Row,
Add Table Rows and Columns, Insert/Delete Table Rows/Columns, Create a
Calculated Column, Select Parts of a Table, Move a Table, Sort Data by
Multiple Levels, Use Text Filters, Use Number Filters, Use Data Validation,
Validate Data using a List, Create a Custom Error Message, Remove Data
Validation.
DAY TWO
Work
with Advanced Filters
Create a Criteria Range, Use a Criteria Range, Show All Records, Use Comparison Criteria, Use an Advanced And Condition, Use an Advanced Or Condition, Copy Filtered Records, Use Database Functions, Find Unique Records, Remove Duplicates from a Table.
Work
with Views
Create, Display and Delete a Custom View.
Share Workbooks
Save a Shared Workbook, View Users Sharing a Workbook, View Shared Workbook
Changes, Highlight Changes, Resolve Conflicting Changes, Add a History
Worksheet, Review Tracked Changes, Merge Shared Workbook Files.
- Views
Create, Display and Delete a Custom View.
Consolidate Worksheets
Consolidating Worksheets by Category or by Position.
DAY THREE
Use Advanced Functions
Use the VLOOKUP and HLOOKUP
Function, Use the IF Function and Nested
IF Function, Use the ISERROR Function, Use an AND Condition with IF, Use
an OR Condition with IF, Use the ROUND Function, Limit the Precision of Numbers.
Create/Revise
PivotTables
Create a PivotTable Report, Add PivotTable Report Fields, Select a Report
Filter Field Item, Refresh a PivotTable Report, Change the Summary Function,
Add New Fields to a PivotTable.
Advanced Charting
Add, Remove and Format Gridlines,
Format an Axis, Change the Axis Scale,
Format the Data Series, Add Data - Different Worksheets, Use a Secondary
Axis, Change Data Series Chart Types, Add a Trendline, Create User-defined
Charts, Create, Customise, Remove Sparklines.
Use
Templates
Save a Workbook as a Template, Use a
Template, Edit a Template, Delete a
Template, Create Default Templates.
Use Auditing Tools
Display the Formula Auditing
Toolbar, Display/Remove Dependent and
Precedent Arrows, Remove Tracer Arrows, Use Auditing Tools Buttons.
DAY FOUR
Use Scenarios & Seeking
Goal
Use the Scenario Manager,
Create, Display and Edit a Scenario, Create a Scenario
Summary Report, Use Goal Seek.
Solve Problems
Use Solver, Save a Solution as a
Scenario, Change a Constraint, Create a
Solver Report, View Solutions using Scenarios.
Use Automatic Formatting &
Styles
Apply an AutoFormat, Change
AutoFormat Options, Extend List Formats
and Formulas, Create a Style by Example, Create a New Style, Edit an
Existing Style, Merge Styles.
Use Conditional and Custom Formats
Apply Conditional Formats,
Change a Conditional Format, Add a
Conditional Format, Create a Custom Conditional Format, Use Data Bars,
Delete a Conditional Format, Create a Custom Number Format.
Share Workbooks
Save a Shared Workbook, View
Users Sharing a Workbook, View Shared Workbook
Changes, Highlight Changes, Resolve Conflicting Changes, Add a History Worksheet, Review Tracked
Changes, Merge Shared Workbook Files.
Create/Revise PivotTables
Create a PivotTable Report, Add
PivotTable Report Fields, Select a Report
Filter Field Item, Refresh a PivotTable Report, Change the Summary
Function, Add New Fields to a PivotTable Report, Move PivotTable Report
Fields, Use Expand and Collapse Buttons, Hide/Unhide PivotTable Report
Items, Delete PivotTable Report Fields, Create Report Filter Pages, Inserting &
Using Slicers, Format a PivotTable Report, Create a PivotChart Report,
Publish PivotTable Reports to the Web.
DAY FIVE
Using, Recording
Report, Move PivotTable Report
Fields, Use Expand and Collapse Buttons, Hide/Unhide
PivotTable Report Items, Delete PivotTable Report Fields,
Create Report Filter Pages, Inserting & Using Slicers, Format a PivotTable
Report, Create a PivotChart Report, Publish PivotTable Reports to the Web.
Use, Record, Edit Macros
Change Macro Security Settings, Open a
Workbook Containing Macros, Run a
Macro, Use a Shortcut Key, Use the Visual Basic Editor Window, Record a
Macro, Assign a Shortcut key, Use Relative References, Delete a Macro, Write
a New Macro, Enter Macro Comments, Copy, Edit and Type Macro
Commands, Run a Macro from the Code Window.
Create a Macro Button
Use, Create, Format, Move/Size & Delete a Macro Button.