  ## Microsoft Excel 2016 Intermediate ## Quorse - Your Training Wholesaler

• Enjoy discounts up to 60% off!
• Chat with us Live to get a Quotation to see it for yourself!
• All courses are HDRF Claimable

Private Class from

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

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

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