IT and Computing (Live Online) > Microsoft Excel Intermediate

Microsoft Excel Intermediate

Course Objectives

Create simple to complex formulas and functions, like:
– COUNTA & COUNTIFS Function
– AVERAGEA &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 RecordsVisualizing Data as a Graphics

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.

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

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
Module 2: Working with Functions
Topic A: Data Summary with Function
– COUNTA
– AVERAGEA
Topic B: Conditional Data Summary 1
– COUNTIF
– AVERAGEIF
– SUMIF
Topic C: Conditional Data Extraction
– IFERROR
– IF
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
Module 4: Visualizing Data as a Graphics
Topic A: Conditional Formatting
– Highlight Cell Rules
– Using Data Bars
– Using Icon Sets
– Creating New Rules with Formula
Topic B: Create Charts
– 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
– 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
Module 5: Setting Up Reports as Pages
Topic A: Managing Multiple Windows
– Arranging Workbooks
– Comparing Workbooks Side by Side
– Synchronous Scrolling and Resetting a Window

Get To Know The Trainer

Trainer CK Ong

4.5

CK has been involved in Microsoft Office Consulting for more than 15 years with the intention of helping friends and working adults to reach their full capability which in return will increase their work productivity. Throughout his many years of working experience using Microsoft Office Applications, he participated with various projects involving Engineering Data Analysis, A Complete System for Employees such as Leave Application, Petty Cash Claim, Arranging Schedule and so on with Programming using VBA Programming.

VIEW PROFILE