Microsoft Excel 2013 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:
  • COUNTA, COUNTIF & COUNTIFS Function
  • AVERAGEA, AVERAGEIF & AVERAGEIFS Function
  • SUMIF & SUMIFS Function
  • IF Functions
  • Nested Functions
  • Database Function
  • Validate data in a Worksheet
  • Filter data using Auto & Advanced Filters
  • Advanced Chart Formatting
  • Clean Duplicate Records
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

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 use Auto Filter command
  • 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

Lesson 1.1 – Freeze Cell
  • Understanding Relative
  • Understanding Absolute Cell References
  • Understanding Mixed Cell References
Lesson 1.2 – Name Manager
  • What are Range Names?
  • Naming range
  • Define Name button
Lesson 1.3 – Formula Auditing
  • Understanding the Formula Auditing Buttons
  • Fixing Formula Errors
  • Modifying Error Checking Options
  • Displaying and Printing Formulas


Lesson 2.1 - Using Statistical Function
  • COUNTA Function
  • COUNTIF Function
  • COUNTIFS Function
  • AVERAGEA Function
  • AVERAGEIF Function
  • AVERAGEIFS Function
Lesson 2.2 - Using Mathematical Function
  • SUMIF Function
  • SUMIFS Function
Lesson 2.3 - Using Logical Function
  • IFERROR Function
  • IF Function
  • Nested If Function
Lesson 2.4 – Using Database Function
  • DSUM Function
  • DCOUNT Function
  • DAVERAGE Function
  • DMAX Function
  • DMIN Function


Lesson 3.1 - Working with Tables

  • What is a Table?
  • Creating Tables
  • Modifying Tables
  • Using the Table Tools – Design Tab
  • Adding a Total Row
  • Using Flash Fill
  • Using Quick Analysis

Lesson 3.2 - Manipulating Records

  • Quickly Add Records to a Table
  • Cleaning up Duplicate Records

Lesson 3.3 - Using Custom Lists

  • Creating a Custom AutoFill List
  • Using a Custom AutoFill List
  • Modifying a Custom AutoFill List
  • Deleting a Custom AutoFill List
  • Sorting with Custom List

Lesson 3.4 - Working with Filters

  • Number Filter
  • Text Filter
  • Date Filter
  • Filtering with Wildcard Characters

Lesson 3.5 - Working with Advanced Filters

  • Using an Advanced Filter
  • Using an Advanced Filter with Logical Statements
  • Copying Filtered Records

Lesson 3.6 – Data Validation

  • Validating your Data
  • Data Validation using Lists
  • Data Validation using Date
  • Data Validation using Formulas


Lesson 4.1 - Conditional Formatting

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

Lesson 4.2 - Advanced Chart Options

  • Use Recommended Chart
  • Changing the Source Data
  • Working with the Chart Axes and Data Series
  • Saving a Chart as a Template
  • Creating Combo Chart
  • Adding Secondary Axis
  • Fine Tune Charts Quickly
  • Richer Data Labels

Lesson 4.3 - Adding Sparklines

  • What are Sparklines?
  • Adding Sparklines
  • About the Sparkline Tools Tab
  • Editing Sparkline Data
  • Removing Sparklines

Lesson 4.4 - Editing Sparklines

  • Showing and Hiding Data
  • Changing the Style
  • Changing the Sparkline and Marker Color
  • Setting Sparkline Options


Lesson 5.1 - Finishing Workbook

  • Publishing to PDF or XPS
  • Protecting Current Sheet
  • Protecting Workbook
  • File Protection

Lesson 5.2 – Working with Different Views

  • Using Custom view
  • Using Page Layout View
  • Express Header & Footer

Lesson 5.3 - Managing Multiple Windows

  • Arranging Workbooks
  • Comparing Workbooks Side by Side
  • Synchronous Scrolling and Resetting a Window