ECDL Advanced Module 4 – Spreadsheets

About the Course

Designed for
Aimed at users who have a good working knowledge of Microsoft Excel’s basic and intermediate principles, this course provides candidates with practical experience of the topics listed in course outline. On completion, candidates should be equipped with the necessary skills to produce complex spreadsheets and integrate them with output from other Windows applications.

Teaching Approach
The teaching approach contains a mix of lectures, practical and one-to-one work and varies according to the people attending. For the most part, candidates are able to work at a pace which best suits them, and emphasis is placed upon quality of learning, rather than quantity.

Requirements
Those attending should have a good understanding of Microsoft Excel to ECDL basic level, be competent Windows users and be familiar with the basics of file and disk management (including directories). Prior completion of ECDL Module 4 is essential.

Course Material
A large part of each course is spent with a set of specially prepared exercises and projects so that those attending gain firm foundations through their own practical experience. Professionally presented course notes are provided and these may be retained for reference afterwards.

Duration
This training program is two days in duration or six evenings, 3 hours per evening, for groups only.

Module Content

Editing with Microsoft Excel
-Name cell range(s) in a worksheet
-Create custom number formats
-Use conditional formatting options
-Use paste special options
-Import a text file and delimit by comma, space or tab
-Freeze row and /or column titles
-Hide / unhide rows or columns
-Use sub-totalling features
-Use one-input or two-input Data tables / What-if tables
-Protect / unprotect a worksheet
-Protect / unprotect designated cells in a worksheet
-Add password protection to a spreadsheet

Data Handling
-Sort data by multiple columns
-Use advanced query / filter options
-Link data / chart between worksheets
-Link data / chart between spreadsheets
-Consolidate data in adjacent worksheets using a 3D sum function
-Use / edit a template
-Change angle of pie chart slices
-Delete a data series in a chart
-Modify the chart type for a defined data series

Using Functions
-Use date and time functions: TODAY; DAY; MONTH; YEAR
-Use mathematical functions: SUMIF; SUMPOSITIVE; ROUND
-Use statistical functions: COUNT; PURECOUNT; COUNTA; COUNTIF
-Use text functions: PROPER; UPPER; LOWER; CONCATENATE
-Use financial functions: FV; NPV; PMT; PV; RATE
-Use lookup and reference functions: HLOOKUP; VLOOKUP
-Use logical functions: IF; AND; OR; ISERROR
-Use available database functions: DSUM; DMIN; DMAX and DCOUNT
-Use nested functions

Analysis
-Create a Pivot Table or a Dynamic Crosstab using defined field names
-Create named Scenarios / Versions from defined cell ranges
-Trace precedent cells in a worksheet
– Trace dependent cells in a worksheet
– Display all formulas or view location of all formulas in a worksheet
– Add or remove worksheet comments
– Edit worksheet comments

Special Tools
-Record a simple macro
-Run a macro
-Assign a macro to a custom button on a toolbar