Excel
Level: Intermediate

Microsoft

REQUIRED LEVEL:

Basic knowledge of Excel’s functions.

DURATION:

1 day

OBJECTIVES:

– Easily manage databases.
– Work with multiple sheets or workbooks.
– Sort and create complex filters.
– Add conditional formatting, drop-down lists and graphics.

PROGRAM:

REVIEW OF THE BEGINNER COURSE

• Review on simple formulas: (Statistics: Average, COUNT, COUNTA, MAX, MIN)
• Freeze the panes and titles for printing
• Absolute and relative references
• Copy a formula effectively

CHART

• Create a chart
• Modify data
• Arrangement and formatting of a chart
• Sparkline chart

CONDITIONAL FORMATTING

• Create a simple formatting
• Icon set, color scheme
• Manage conditions

MULTI-SHEET

• Group and dissociate sheets
• Multi-sheet operations
• Operation from one sheet to another with link
• Identical formatting for some or all sheets
• View multiple sheets of the same workbook
• Hide, un-hide a sheet
• Change the color of a sheet
• Consolidation
• Protect a sheet

MANAGING A DATABASE

SORT AND FILTER

• Personalized sorting
• Sort by font color, filling and icon sets
• Simple filter
• Custom filter
• Advanced filter

SUBTOTALS

• With one or more functions
• On one or more columns

CUSTOM DISPLAYS

• Set a view (Repetitive)

REMOVING DUPLICATES
CONVERTING DATA

DATA VALIDATION

• Create validation
• Error message

NAME MANAGER

• Set a name
• Change or delete a name

Formation CAD

Tél. : (514) 316-6824

Courriel : [email protected]

Web : www.formation-cad.com

Intermediate

Basic knowledge of Excel’s functions.

Easily manage databases.
– Work with multiple sheets or workbooks.
– Sort and create complex filters.
– Add conditional formatting, drop-down lists and graphics.

REVIEW OF THE BEGINNER COURSE

• Review on simple formulas: (Statistics: Average, COUNT, COUNTA, MAX, MIN)
• Freeze the panes and titles for printing
• Absolute and relative references
• Copy a formula effectively

CHART

• Create a chart
• Modify data
• Arrangement and formatting of a chart
• Sparkline chart

CONDITIONAL FORMATTING

• Create a simple formatting
• Icon set, color scheme
• Manage conditions

MULTI-SHEET

• Group and dissociate sheets
• Multi-sheet operations
• Operation from one sheet to another with link
• Identical formatting for some or all sheets
• View multiple sheets of the same workbook
• Hide, un-hide a sheet
• Change the color of a sheet
• Consolidation
• Protect a sheet

MANAGING A DATABASE

SORT AND FILTER

• Personalized sorting
• Sort by font color, filling and icon sets
• Simple filter
• Custom filter
• Advanced filter

SUBTOTALS

• With one or more functions
• On one or more columns

CUSTOM DISPLAYS

• Set a view (Repetitive)

REMOVING DUPLICATES
CONVERTING DATA

DATA VALIDATION

• Create validation
• Error message

NAME MANAGER

• Set a name
• Change or delete a name