Course Code:05.02
Course Subject:Advanced Spreadsheets
Course Title:ECDL Advanced (2v0) Spreadsheets (3-Day)
Aims:Candidate will be able to use the spreadsheet application to produce advanced spreadsheet outputs.
Introduction:The candidate shall be able to: 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 features; 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, macros and templates; Use linking, embedding and importing features to integrate data; Collaborate on and review spreadsheets. Apply spreadsheet security features..
  

Objectives

The learner will be able to:

Assessment Criteria

The learner has achieved this objective because he/she can:

1. Format Cells

  1. Apply an autoformat/table style to a cell range.

  2. Apply conditional formatting based on cell content.

  3. Create and apply custom number formats.

2. Format Worksheets

  1. Copy, move worksheets between spreadsheets.

  2. Split a window. Move, remove split bars.

  3. Hide, show rows, columns, worksheets.

3. Use Functions and Formulas

  1. Use date and time functions: today, now, day, month, year.

  2. Use mathematical functions: rounddown, roundup, sumif.

  3. Use statistical functions: countif, countblank, rank.

  4. Use text functions: left, right, mid, trim, concatenate.

  5. Use financial functions: fv, pv, pmt.

  6. Use lookup functions: vlookup, hlookup.

  7. Use database functions: dsum, dmin, dmax, dcount, daverage.

  8. Create a two-level nested function.

  9. Use a 3-D reference within a sum function.

10. Use mixed references in formulas.

4. Create Charts

  1. Create a combined column and line chart.

  2. Add a secondary axis to a chart.

  3. Change the chart type for a defined data series.

  4. Add, delete a data series in a chart.

5. Format Charts

  1. Re-position chart title, legend, data labels.

  2. Change scale of value axis: minimum, maximum number to display, major interval.

  3. Change display units on value axis without changing data source: hundreds, thousands, millions.

  4. Format columns, bars, plot area, chart area to display an image.

6. Analyse using Tables

  1. Create, modify a pivot table/datapilot.

  2. Modify the data source and refresh the pivot table/datapilot.

  3. Filter, sort data in a pivot table/datapilot.

  4. Automatically, manually group data in a pivot table/datapilot and rename groups.

  5. Use one-input, two-input data tables/multiple operations tables.

7. Analyse using Sorting and Filtering

  1. Sort data by multiple columns at the same time.

  2. Create a customized list and perform a custom sort.

  3. Automatically filter a list in place.

  4. Apply advanced filter options to a list.

  5. Use automatic sub-totalling features.

  6. Expand, collapse outline detail levels.

8. Analyse using Scenarios

  1. Create named scenarios.

  2. Show, edit, delete scenarios.

  3. Create a scenario summary report.

9. Validate

  1. Set, edit validation criteria for data entry in a cell range like: whole number, decimal, list, date, time.

  2. Enter input message and error alert.

10. Audit

  1. Trace precedent, dependent cells. Identify cells with missing dependents.

  2. Show all formulas in a worksheet, rather than the resulting values.

  3. Insert, edit, delete, show, hide comments/notes.

11. Enhance Productivity using named Cells

  1. Name cell ranges, delete names for cell ranges.

  2. Use named cell ranges in a function.

12. Enhance Productivity using Paste Special

  1. Use paste special options: add, subtract, multiply, divide.

  2. Use paste special options: values /numbers, transpose.

13. Enhance Productivity using Templates

  1. Create a spreadsheet based on an existing template.

  2. Modify a template.

14. Enhance Productivity by Linking, Embedding and Importing

  1. Insert, edit, remove a hyperlink.

  2. Link data within a spreadsheet, between spreadsheets, between applications.

  3. Update, break a link.

  4. Import delimited data from a text file.

15. Enhance Productivity through Automation

  1. 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.

  2. Run a macro.

  3. Assign a macro to a custom button on a toolbar.

16. Use Collaborative Editing - Tracking and Reviewing

  1. Turn on, off track changes. Track changes in a worksheet using a specified display view.

  2. Accept, reject changes in a worksheet.

  3. Compare and merge spreadsheets.

17. Use Collaborative Editing - Security

  1. Add, remove password protection for a spreadsheet: to open, to modify.

  2. Protect, unprotect cells, worksheet with a password.

  3. Hide, unhide formulas.