Training Type

• Classroom or Online Virtual Class

Duration, Hours

• 2 Days – 7 Hours each day

Course Overview

Description

Microsoft Excel is a software for storing numerical data and analyzing them. It is one of the most flexible and commonly used applications in Office. Whatever career path you choose, you will probably have to use Excel to achieve your professional goals, some of which may happen every day. For one way or the other, we all work with numbers. When we want to record, analysis and save such numeric data, Microsoft Excel comes in handy. At this Advanced level, it provides an overview of the Excel database management and basic analysis features used for preparing reports and analytics for our managers.

Learning Outcome and Goals

• Describe Excel Data thoroughly to prevent common mistake in Excel Reports
• Manage & Analyze Database/Excel List effectively
• Ability to connect data from another source/file
• Introduction to Macro Recording to automate repetitive task
• Manage worksheet & file Protection

Course Requirement

• Participants should be able to use a PC at the beginner level
• Basic knowledge and functionality of Microsoft Excel
• Microsoft Office 2013 and above

Course Information

Course Outline

Sorting and Filtering Data

• Sorting Data by Multiple Columns at the Same Time
• Applying a Pre-Installed Custom Sort
• Creating a Customized List and Performing a Custom Sort
• Sub-Totalling
• Removing Subtotals
• Using AutoFilter
• Using AutoFilter to Perform Multiple Queries
• Removing All AutoFilters from a Worksheet
• Advanced Filter Criteria

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

Importing Text Files

• What Is a Delimited Text File?
• Importing a Delimited Text File

Linking & Embedding

• 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

Hyperlinks

• Inserting a Hyperlink
• Editing a Hyperlink
• Removing a Hyperlink

Tracking and Reviewing Changes

• Enabling or disabling the ‘Track Changes’ Feature
• Sharing, Comparing and Merging Worksheets

Validating

• Data Validation – Whole Number
• Data Validation – List
• Data Validation – Text Length
• Customising a Validation Input Message and Error Alert
• Removing Data Validation

Auditing

• Tracing Precedent Cells
• Tracing Dependent Cells
• Cells with Missing Dependents
• Showing All Formulas in a Worksheet

Charts

• Creating a Combined Line and Column Chart
• Adding a Secondary Axis to a Chart
• Changing the Chart Type for a Data Series
• Moving and Formatting Chart Data Labels
• Modifying Chart Axis Scales
• Inserting Images into Chart Columns

Input Tables

• One-Input Data Tables
• Two-Input Data Tables

Scenarios

• Scenario Manager

Macros

• Macro to Change the Page Set-Up
• Macro to Apply a Custom Number Format
• Macro to Format a Cell Range
• Deleting Macros

Passwords & Security Issues

• Adding ‘Open’ Password Protection to a Workbook
• Removing an ‘Open’ Password from a Workbook
• Password Protecting Cells and Worksheets

Course Schedule

Schedule/Timing is subject to change according to the Participants Progress and Level of Knowledge.