Microsoft Excel Intermediate

 

COURSE OVERVIEW

 

Microsoft Excel is an all-purpose software application for creating and sharing information through spreadsheets. With a robust set of functions to automatically perform complex calculations, features to rapidly convert data into charts and graphs and tools to securely share business information with to others, it is the most widely-used spreadsheet application worldwide.

Our Facilitators have extensive real-world experience to help the participants rapidly master the fundamentals of Excel, regardless of which version they are using.

TARGET AUDIENCE

For those who have little knowledge in Excel.

 

LEARNING OUTCOMES

Upon completion of the training, participants should be able to:

• Create reports using Excel
• Format worksheets, cells and numbers
• Handle absolute and relative cells for effective formulas settings
• Perform sorting and data filtering
• Use common functions to manipulate data
• Analyze and summarize data within seconds
• Create and customize chart with confidence
• Perform data analysis within minutes
• Perform data protection

 

COURSE OUTLINE

 

• Naming Techniques
  • Easier-to-Understand Formulas with Named Ranges
  • Assign name to a Constant
  • Name Manager
  • Worksheet scope vs Workbook scope

 

• Data Manipulation
  • Sorting
  • Tactics of Sorting
  • Custom Sorts
  • Auto Filter
  • Number, Date, Text Filters
  • Subtotal & Outlines

 

 

 

 

• Data Validation
  • Type of Data verification & Controls
  • Errors handling with Data Validation
  • Validate Text
  • Validate Number
  • Validate Date
  • Customize Error Message

 

• Conditional Formatting
  • Conditional Formatting
  • Rules Manager
  • Apply to number
  • Apply to text
  • Apply to date
  • Multi-conditional Formatting
  • Icon-Based

 

• Functions
  • Functions Arguments
  • Operators Precedence in Formulas
  • Text Functions
  • Number Functions
  • Date Functions
  • Conditional Control Functions
  • Lookup Functions
  • Functions application to real working environment
  • Introducing New Functions in 2010/ 2013

 

• Pivot Tables

• Introduction to Pivot Tables
• Introduction to Table
• Drag & Drop Techniques
• Sorting in Pivot Tables
• Pivot Fields Grouping
• Subtotals and Blank Lines Controls
• Summary Calculation
• Running Total

 

• Dashboarding
  • Create reports using Pivot Table
  • Create charts using Pivot Chart
  • Create Slicers
  • Design Dashboard

 

• Charting
  • Create and format a chart
  • Chart Elements
  • Data Series
  • Comparative Diagram
  • Combination Chart
  • Pie Chart
  • Creating Chart Template
  • Using Sparklines

 

• Protection & Hyperlinks
  • Tips of Tricks of Applying Password in Excel
  • Locked and Hidden Cells
  • Type of Hyperlinks
  • Hyperlinks to other application