Microsoft Excel 2016 Intermediate-2D | IT Training & Certification | Info Trek
Respect Your Dreams
Follow through on your goals with courses

Microsoft Excel 2016 Intermediate-2D

Location

Format What’s this?
  1. 2 Days
  1. All of our private classes are customized to your organization's needs.
  2. Click on the button below to send us your details and you will be contacted shortly.
Request more information

Inquiry for: Myself    My Company

By providing your contact details, you agree to our Privacy Policy

 

 

 

Thank You

Our learning consultant will get back to you in 1 business day

Microsoft Excel 2016 Intermediate-2D

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

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

Expand All

Modules

Lesson 1 – Performing Calculations

Topic A: Reuse Formulas

  • Relative References
  • Absolute References
  • Mixed References
Lesson 2 – Creating Advanced Formulas

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
Lesson 3 – Organizing Worksheet Data with Tables

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
Lesson 4 – Working with Statistical, Mathematical, Logical and Lookup Functions

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
Lesson 5 - Visualizing Data with Charts

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
Lesson 6 - Showing Data as Graphic

Topic A: Conditional Formatting

  • Highlight Cell Rules
  • Using Data Bars
  • Using Icon Sets
  • Creating New Rules with Formula
Lesson 7 - Finalize Workbook

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
Anne Teoh Hui San

Anne Teoh Hui San

A rising and highly engaging trainer, Anne Teoh represents a new face for Microsoft Office training. She has been involved in training Microsoft Windows 8 and Windows Phone development and design Microsoft Student Partners (MSP) for a year. Within her first year venture into the training industry, she is now highly sought after by banking, investment firms and high-tech industries; to execute competent Microsoft Office training. She specializes in delivering Office Word, Office Excel and Office PowerPoint trainings, having most of the occasion delivering beyond client expectations of what defines a training.Focusing heavily on delivering hands-on experience to her participants, she instils a robust understanding of the fundamentals for Microsoft Office training; as she strongly views that traditional delivery of training should be phased out and delivered in a lively manner that reflects the everyday routine of a given professional.Besides training, she is also as a deeply involved the development of Windows Phone Applications, mostly handling the UI/UX portion.


Read More

Muhamad Rezall Bin Roslan

Muhamad Rezall Bin Roslan

Rezall has been in the IT training industry for about 8 years. He has trained thousands of people on the use of Microsoft Word, Excel, PowerPoint. He spent 5 year working in the IT department of a well-known retail company where he was involved in infra technologies and related areas.

Rezall comes across as not only an approachable and reliable trainer but is also liked for his warm, cheerful and pleasant personality.

Read More

Azrulnizam Bin Ahmad

Azrulnizam Bin Ahmad

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.He is an energetic, independent, goal-oriented individual that has a high work standard and is committed to his work. He is also a good planner, is creative and resourceful, has great leadership charisma and works well with others. Read More

Jarina Muhamad Ismail

Jarina Muhamad Ismail

Since 2005, Jarina is involved in delivering learning programmes for Microsoft Excel, PowerPoint, Project, Outlook, Access, Word, Publisher and Visio. As a Microsoft Office Specialist (MOS) in all versions (2003-2013), she is using her expertise to provide timely solutions to the end-user which ranges from basic to advanced level including power users. She has experience in training and coaching various teams of professionals like executives, engineers, managers and directors to organize, analyze, create report and present business data more efficiently.
Read More

Sylvia Sharon Anthony

Sylvia Sharon Anthony

She began her career as a Corporate Trainer and her personal areas of expertise are wide; encompassing Database and Graphic applications. In addition to that; she also specializes in corporate coaching for government, multi-national companies to local SMEs.

Sylvia has acquired immense knowledge and skill in the training field. Since becoming a trainer, her work has involved design, delivery and developing training programmers. In her 16 years of experience in the training arena she has delivered and customized training programmers specifically to tailor for organization's unique training needs and to maximize the participant's learning outcomes.

As a trainer, Sylvia constantly strives to improve herself and learn new methods and skills. She is passionate about building new programs that meet the upcoming challenges of the IT industry. She is currently developing a customized Excel programme which will emphasize on the advanced features of Microsoft Excel i.e. Mastering Charts, Functions and Pivot Tables.

Read More

Kua Su-Ann

Kua Su-Ann

Su-Ann has been in the IT training industry for the 20 years and has trained thousands of people on the use of Microsoft Office. As Microsoft Office application is her area of specialization, she is an expert in all versions of the software and is capable of teaching at the basic, intermediate and advanced level. She has provided training to a variety of people: bankers, IT Personnel, young and old office workers, managers, senior managers, directors and corporate C-level executives. Su-Ann is the trainer of choice for many of our Blue Chip clients. Besides training, she has also been deeply involved in courseware development. In addition to developing generic courseware on Microsoft Office Applications, she has developed customized courseware to meet the unique requirements of the clients.


Read More

Course Reviews

No Remarks

0

0 Ratings