SA-EFLI-2: Excel Focus Learning Intermediate

Name: SA-EFLI-2: Excel Focus Learning Intermediate
Brand: Microsoft

RM 600.00

This course is intended for participants who wish to gain more knowledge from the foundation level of Excel. Participants who are working with lots of formulas and create a report to understand the necessary technique on how an electronic spreadsheet works.

Focus Learning is the course that provides additional value by increasing the learning outcomes of your training efforts and providing additional performance returns on your investment. This course is conducted in a workshop format, which is training based entirely on instructor-led hands-on driven classes. Our trainers thoroughly guide and perform hands-on lab exercises with the attendees to give them the maximum learning experience possible.

Training Duration: 2 Days

Certificate Of Completion Available
Group Private Class
VILT Class Available
SBL-Khas Claimable

Download Course Outline

This course is intended for participants who wish to gain more knowledge from the foundation level of Excel. Participants who are working with lots of formulas and creates report to understand the necessary technique on how an electronic spreadsheet works.

Participants who need handling databases, using filter and sorting. Those who creates Pivot Table to have in depth understanding of how an electronic spreadsheet works.
Basic knowledge of Microsoft Excel is essential with the follow pre-requisites:

Able to maneuver with the mouse – Point, Click, Drag and Double Click

Able to switch between tasks applications

Able to create a spreadsheet with simple formatting

Able to “Create”, “Save”, “Open”, and “Print” spreadsheet

Able to use the “Undo” function

Able to insert and delete Row & Column

Able to print a spreadsheet with headers and footers applied

Able to Insert, Delete and Rename a worksheet

Able to create basic formulas like addition, subtraction, multiplication, and division

Able to use basic functions like Sum, Average, Count, Max, and Min function

Able to use functions like COUNTIFS, SUMIFS, IF, Nested IF, DATEDIF, EDATE, and NETWORKDAYS function
Upon completion of this program, participants should be able to Create simple to complex formulas and functions, like:

COUNTIFS Function

SUMIFS Function

IF Function

Nested IF Function

DATEDIF Function

EDATE Function

NETWORKDAYS Function

Validate data in a Worksheet

Filter data using Auto & Advanced Filters

Clean Duplicate Records

TRIM Function

Data Extraction Function

Change Case Functions

Use Subtotal Function

Create PivotTable
Unit 1: $ Symbols in Formulas, Effective Formatting, Logical Functions and Date Functions

Module 1: $ Symbols in Formulas & Functions
An absolute reference in Excel refers to a reference that is "locked" so that rows and columns will not change when copied. A mixed reference is a reference that refers to a specific row or column.

Topic 1: How to use the $ in your Formula?

Using Absolute Addressing formula

Using Mixed Referencing in formula

The Microsoft Excel COUNTIFS function counts the number of cells in a range, that meets a single or multiple criterion. The SUMIFS function allows summation of cell values that meets a single or multiple criterion.

Topic 2: Evaluate Data Using Statistical and Math Functions

Using COUNTIFS Function

Using SUMIFS Function

Module 2: Effective Formatting and Logical Functions
The Excel IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.

The IF function is one of the most popular functions in Excel, and it allows you to make logical comparisons between a value and what you expect. An IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.

Topic 1: Logical Function

IFERROR Function

IF Function

Nested If Function

Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell's value.

Topic 2: Conditional Formatting

Highlight Cell Rules

Using Data Bars

Using Icon Sets

Creating New Rules with Formula

Module 3: Date Formulas
In this session, you will learn date functions that is used to handle dates within Excel. Some the functions taught will be calculation between 2 dates, contract date expiry calculation and working day function.

Topic 1: Working with Date Functions

Date Calculation

Add A Number to Current Date to Get New Dates

Contract Date Expiry Calculation

Calculate a duration Between Two Dates

EDATE Function

NETWORKDAYS Function

Unit 2: Analyzing Data with PivotTable, Handling Database and Data Integrity with Text and Subtotal Functions

Module 1: Ensuring Database Integrity and Handling Text Data Functions
Data validation is a feature in Excel used to control what a user can enter a cell.

Topic 1: Data Validation

What Is Data Validation?

Validating Your Data

Types of Validation and Alerts

In this topic, you will learn text functions that is used to handle text within Excel. Some these functions will trim off excess spaces, combines text multiple ranges and/or strings, segregating data functions and change case function.

Topic 2: Handling Text Data

Segregate Data Using the Text to Columns Wizard

TRIM Function

SUBSTITUTE Function

CONCATENATE Function

Data Extraction Function

Change Case Functions

Module 2: Handling Database and Subtotal Function
Dynamic tables in excel are the tables where when a new value is inserted to it, the table adjust its size by itself. In this topic, you will learn how to filter records using Auto Filter. Normal or simple filter is used to only filter the selected data items.

Topic 1: Creating Table

Creating Table

Auto Filter

Styles and Quick Style Sets

Adding New Record

Creating New Field

Customizing Row Display

Table Modification Options

Summary Functions in Tables

Advance filter is used to filter selected data item like normal filter along with an additional feature (Copy filtered data in another location. Besides filter, you will learn to remove duplicate records and sorting of data.

Topic 2: Sort and Filter Data

Difference Between Sorting and Filtering

Sorting Data

Advanced Filtering

Removing Duplicate Values

The Microsoft Excel SUBTOTAL function returns the subtotal of the numbers in a column in a list or database.

Topic 3: Subtotals and Grouping Data

Creating Subtotals

Using Automatic Outlining

Grouping Data Manually

Displaying and Collapsing Levels

Module 3: Analyzing Data with PivotTable, Slicer and PivotChart
A pivot table is a data summarization tool that is used in the context of data processing. Pivot tables are used to summarize, sort, reorganize, group, count, total or average data stored in a database. It allows its users to transform columns into rows and rows into columns.

Topic 1: PivotTable

Create PivotTable Report

Show Values as Functionality of a PivotTable

Slicers provide buttons that you can click to filter PivotTables. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand what exactly is currently displayed.

Topic 2: Slicer

Insert Slicer Dialog Box

Filtering Data

Pivot Chart in Excel is an in-built tool in Excel which helps you out to summarize selected rows and column of data in a spreadsheet. It is the visual representation of a pivot table or any tabular data which helps to summarize & analyze the datasets, patterns, and trends.

Topic 3: PivotChart

Creating PivotChart

Applying a Style to a PivotChart

SA-EFLI-2: Excel Focus Learning Intermediate

AUDIENCE

PREREQUISITES

OBJECTIVES

COURSE MODULE

Recommended Courses Upon Completion

Frequently Asked Questions

Contact Us