Microsoft Excel 2019 Intermediate

Course Objectives

  • Create simple to complex formulas and functions, like:
    o COUNTA & COUNTIFS Function
    o AVERAGEA &AVERAGEIFS Function
    o SUMIF & SUMIFS Function
    o IF Functions
    o Nested Functions
    o Database Function
  • Validate data in a Worksheet
  • Filter data using Auto & Advanced Filters
  • Advanced Chart Formatting
  • Clean Duplicate Records
  • Visualizing Data as a Graphics

Target Audience

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 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.
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.

Methodology

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

Course Modules

  • Module 1: Referencing Calculations

    Topic A: Formula Reference
    • Relative References
    • Absolute References
    • Mixed References

    Topic B: Apply Range Names
    • Range Names
    • Adding Range Names Using the Name Box
    • Adding a Range Names Using Define Name
    • Adding multiple names using Create from Selection
    • Editing and Deleting Range Name Using Name Manager
    • Using Range Names in Formulas

  • Module 2: Working with Functions

    Topic A: Data Summary with Function
    • COUNTA
    • AVERAGEA

    Topic B: Conditional Data Summary 1
    • COUNTIF / COUNTIFS
    • AVERAGEIF / AVERAGEIFS
    • SUMIF / SUMIFS
    • MAXIF
    • MINIF

    Topic C: Conditional Data Extraction
    • IFERROR
    • IF
    • IFS or Nested IF

    Topic D: Conditional Data Summary 2
    • DSUM
    • DCOUNT / DCOUNTA
    • DAVERAGE
    • DMAX
    • DMIN

  • Module 3: Organizing Dynamic Data Range with Tables

    Topic A: Create and Modify Tables
    • Create table
    • Styles and Quick Style Sets
    • Removing Duplicate Values

    Topic B: Sort and Filter Data
    • Sorting Data
    • Filtering Data
    • Advanced Filtering

    Topic C: Use Subtotal to Calculate Data
    • Total Row with SubTotal Functions

    Topic D: Data Validation
    • Data Validation Using Lists
    • Data Validation Using Whole Number / Decimal
    • Data Validation Using Date
    • Data Validation Using Formulas

  • Module 4: Data Visualization

    Topic A: Conditional Formatting
    • Highlight Cell Rules
    • Data Bars
    • Icon Sets
    • Creating New Rules with Formula

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

    Topic C: Modify and Format Charts
    • Chart Elements
    • Minimize Extraneous Chart Elements
    • The Chart Tools Contextual Tabs
    • Formatting the Chart with a Style
    • Adding a Legend to the Chart

    Topic D: Adding Sparklines
    • Adding Sparklines
    • Editing Sparkline Data
    • Removing Sparklines

    Topic E: Editing Sparklines
    • Showing and Hiding Data
    • Changing the Style
    • Changing the Sparkline and Marker Color
    • Setting Axis Options

  • Module 5: Setting Up Reports as Pages

    Topic A: Working with Different Views
    • Using Page Break Preview for Page Setup
    • Using Page Layout View for Express Header & Footer
    • Using Custom view

    Topic B: Managing Multiple Windows
    • Arranging Workbooks
    • Comparing Workbooks Side by Side
    • Synchronous Scrolling and Resetting a Window

Get To Know The Trainer

Trainer Slyvia

 

Sylvia has been in the IT Training Industry for the past 20 years She began her career as a Corporate Trainer and her personal areas of expertise are wide; encompassing Microsoft and Adobe applications. In addition to that; she also specializes in corporate coaching for government, multi-national companies to local SMEs

Trainer Nazarudin

 4.5

Nazarudin Aziz started freelancing as a Corporate Trainer since the year of 2005 specializing in the field of Multimedia Graphic Designer and Microsoft Office Applications. His list of clients are Perodua, Grand Pacific Hotel, Cosway, UDA Mall, Institut Teknologu Rekabentuk Perabot Malaysia, Telekom Malaysia, Lembaga Tabung Haji, Yayasan Pelajaran MARA, Kementerian Pembangunan Wanita & Masyarakat, Hospital Tengku Ampuan Rahimah Klang, United Engineers Malaysia, Malaysian Maritime & Dredging Corporation, Jabatan Perdana Menteri, Indah Water Consotrium and many more.

Trainer Azrul

 

Azrulnizam Ahmad has been working in the business industry since 2004 and has been exposed to all the common challenges faced by business personnel. With his years of experience working with business and management, IT services and end users, he understands the importance of business value, infrastructure efficiency and user productivity.He shares his training passion in Microsoft Office 2007 Application - Excel, PowerPoint, and Word; Microsoft Office 2010 Application - Excel, PowerPoint, and Word and also emphasizes on the criticality of this aspect in any implementation

Trainer Pauline (copy)

 4.5

She is an expert on Microsoft Applications, Publisher (Version 2000, XP, 2003 & 2007, VBA, Introduction To Internet, Multimedia Fundamentals, IT Skills For Non-PC Users, Basic IT skills and related programs. In her previous company, she was responsible for developing training courseware and customizing training curriculum.

Trainer Beatrice

 4.5

Beatrice has been in the training industry for the past 27 years being one of the pioneer trainers of Microsoft Office training programs. She has vast experience having trained many people from various corporate sectors. She is a certified Microsoft Office training consultant and is accredited by Pembangunan Sumber Manusia Berhad (PSMB/HRDF).

  View more trainers

Chat with us LIVE to get a

FREE QUOTATION!

COURSE INCLUDES

COURSE BENEFITS

ALL IN FROM ONLY

RM0/PAX

*T&C Applies

PARTICIPANT REVIEWS FOR THIS COURSE

4.9

  • 93%
  • 7%
  • 0%
  • 0%
  • 0%
  • NJ
    Nor Laila Jamal
    Assistant Manager, Operations

    5

  • BP
    Baskar Patrick
    Manager / Supervisor, Sales

    5

  • SY
    Sharifah Shahidah Syed Yusof
    Manager / Supervisor, Sales

    5

  • NA
    Nor Dalila binti Ahmad Afandi
    Executive, Sales

    5

    Good training, easy to understand, more reliable
  • NA
    Noorhuda binti Ariffin
    Senior Manager, Operations

    5

  • SS
    SUHAIDA LAILY BINTI SULAIMAN
    Senior Manager, Operations

    5

    Sylvia has an excellent experience with highly patience! Passionate with the teaching.
  • BY
    Bernard Ooi Zhi Yong
    Executive, Sales

    5

    Good Course which helps me to improve my productivity
  • SU
    Siti Aminah Umar
    Executive, Sales

    5

    I love to learn about excel
  • BG
    Ban Keng Giam
    Executive, Sales

    5

  • LJ
    Lee Wee Jan
    Executive, Sales

    5

  • MJ
    Muhamad Fairuz Rizwan Bin Jemahar
    Junior Executive, Sales

    5

  • KL
    Kalaivani Lingam
    Junior Executive, Operations

    5

    Madam Sylvia is the best
  • AR
    Amir Danial Putera bin Mustapha Rounal
    Executive, Sales

    5

  • NS
    Nadeeya Eli Syaheerah binti Abd Shukor
    Director, Sales

    5

  • AA
    Alaa' Aimuni
    Junior Executive, Administration / Secretarial

    4

OF 2Next

Courses you may like

MEMBERS ONLY FEATURE

Kindly sign-up or log-in to get access.

LOGIN

Login with your social account

or

Don’t have an account with us?

FREE FOR LIFE

Sign Up to Bookmark your Favourite Course
or Request for a Quotation instantly

Sign in with your social account

or

By signing up, you agree to our Terms of Use and Privacy Policy

Have an account with us already?