Microsoft Excel – Advanced
Overview
The advanced course is designed to enable the generation of reports using a combination of advanced formulas and functions. The aim is to enable the building of complete systems using skills acquired from the intermediate level together with those newly acquired at the advanced level.
Objectives
At the end of the advanced course, participants should be able to use Excel’s advanced functions to generate dynamic and automated routine reports; generate reports using pivot tables and charts. Participants should also be able to manually and automatically outline data and generate another type of report using subtotals.
-
Advanced
-
18 Hours
GHC 650
Per Participant
Content
Getting the Most from your Data
- Using Automatic Outlining
- Displaying and Collapsing Levels
- Grouping Data Manually
- Creating Subtotals
- Scenario manager for sensitivity analysis
- What is a Scenario?
- Creating a Scenario
- Saving Multiple Scenarios
- Merging Scenarios
- Creating a Scenario Summary Report
- Enabling Data Analysis Tools
- An Overview of Excel’s Analysis Tools
- An Overview of the Available Add-In Tools
- Sensitivity Analysis with data tables
- Using a One or Two Input Data Tables
- Using Goal Seek
- What is a Hyperlink?
- Inserting Hyperlinks
- Editing Hyperlinks
- Formatting Hyperlinks
- Using Hyperlinks in Excel
Pivoting Data
- What is a PivotTable?
- Creating a PivotTable
- Using the PivotTable Tools Tabs
- Adding and Removing Data with the Field List
- Changing the Field List Layout
- Pivoting Data
- Expanding and Collapsing Data
- Filtering Data
- Sorting Data
- Grouping Data
- Refreshing Data
- Editing the Data Source
- Modifying Fields and Labels
- Modifying Values
- Using the Layout Group on the Design Tab
- Applying a Style to a PivotTable
- Changing PivotTable Style Options
- Manually Formatting a PivotTable
- Using the PivotTable Options Dialog
- Creating an Empty (Classic) PivotTable Frame
- Switching an Existing PivotTable to a Classic Layout
- Adding Data
- Pivoting Data
- Creating a PivotTable Based on External Data
- Refreshing External Data
- Creating a Slicer
- Using the Slicer Tools Tab
Charting Pivoted Data
- Creating a PivotChart from Scratch
- Creating a PivotChart from Existing Data
- Adding Data to your Chart
- Pivoting Data
- Using the Design Tab
- Using the Layout Tab
- Using the Format Tab
- Using the Analyse Tab
- Renaming Fields
- Changing the Chart Type
- Applying a Chart Style
- Manually Formatting Chart Elements
- Changing the Layout of Chart Elements
- Creating a PivotChart Based on External Data
- Creating a Slicer
- Creating a PivotTable and PivotChart from a Scenario
- Creating PivotCharts with PowerPivot Data
Advanced Excel Tasks
- Evaluating investments with net present value criteria
- Internal rate of return
- Functions for personal financial decisions
- PV; FV; PMT; IMPT
- Understanding Logical Functions
- Using Logical Functions
- Using IFERROR with Array Formulas
- Understanding VLOOKUP and HLOOKUP
- Using VLOOKUP to Find Data
- How to Find an Exact Match with VLOOKUP
- Finding an Approximate Match with VLOOKUP
- Using VLOOKUP as an Array Formula
- What is an AutoFill List?
- Creating a Custom AutoFill List
- Using a Custom AutoFill List
- Modifying a Custom AutoFill List
- Deleting a Custom AutoFill List
- Linking Workbooks
- Consolidating Workbooks
- Combining Worksheets
- Pivoting Consolidated Data
Macros
- Recording a Macro
- Editing a Macro
- Running a Macro
- Understanding Macro Security
Course Reviews
Facilitator(s)
Richard Lamptey
Richard Odartey Lamptey has worked in the private sector for a period spanning over eight (8) years. He has consulted...
Roland Ross Ewool Jnr.
Roland Jnr. is the Deputy Managing Director of Krol Consultancy Services. He joined the company in 2012 after working as...
