Microsoft Excel Intermediate

Learn Excel Intermediate from Microsoft Certified Trainer

Created byInfo Trek

Last updated 2/2019

English

What you'll learn

Using Microsoft Excel above beginner's level

Course content

16 sections • 59 lectures • 4h 26m total length

Relative Reference4:20
In this video, you will learn how to use Relative Reference in formula.
Absolute Reference3:50
In this video, you will learn how to use Absolute Reference in formula.
Mixed Reference4:05
In this video, you will learn how to use Mixed Reference in formula.

Count A3:09
In this video, you will learn how to use Count A formula. This formula will help us to count the number of cells.
Count IF2:57
In this video, you will learn how to use Count IF formula. This formula will help us to count the number of cells that match given criteria.
Count IFS5:09
In this video, you will learn how to use Count IFS formula to count the number of cells that match multiple criteria.
Average A2:32
In this video, you will learn how to use Average A formula to return average including numbers and texts.
Average IF3:14
In this video, you will learn how to use Average IF formula to average number of cells that match given criteria.
Average IFS4:49
In this video, you will learn how to use Average IFS formula to count average quantity that match multiple criteria.

DSum4:04
In this video, you will learn how to use D Sum function. It is use to get the total that meet the criteria of the database.
DCountA2:42
In this video, you will learn how to use D Count A function. It will help us to get the number of cells that meet criteria in the database.
DAverage2:37
In this video, you will learn how to use D Average function to get the average value that meet criteria in the database.
DMax2:37
In this video, you will learn how to use D Max function to analyse maximum value by matching some criteria.
DMin2:49
In this video, you will learn how to use D Min function to get the lowest value that meet certain criteria in the database.

Create Table4:13
In this video, you will learn how to create table and to keep database in the table format.
Adding, Deleting Records4:05
In this video, you will learn how to add or delete record in the table.
Total Row2:54
In this video, you'll learn how to quickly summerize table records.
Cleaning Duplicate Records3:20
In this video, you will learn the fastest way to clean up the duplicated records in the table.

Autofilter5:30
In this video, you will learn how to use Autofilter the records in the database.
Text Filter6:52
In this video, you will learn how to use Text filter in the database. It allows us to display records that begin with specific character, end with specific character or even contain or do not contain specific character.
Number Filter4:20
In this video, you will learn how to do Number filter for your record in the database. It allows you to display the record equal to certain value, less than certain value, greater than certain value and in between 2 values.
Date Filter3:13
In this video, you will learn how to do Date filter for your record in the database. It allows you to display the records on daily, weekly, monthly, quarterly and yearly basis.
Wildcard4:35
In this video, you will learn how to do wildcard filter for your record in the database. It allows you to filter the data base on the special text string and it allows you to display the record with the special string content.
Advance Filter5:50
In this video, you will learn how to do Advance filter the record in database.
Top 10 Filter4:00
In this video, you will learn how to do Top 10 filter by using numbers in your database.

List7:04
In this video, you will learn how to use the dropdown list for item name in Excel Data Validation features. It makes data entry easier and reduce error (typing mistake).
Numbers5:07
In this video, you will learn how to do Data Validation with numbers.
Date5:40
In this video, you will learn how to do Data Validation with date to limit the dates that can be entered in a cell and prevent invalid dates.
Text Length5:33
In this video, you will learn how to do Data Validation with text length to limit the number of character user can input in a cell.
Custom6:04
In this video, you will learn how to use Custom Data Validation to create special rule.

Requirements

You should be able to use Microsoft Excel at a beginner level
A computer with Microsoft Excel software installed

Description

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.

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.

Upon completion of this program, participants should be able to:

Create simple to complex formulas and functions, like:
COUNTIF Function
AVERAGEIF Function
SUMIF Function
IF Function
VLOOKUP Function
Filter data using Auto Filters
Create Chart
Apply Conditional Formatting

Who this course is for:

Anyone who wants to learn Microsoft Excel Intermediate level

Microsoft Excel Intermediate

What you'll learn

Explore related topics

Course content

Cell Reference In Formula3 lectures • 12min

Formula Auditing2 lectures • 5min

Statistical Function6 lectures • 22min

Mathematical Function2 lectures • 9min

Logical Function3 lectures • 13min

Database Function5 lectures • 15min

Working With Table4 lectures • 15min

Sorting Records1 lecture • 6min

Filter7 lectures • 34min

Data Validation5 lectures • 29min

Requirements

Description

Who this course is for: