Training: ICDL ADVANCED – Spreadsheet – AM4

Ref. ECDL3-AM4
Duration:
2
 days
Exam:
Optional
Level:
Advanced

Description

This training covers advanced spreadsheet skills, e.g. the analysis of data in tables, conditional formatting and custom number formats.

Participant profiles

  • Anyone who already have advanced knowledge of Excel spreadsheet and wish to obtain the ICDL certification

Objectives

  • Apply advanced formatting options such as conditional formatting and customised number formatting and handle worksheets
  • Use functions such as those associated with logical, statistical, financial and mathematical operations
  • Create charts and apply advanced chart formatting `Work with tables and lists to analyse, filter and sort data. Create and use scenarios
  • Validate and audit spreadsheet data
  • Enhance productivity by working with named cell ranges and macros
  • Use linking, embedding and importing features to integrate data
  • Compare and merge spreadsheets. Apply spreadsheet security features

Prerequisites

  • Follow or have the knowledge covered by ICDL Base – Spreadsheet course

Course content

Chapter 1: Formatting

  • Module 1: Cells
    • Apply conditional formatting
    • Create and apply custom number formats
    • Split text to columns
  • Module 2: Worksheets
    • Copy, move worksheets between spreadsheets
    • Split a window. Move, remove split bars
    • Hide, show rows, columns, worksheets
    • Save a spreadsheet as a template, modify a template

Chapter 2: Formulas and Functions

  • Module 1: Using Formulas and Functions
    • Use date and time functions: today, now, day, month, year
    • Use logical functions: and, or, not
    • Use mathematical functions: rounddown, roundup, sumif
    • Use statistical functions: countif, countblank, rank
    • Use text functions: left, right, mid, trim, concatenate
    • Use financial functions: fv, pv, pmt
    • Use lookup functions: vlookup, hlookup
    • Use database functions: dsum, dmin, dmax, dcount, daverage
    • Create a two-level nested function
    • Use a 3-D reference within a sum, average, minimum, maximum function
    • Use mixed references in formulas

Chapter 3: Charts

  • Module 1: Creating Charts
    • Create a combined chart like: column and line, column and area
    • Create, change, delete a sparkline
    • Add a secondary axis to a chart
    • Change the chart type for a defined data series
    • Add, delete a data series in a chart
  • Module 2: Formatting Charts
    • Re-position chart title, legend, data labels
    • Change scale of value axis: minimum, maximum number to display, major interval
    • Change display units on value axis without changing data source: hundreds, thousands, millions
    • Format columns, bars, pie slices, plot area, chart area to display an image

Chapter 4: Analysis

  • Module 1: Using Tables
    • Create, modify a pivot table/datapilot
    • Modify the data source and refresh the pivot table/datapilot
    • Filter, sort data in a pivot table/datapilot
    • Automatically, manually group data in a pivot table/datapilot and rename groups
    • Use one-input, two-input data tables/multiple operations tables
  • Module 2: Sorting and Filtering
    • Sort data by multiple columns at the same time
    • Create a customized list and perform a custom sort
    • Automatically filter a list in place
    • Apply advanced filter options to a list
    • Use automatic, manual outline features: group, ungroup, sub-total
    • Expand, collapse outline detail levels
  • Module 3: Scenarios
    • Create named scenarios
    • Show, edit, delete scenarios
    • Create a scenario summary report

Chapter 5: Validating and Auditing

  • Module 1: Validating
    • Set, edit validation criteria for data entry in a cell range like: whole number, decimal, list, date, time
    • Enter input message and error alert
  • Module 2: Auditing
    • Trace precedent, dependent cells. Identify cells with missing dependents
    • Display all formulas in a worksheet, rather than the resulting values
    • Insert, edit, delete, show, hide comments/notes in a worksheet locally, online

Chapter 6: Enhancing Productivity

  • Module 1: Naming Cells
    • Name cell ranges, delete names for cell ranges
    • Use named cell ranges in a function
    • Activate, deactivate the group mode
  • Module 2: Paste Special
    • Use paste special options: add, subtract, multiply, divide
    • Use paste special options: values/numbers, transpose
  • Module 3: Linking, Embedding and Importing
    • Insert, edit, remove a hyperlink
    • Link data within a spreadsheet, between spreadsheets
    • Update, break a link
    • Import delimited data from a text file
  • Module 4: Automation
    • Record a simple macro like: change page setup, apply a custom number format, apply autoformats to a cell range, insert fields in worksheet header, footer
    • Run a macro
    • Assign a macro to a custom button

Chapter 7: Collaborative Editing

  • Module 1: Reviewing and Security
    • Compare and merge spreadsheets
    • Add, remove password protection for a spreadsheet: to open, to modify
    • Protect, unprotect cells, worksheet with a password
    • Hide, unhide formulas

Documentation

  • Digital courseware included

Lab / Exercises

  • Practice exercises will be offered during and at the end of each module

Exam

  • This course prepares to the exam ICDL ADVANCED – Spreadsheet. If you wish to take this exam, please contact our secretariat who will let you know the cost of the exam and will take care of all the necessary administrative procedures for you

Complementary courses

Temptraining funding

ITTA is a partner of Temptraining, the continuing education fund for temporary workers. This training fund can subsidize continuing education for anyone who works for an employer subject to the Collective Work Agreement (CCT) Rental of services.
Prix de l'inscription
CHF 1'200.-
Inclus dans ce cours
  • Training provided by a certified trainer
  • Exam fee (Skills Card not included)
  • Pre-training level evaluation
  • Digital documentation

lun18Mar(Mar 18)09:00mar19(Mar 19)17:00VirtuelVirtual Etiquettes de sessionECDL3-AM4

lun18Mar(Mar 18)09:00mar19(Mar 19)17:00Lausanne, Avenue Mon repos 24, 1005 Lausanne Etiquettes de sessionECDL3-AM4

lun22Avr(Avr 22)09:00mar23(Avr 23)17:00VirtuelVirtual Etiquettes de sessionECDL3-AM4

lun22Avr(Avr 22)09:00mar23(Avr 23)17:00Genève, Route des Jeunes 33B, 1227 Carouge Etiquettes de sessionECDL3-AM4

lun27Mai(Mai 27)09:00mar28(Mai 28)17:00VirtuelVirtual Etiquettes de sessionECDL3-AM4

lun27Mai(Mai 27)09:00mar28(Mai 28)17:00Lausanne, Avenue Mon repos 24, 1005 Lausanne Etiquettes de sessionECDL3-AM4

lun01juil09:00mar02(juil 2)17:00VirtuelVirtual Etiquettes de sessionECDL3-AM4

lun01juil09:00mar02(juil 2)17:00Genève, Route des Jeunes 33B, 1227 Carouge Etiquettes de sessionECDL3-AM4

lun05Aoû(Aoû 5)09:00mar06(Aoû 6)17:00VirtuelVirtual Etiquettes de sessionECDL3-AM4

lun05Aoû(Aoû 5)09:00mar06(Aoû 6)17:00Lausanne, Avenue Mon repos 24, 1005 Lausanne Etiquettes de sessionECDL3-AM4

lun09Sep(Sep 9)09:00mar10(Sep 10)17:00VirtuelVirtual Etiquettes de sessionECDL3-AM4

lun09Sep(Sep 9)09:00mar10(Sep 10)17:00Genève, Route des Jeunes 33B, 1227 Carouge Etiquettes de sessionECDL3-AM4

lun14Oct(Oct 14)09:00mar15(Oct 15)17:00VirtuelVirtual Etiquettes de sessionECDL3-AM4

lun14Oct(Oct 14)09:00mar15(Oct 15)17:00Lausanne, Avenue Mon repos 24, 1005 Lausanne Etiquettes de sessionECDL3-AM4

lun18Nov(Nov 18)09:00mar19(Nov 19)17:00VirtuelVirtual Etiquettes de sessionECDL3-AM4

lun18Nov(Nov 18)09:00mar19(Nov 19)17:00Genève, Route des Jeunes 33B, 1227 Carouge Etiquettes de sessionECDL3-AM4

lun23Déc(Déc 23)09:00mar24(Déc 24)17:00VirtuelVirtual Etiquettes de sessionECDL3-AM4

lun23Déc(Déc 23)09:00mar24(Déc 24)17:00Lausanne, Avenue Mon repos 24, 1005 Lausanne Etiquettes de sessionECDL3-AM4

Horaires d’ouverture

Du lundi au vendredi, de 8:30 à 18:00.

Contactez-nous

Votre demande

Opening hours

Monday to Friday, from 8:30 am to 06:00 pm.

Contact us

Your request

X
X
X
X