Microsoft Excel 2010/ 2013 (Advanced)

Duration: 3 Days
Contact Hours: 21

---

Course Mode

• Lectures
  
• Hands-on
• Mini-project

---

Target Audience

• Office administrator
• Secretaries
• Managers
• General PC users who intend to enhance knowledge & ability to support more advanced features in Microsoft Excel
  

---

Benefits

• Fast track from zero-to-hero on Microsoft Excel advanced
• End-to-end of transforming raw data into desired message in the form of charts/ tables
• Delegates are shown the strategy to uncover the solution
• At the end of the training, delegates have the opportunity to showcase what they learned as a mini-project on the topic of their choice

---

Course Overview

Quick Refresher on Excel 101

• Working with Worksheets
  
• Working with Rows and Columns
  
• Working with Cells and Ranges
  
• Copy & Paste
  
• Working with Tables
  
• Formatting & Style
  
• Borders & Lines
  

Conditional Formatting

• Conditional format with data bars
  
• Conditional format with icon sets
  
• Conditional format with formulas
  
• Conditional format with colour scales
  
• Conditional format with cell rules
  

Function

• Date & time function
  
• Text function
  
• Sum & count function
  
• Information function
  
• Lookup & reference function
  
• Math & trigonometry function
  

Advanced Function

• Using lookup functions: VLOOKUP, HLOOKUP, LOOKUP
  
• Using logic function: IF, AND, OR, NOT
  

Managing data in list

• Sorting database list
  
• Filtering database list
  
• Subtotal data
  
• Using functions with lists
  
• Using advanced filtering and AutoCalculate
  

Performing Calculation on Named Groups

• Understand relatives and absolute reference
  
• Naming methods
  
• Creating and redefining range names
  
• Creating formulas with range names
  

Data Validation

• What is data validation?
  
• Types of validation criteria
  

Data Analysis

• Consolidate data in single workbook
  
• Consolidate data in multiple workbook
  
• Subtotal
  

Retrieving External Data

• Different types of text files
  
• Importing delimited text files
  
• Importing fixed width text files
  
• Parsing a column of data
  
• Importing external data
  
• Importing data from a database using Microsoft Query
  

Presenting Data in Charts

• Overview of Excel charting features
  
• Creating chart
  
• Charts modifications
  
• Creating combination chart with secondary axis
  

Pivot Table

• Pivot table
  
• Create pivot table
  
• Interactive pivot table - Slicer
  

Simple Macro

• Automate routine manual tasks – copying to transposed table and converting formulas to values with a macro
  
• Assigning macro to Quick Access Toolbar
  
• Attaching macro to a command button
  

---