II. MICROSOFT EXCEL ADVANCED 

Introduction

The main focus of the training is to analyse data and summarise data. Participants will learn in-depth how to work with Pivot Table to analyse, summarise, sort and filter data. Participants will also learn how to perform an advanced lookup to summarise data, consolidate and subtotal data, filter, and validate data. Finally, what if analysis will be performed on data.

Learning Outcome

At the completion of the training, candidates will be able to:

• Use Microsoft Excel pivot tables
• Use Microsoft Excel input tables
• Create Microsoft Excel charts
• Link & embedding within Microsoft Excel
• Import text files into Microsoft Excel
• Perform Sorting, filtering & totaling data within Microsoft Excel
• Perform Validating within Microsoft Excel
• Perform Auditing techniques in Microsoft Excel
• Create Microsoft Excel passwords & understand security issues

Pre-Requisite

Completion of Microsoft Excel Intermediate or equivalent knowledge

Who Should Attend

This course is suitable for users who are required to analyze data or generate reports using Microsoft Excel

Duration

2 Days

Training Course Outline

Module 1 - Auditing Techniques in Microsoft Excel

• Tracing precedent cells
• Tracing dependent cells
• Identifying cells with missing dependents
• Showing all formulas in a worksheet, rather than the resulting values
• Inserting and viewing comments
• Editing comments
• Deleting comments
• Showing and hiding comments

Module 2 - Selected Functions

• IF, OR AND logic functions
• LOOKUP functions
• DATABASE functions

Module - 3 Microsoft Excel Pivot Tables

• Creating and using a pivot table
• Filtering and sorting data within a pivot table
• Automatically grouping data in a pivot table and renaming groups
• Manually grouping data in a pivot table and renaming groups

Module - 4 Microsoft Excel Input Tables

• One input data tables
• Two input data tables

Module - 5 - Linking & Embedding Within Microsoft Excel

• What is embedding and linking?
• Linking data within a worksheet
• Linking cells between worksheets within a workbook
• Linking data between workbooks
• Linking data from Excel to a word document
• Linking an Excel chart to a word document
• Updating, locking and breaking links

Module 6 - Sorting, Filtering, and Totalling Data Within Microsoft Excel

• Sorting data by multiple columns at the same time
• Applying a preinstalled custom sort
• Creating a customized list and performing a custom sort
• Deleting an Excel customised list
• Using AutoFilter
• Using AutoFilter to perform multiple queries
• Top 10 AutoFilter
• Removing all AutoFilters from a worksheet
• Advanced filter criteria
• Subtotalling
• Removing subtotals
• Expanding and collapsing outline detail levels

Module - 7 Validating Within Microsoft Excel

• Data validation – whole number
• Data validation – decimal number
• Data validation – list
• Data validation – date
• Data validation – time
• Data validation – text length
• Customising a validation input message and error alert
• Removing data validation

Module 8 - Microsoft Excel Passwords & Security Issues

• Adding ‘open’ password protection to a workbook
• Adding ‘modify’ password protection to a workbook
• Removing an ‘open’ password from a workbook
• Removing a ‘modify’ password from a workbook
• Password protecting cells and worksheets
• Hiding formulas
• Un-hiding formulas

Module 9 - Microsoft Excel

• Creating a combo line and column chart
• Changing the chart type for a particular data series
• Adding a data series to a chart
• Removing a data series from a chart
• Repositioning chart title
• Repositioning the chart legend
• Moving and formatting chart data labels
• Modifying chart axis scales
• Formatting an axis to display using commas
• Inserting images into chart columns
• Formatting the chart plot area using a picture
• Formatting the chart area using a picture