Advanced

Basic knowledge of Excel’s functions.

Be able to build advanced formulas and perform complex calculations.
– Create and use a pivot table.
– Perform VLOOKUP

FUNCTIONS

Statistical

• Review on simple formulas
• Absolute and relative references
• Statistics: Average, COUNT, COUNTA, MAX,
• MIN, COUNTIF, AVERAGEIF

Math and trigonometry

• SUMIF
• INT (age calculation)
• ROUND, ROUNDDOWN, ROUNDUP

Text

• UPPER, LOWER, PROPER
• CONCATENATE
• LEFT, RIGHT
• LEN

Date

• TODAY, NOW
• YEAR, MONTH, DAY, NETWORKDAYS

Logic

• Simple logic IF
• Complex logic: IF with several logics, AND, OR

Search and reference

• VLOOKUP

Other functions

• PV, NPER, VPMT
• ISBLANK

Formula audit

• Identify dependents and precedents

VALIDATION

• Create validation
• Create an error message

NAME MANAGER

• Set a name
• Change or delete a name

CLIPBOARD OPTIONS

• Paste, paste with link
• Paste formula, value, etc.

DATA PROTECTION

• Protect the workbook
• Allow some users to change ranges

PIVOT TABLES

List of fields

• Line Labels
• Filter of the report
• Labels
• Values
• Pivot table options

Group selection

• By date, number, text

Creation tab

Options (Analysis)

• Change the source
• Update the data
• Show percentage values
• Segment and displays

CREATE A MODEL

Excel
Level: Advanced

Microsoft

DURATION:

1 day

REQUIRED LEVEL:

Basic knowledge of Excel’s functions.

OBJECTIVES :

– Be able to build advanced formulas and perform complex calculations.
– Create and use a pivot table.
– Perform VLOOKUP

PROGRAM:

FUNCTIONS

Statistical

• Review on simple formulas
• Absolute and relative references
• Statistics: Average, COUNT, COUNTA, MAX,
• MIN, COUNTIF, AVERAGEIF

Math and trigonometry

• SUMIF
• INT (age calculation)
• ROUND, ROUNDDOWN, ROUNDUP

Text

• UPPER, LOWER, PROPER
• CONCATENATE
• LEFT, RIGHT
• LEN

Date

• TODAY, NOW
• YEAR, MONTH, DAY, NETWORKDAYS

Logic

• Simple logic IF
• Complex logic: IF with several logics, AND, OR

Search and reference

• VLOOKUP

Other functions

• PV, NPER, VPMT
• ISBLANK

Formula audit

• Identify dependents and precedents

VALIDATION

• Create validation
• Create an error message

NAME MANAGER

• Set a name
• Change or delete a name

CLIPBOARD OPTIONS

• Paste, paste with link
• Paste formula, value, etc.

DATA PROTECTION

• Protect the workbook
• Allow some users to change ranges

PIVOT TABLES

List of fields

• Line Labels
• Filter of the report
• Labels
• Values
• Pivot table options

Group selection

• By date, number, text

Creation tab

Options (Analysis)

• Change the source
• Update the data
• Show percentage values
• Segment and displays

CREATE A MODEL