Transcription

ECDL MODULEADVANCEDSPREADSHEETSAdvanced Spreadsheet Software Level 3Syllabus Version 3.0

PurposeThis document details the syllabus for the Advanced Spreadsheets module. The syllabusdescribes, through learning outcomes, the knowledge and skills that a candidate for theAdvanced Spreadsheets module should possess. The syllabus also provides the basis for thepractice-based test in this module.Copyright 1997 - 2017 ECDL FoundationAll rights reserved. No part of this publication may be reproduced in any form except aspermitted by ECDL Foundation. Enquiries for permission to reproduce material should bedirected to ECDL Foundation.DisclaimerAlthough every care has been taken by ECDL Foundation in the preparation of this publication,no warranty is given by ECDL Foundation, as publisher, as to the completeness of theinformation contained within it and neither shall ECDL Foundation be responsible or liable forany errors, omissions, inaccuracies, loss or damage whatsoever arising by virtue of suchinformation or any instructions or advice contained within this publication. Changes may bemade by ECDL Foundation at its own discretion and at any time without notice.ECDL Foundation is a registered business name of The European Computer Driving Licence Foundation Limited. European Computer Driving Licence, ECDL andrelated logos are all registered Trade Marks of ECDL Foundation. All rights reserved. 1997-2017 ECDL FoundationRef: ECDL Advanced Spreadsheets - Syllabus - V3.0Page 2 of 6

Advanced SpreadsheetsThis module sets out advanced skills that can be used to produce sophisticated reports, to performcomplex mathematical and statistical calculations, and to improve productivity using a spreadsheetapplication.Module GoalsSuccessful candidates will be able to: Apply advanced formatting options such as conditional formatting and customisednumber formatting and handle worksheets. Use functions such as those associated with logical, statistical, financial andmathematical 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.CATEGORY1 FormattingSKILL SET1.1 Cells1.2 Worksheets2 Formulas andFunctions 1997-2017 ECDL Foundation2.1 Using Formulasand FunctionsREF.TASK ITEM1.1.1Apply conditional formatting.1.1.2Create and apply custom number formats.1.1.3Split text to columns.1.2.1Copy, move worksheets between spreadsheets.1.2.2Split a window. Move, remove split bars.1.2.3Hide, show rows, columns, worksheets.1.2.4Save a spreadsheet as a template, modify atemplate.2.1.1Use date and time functions: today, now, day,month, year.2.1.2Use logical functions: and, or, not.2.1.3Use mathematical functions: rounddown,roundup, sumif.2.1.4Use statistical functions: countif, countblank,rank.2.1.5Use text functions: left, right, mid, trim,concatenate.2.1.6Use financial functions: fv, pv, pmt.2.1.7Use lookup functions: vlookup, hlookup.Ref: ECDL Advanced Spreadsheets - Syllabus - V3.0Page 3 of 6

CATEGORY3 ChartsSKILL SET3.1 Creating Charts3.2 Formatting Charts4 Analysis4.1 Using Tables4.2 Sorting andFiltering 1997-2017 ECDL FoundationREF.TASK ITEM2.1.8Use database functions: dsum, dmin, dmax,dcount, daverage.2.1.9Create a two-level nested function.2.1.10Use a 3-D reference within a sum, average,minimum, maximum function.2.1.11Use mixed references in formulas.3.1.1Create a combined chart like: column and line,column and area.3.1.2Create, change, delete a sparkline.3.1.3Add a secondary axis to a chart.3.1.4Change the chart type for a defined data series.3.1.5Add, delete a data series in a chart.3.2.1Re-position chart title, legend, data labels.3.2.2Change scale of value axis: minimum, maximumnumber to display, major interval.3.2.3Change display units on value axis withoutchanging data source: hundreds, thousands,millions.3.2.4Format columns, bars, pie slices, plot area, chartarea to display an image.4.1.1Create, modify a pivot table/datapilot.4.1.2Modify the data source and refresh the pivottable/datapilot.4.1.3Filter, sort data in a pivot table/datapilot.4.1.4Automatically, manually group data in a pivottable/datapilot and rename groups.4.1.5Use one-input, two-input data tables/multipleoperations tables.4.2.1Sort data by multiple columns at the same time.4.2.2Create a customized list and perform a customsort.4.2.3Automatically filter a list in place.4.2.4Apply advanced filter options to a list.4.2.5Use automatic, manual outline features: group,ungroup, sub-total.Ref: ECDL Advanced Spreadsheets - Syllabus - V3.0Page 4 of 6

CATEGORYSKILL SET4.3 Scenarios5 Validating andAuditing5.1 Validating5.2 Auditing6 EnhancingProductivity6.1 Naming Cells6.2 Paste Special6.3 Linking,Embedding andImporting6.4 Automation7 Collaborative Editing 1997-2017 ECDL Foundation7.1 Reviewing andSecurityREF.TASK ITEM4.2.6Expand, collapse outline detail levels.4.3.1Create named scenarios.4.3.2Show, edit, delete scenarios.4.3.3Create a scenario summary report.5.1.1Set, edit validation criteria for data entry in a cellrange like: whole number, decimal, list, date,time.5.1.2Enter input message and error alert.5.2.1Trace precedent, dependent cells. Identify cellswith missing dependents.5.2.2Display all formulas in a worksheet, rather thanthe resulting values.5.2.3Insert, edit, delete, show, hide comments/notes ina worksheet locally, online.6.1.1Name cell ranges, delete names for cell ranges.6.1.2Use named cell ranges in a function.6.1.3Activate, deactivate the group mode.6.2.1Use paste special options: add, subtract, multiply,divide.6.2.2Use paste special options: values /numbers,transpose.6.3.1Insert, edit, remove a hyperlink.6.3.2Link data within a spreadsheet, betweenspreadsheets.6.3.3Update, break a link.6.3.4Import delimited data from a text file.6.4.1Record a simple macro like: change page setup,apply a custom number format, applyautoformats to a cell range, insert fields inworksheet header, footer.6.4.2Run a macro.6.4.3Assign a macro to a custom button.7.1.1Compare and merge spreadsheets.Ref: ECDL Advanced Spreadsheets - Syllabus - V3.0Page 5 of 6

CATEGORY 1997-2017 ECDL FoundationSKILL SETREF.TASK ITEM7.1.2Add, remove password protection for aspreadsheet: to open, to modify.7.1.3Protect, unprotect cells, worksheet with apassword.7.1.4Hide, unhide formulas.Ref: ECDL Advanced Spreadsheets - Syllabus - V3.0Page 6 of 6

4 Analysis 4.1 Using Tables 4.1.1 Create, modify a pivot table/datapilot. 4.1.2 Modify the data source and refresh the pivot table/datapilot. 4.1.3 Filter, sort data in a pivot table/datapilot. 4.1.4 Automatically, manually group data in a pivot table/datapilot and rename gr