Training: ICDL ADVANCED – Spreadsheet – AM4

Ref. ECDL3-AM4

Download PDF

Duration:

2

days

Exam:

Optional

Level:

Advanced

Funding:

Temptraining

ITTA | Trainings | Office Automation | Office Automation Certifications | ICDL ADVANCED – Spreadsheet – AM4

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.