ECDL Courseware - ICDL Courseware

ECDL / ICDL Advanced AM4
Advanced Level Spreadsheets
Approved Courseware Training Resources

1. EDITING

DATA

What does naming a cell range mean?
Rules for naming cells and ranges
To name cells
To navigate workbooks using named ranges
To create named ranges based on cell values
To delete named cells/ranges
To AutoFormat a cell range
To remove the effects of AutoFormat
To create a custom number format
Using custom number formatting codes
Example custom number formatting
What is 'conditional formatting'?
To format a range of cells using conditional formatting
To use the Paste Special command
'Paste Special' options
To transpose data
Why import data?
Common Text formats
What is the Text Import Wizard?
To import text into a worksheet using the Text Import Wizard, and delimit by space, comma or tab

DISPLAY

To freeze column titles
To freeze row titles
To freeze row and column titles at the same time
To unfreeze rows or columns
To hide rows
To hide columns
To re-display hidden columns
To re-display hidden rows
To hide worksheets
To re-display hidden worksheets
To create subtotals
To remove subtotals
What are data tables?
To use a one input Data Table command
To use a two input data table command

PROTECTION

What is 'worksheet protection'?
To protect a worksheet
To un-protect a worksheet
To protect cells
To unprotect cells

SECURITY

To specify a password for opening a workbook
To specify a password for saving a workbook
To use the 'read-only recommended' option
To use the 'Always create backup' option
To protect a workbook
To un-protect a workbook
To remove a password from an Excel workbook

2. DATA HANDLING

SORTING

Sorting an internal Excel list
To sort the data within a list
To sort the list on a single field
Using the 'Then by' sort option
To use custom sorting options

QUERYING / FILTERING

What is 'AutoFilter'?
To use AutoFilter
Using drop down AutoFilters
To perform multiple queries
To specify a conditional filter criteria
Using the And / Or filters
To remove all AutoFilters
To use Top 10 AutoFilter
To filter unique records
To use the Advanced Filter

LINKING

To link individual cells within a worksheet
To link charts to data within a worksheet
To link a cell range on one worksheet to another worksheet (in the same workbook)
To link data on one worksheet to a chart in another worksheet (within the same workbook)
To link charts between worksheets
To link data from one workbook to another
To link a chart from one workbook to another
Copying a chart from one workbook to another
What is embedding?
What is linking?
To copy data to the Clipboard
To embed data (or a chart) from Excel into a Word document
To Paste Link selected Excel data / chart to a Word document
To consolidate data over several worksheets

TEMPLATES

What are templates?
To use a template
To create a template
To edit a template

CHARTS & GRAPHS

To use the Chart Wizard to create a chart
To quickly create a chart using default settings
To change the angle of pie chart slices
To format the chart axis numbers
To format the chart axis scales
To format the chart axis text
To move a chart item
To resize a chart item
To re-position a chart title
To re-position a chart legend
To re-position a chart data label
To explode all the segments within a pie chart
To delete a data series in a chart
To add a data series to a chart
To modify the chart type for a defined data series
To widen the gap between columns / bars within a 2-D chart
To insert an image into a 2D chart (as a background)
To insert an image into a 2D chart (to format a column or bar of data)

3. FUNCTIONS

USING FUNCTIONS

What is the 'Insert Function' utility?
Ways to access the Function Wizard
To use the Insert Function to enter a function
Using the mouse to enter information into the Function Wizard
To use the TODAY function
To use the DAY function
To use the MONTH function
To use the YEAR function
To use the SUMIF function
Another example of using SUMIF
To SUMPOSITIVE function - LOTUS
To use the ROUND function
Rounding up or rounding down!
To use the COUNT function
To use the COUNTA function
To use the COUNTIF function
To use the PROPER function
To use the UPPER function
To use the LOWER function
To use the CONCATENATE function
What is the FV function?
FV Syntax
Things to be careful of when using FV
An example of using the FV function
The NPV (Net Present Value) function
NPV Syntax
An example using the NPV function
The PMT function
PMT Syntax
Example of using the PMT function
The PV (Present Value) function
PV syntax
Example using the PV function
To use the RATE function
Example using the RATE function
To use the HLOOKUP function
Example using the HLOOKUP function
To use the VLOOKUP function
Example using the VLOOKUP function
To use the IF function
Functions and named ranges
Action_if_true
Action_if_false
The IF( ) Function - an example
To enter an IF ( ) function
To use the AND function
To use the OR function
The ISERROR function
Example of using ISERROR.
The DSUM function
An example of using DSUM
The DMIN function
An example of using DMIN
The DMAX function
An example using DMAX
The DCOUNT function
Example of using the DCOUNT function
What are nested functions?

4. ANALYSIS

PIVOT TABLES / DYNAMIC CROSSTAB

What are Pivot Tables?
To create a PivotTable
To drop data into the Pivot Table
To modify data and refresh the Pivot Table
To group data within a Pivot table

SCENARIOS / VERSIONS

What is the 'Scenario Manager'?
Scenario Manager - an example
To show a scenario
To view an alternative scenario
To create a scenario summary

AUDITING

To use tracers to audit a worksheet
To display the Formula Auditing toolbar
The Formula Auditing toolbar icons
To trace errors in a cell
An example of using 'trace precedents'
To trace the dependants of a cell
An example of using 'trace the dependants'
To display all formulas within a worksheet
What are cell comments?
To add a comment
To display a comment
To remove a comment
To edit a comment

5. SPECIAL TOOLS

MACROS

What are Macros?
To record a Macro
To assign a macro to a shortcut key
To run a macro using the Tools Macro command
To run a macro assigned to a shortcut key
Macros can be dangerous!
To disable macros when opening a workbook
To create a custom toolbar
To assign a macro to custom button on a toolbar

ECDL | European Computer Driving Licence
ICDL | International Computer Driving Licence