Microsoft Excel 2016 Intermediate
Private Class From
RM1100
/day
(2 Days)
COURSE OBJECTIVES

Upon completion of this program, participants should be able to:

  • Create simple to complex formulas and functions, like:
  • Database Function
  • COUNTIF Function
  • COUNTIFS Function
  • AVERAGEIF Function
  • AVERAGEIFS Function
  • SUMIF Function
  • SUMIFS Function
  • IF Function
  • AND Function
  • VLOOKUP Function
  • Validate data in a Worksheet
  • Filter data using Auto & Advanced Filters
  • Create Chart
  • Clean Duplicate Records
  • Apply Conditional Formatting
  • Protect Worksheet & Workbook
AUDIENCE

This course is designed for Clerks, Officers, Executives, Supervisors, Administrators, Managers of all levels; and personnel who already know and understand and want to further enhance their knowledge and practical uses of Microsoft Excel.

PREREQUISITES
REQUIRED PREREQUISITES:

Basic knowledge of Microsoft Excel is essential with the following pre-requisites:

  • Have attended Microsoft Excel – Foundation Level; OR
  • Able to switch between task applications
  • Able to create a spreadsheet with simple formatting
  • Able to create a basic chart
  • Able to print a spreadsheet with headers and footers added
  • Able to apply Freeze Pane command
  • Able to create basic formulas – Addition, Subtraction, Multiplication and Division
  • Able to use basic functions – AutoSum, Count, Max, Min and Average functions.
METHODOLOGY

This program will be conducted with interactive lectures, PowerPoint presentation, discussions and practical exercise

Topic A: Reuse Formulas

  • Relative References
  • Absolute References
  • Mixed References

Topic A: Apply Range Names

  • Adding Range Names Using the Name Box
  • Adding Range Names Using the New Name Dialog Box
  • Editing a Range Name and Deleting a Range Name
  • Using Range Names in Formulas
  • The Go to Command

Topic B: Use Specialized Functions

  • Showing and Hiding Formulas

Topic A: Create and Modify Tables

  • Tables
  • Table Components
  • The Create Table Dialog Box
  • The Table Tools – Design Contextual Tab
  • Styles and Quick Style Sets
  • Customizing Row Display
  • Table Modification Options

Topic B: Sort and Filter Data

  • The Difference Between Sorting and Filtering
  • Sorting Data
  • Advanced Filtering
  • Filter Operators
  • Removing Duplicate Values

Topic C: Use Subtotal and Database Functions to Calculate Data

  • SUBTOTAL Functions
  • The Subtotal Dialog Box
  • Summary Functions in Tables
  • Database Functions

Topic D: Validating your Data

  • Data Validation using Lists
  • Data Validation using Date
  • Data Validation using Formulas

Topic A: Statistical Functions

  • COUNTIF Function
  • COUNTIFS Function
  • AVERAGEIF Function
  • AVERAGEIFS Function

Topic B: Mathematical Function

  • SUMIF Function
  • SUMIFS Function

Topic C: Use Logical Functions

  • The IF Function
  • The AND Function
  • The IFERROR Function
  • NESTED IF

Topic D: Use Lookup Functions

  • Understanding VLOOKUP
  • 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

Topic A: Create Charts

  • Charts
  • Chart Types
  • Chart Insertion Methods
  • Resizing and Moving the Chart
  • Adding Additional Data
  • Switching Between Rows and Columns

Topic B: Modify and Format Charts

  • The Difference Between Modifying and formatting
  • Chart Elements
  • Minimize Extraneous Chart Elements
  • The Chart Tools Contextual Tabs
  • Formatting the Chart with a style
  • Adding a Legend to the chart

Topic C: Sparkline

  • What are Sparklines?
  • Adding Sparklines
  • Editing Sparkline Data
  • Removing Sparklines
  • Showing and Hiding Data
  • Changing the Style
  • Changing the Sparkline and Marker Color
  • Setting Sparkline Options

Topic A: Conditional Formatting

  • Highlight Cell Rules
  • Using Data Bars
  • Using Icon Sets
  • Creating New Rules with Formula

Topic A: Worksheet and Workbook Protection

  • The Protect Worksheet Option
  • The Protect Workbook Option
  • Allow users to Edit Ranges

Topic B: Managing Multiple Windows

  • The Arrange All Command
  • The Arrange Windows Dialog Box
  • The View Side by Side Command
  • The New Windows Command