ECDL Module 4 - Spreadsheets

ECDL 5 / BCS Level 3 Courseware

Course Outline

CELL FORMATTING

• Applying AutoFormatting to a cell range.
• Applying conditional formatting based on cell content.
• Creating custom number formats.
• Applying custom number formats.

WORKSHEET FORMATTING

• Copying worksheets within a workbook.
• Moving worksheets within a workbook.
• Splitting a window.
• Moving split bars.
• Removing split bars.
• Hiding rows
• Hiding columns
• Hiding worksheets.
• Displaying hidden rows
• Displaying hidden columns
• Displaying hidden worksheets

USING FUNCTIONS AND FORMULAS

• Using the date and time function 'today'.
• Using the date and time function 'now'.
• Using the date and time function 'day'.
• Using the date and time function 'month'.
• Using the date and time function 'year'.
• Using the mathematical function 'rounddown'
• Using the mathematical function 'roundup'.
• Using the mathematical function 'sumif'.
• Using the statistical function 'countif'.
• Using the statistical function 'countblank'.
• Using the statistical function 'rank'
• Using the text function 'left'
• Using the text function 'right'
• Using the text function 'mid'
• Using the text function 'trim'
• Using the text function 'concatenate'
• Using the financial function 'fv'
• Using the financial function 'pv'
• Using the financial function 'pmt'
• Using the lookup function 'vlookup'
• Using the lookup function 'hlookup'.
• Using the database function 'dsum'
• Using the database function 'dmin'
• Using the database function 'dmax'
• Using the database function 'dcount'
• Using the database function 'daverage'
• Creating a two-level nested function.
• Using a 3-D reference within a sum function
• Using mixed references within formulas

CREATING CHARTS

• Creating a combined column and line chart.
• Adding a secondary axis to a chart.
• Changing the chart type for a defined data series.
• Adding a data series to a chart.
• Deleting a data series within a chart.

FORMATTING CHARTS

• Re-positioning the chart title
• Re-positioning the chart legend
• Re-positioning chart data labels
• Changing the scale of value axis including minimum, maximum number to display and major interval
• Changing display units on value axis without changing data source including hundreds, thousands and millions
• Formatting chart columns to display an image
• Formatting chart bars to display an image
• Formatting the chart plot area to display an image
• Formatting the chart area to display an image

USING TABLES

• Creating a pivot table.
• Modifying a pivot table.
• Modifying the data source and refreshing the pivot table.
• Filtering and sorting data within a pivot table.
• Automatically and manually grouping data in a pivot table and renaming groups.
• Using one-input data tables.
• Using two-input data tables.

SORTING AND FILTERING DATA

• Sorting data by multiple columns at the same time.
• Creating a customized list and performing a custom sort.
• Automatically filtering a list in place.
• Applying advanced filter options to a list.
• Using automatic sub-totalling features.
• Expanding and collapsing outline detail levels.

SCENARIOS

• Creating named scenarios.
• Showing scenarios.
• Editing scenarios.
• Deleting scenarios.
• Creating a scenario summary report.

VALIDATING

• Setting and editing validation criteria for data entry within a cell range such as 'whole number'.
• Setting and editing validation criteria for data entry within a cell range such as 'decimal'
• Setting and editing validation criteria for data entry within a cell range such as 'list'
• Setting and editing validation criteria for data entry within a cell range such as 'date'.
• Setting and editing validation criteria for data entry within a cell range such as 'time'.
• Entering an input message and error alert.

AUDITING

• Tracing precedent cells.
• Tracing dependent cells.
• Identifying cells with missing dependents.
• Showing all formulas in a worksheet, rather than the resulting values.
• Inserting comments
• Editing comments
• Deleting comments
• Showing comments
• Hiding comments

NAMING CELLS

• Naming cell ranges and deleting names for cell ranges.
• Using named cell ranges within a function.

PASTE SPECIAL OPTIONS

• Using Paste Special options such as adding
• Using Paste Special options such as subtracting
• Using Paste Special options such as multiplying
• Using Paste Special options such as dividing
• Using Paste Special options such as values
• Using Paste Special options such as transpose

TEMPLATES

• Creating a spreadsheet based on an existing template.
• Modifying a template.

LINKING, EMBEDDING AND IMPORTING

• Inserting a hyperlink.
• Editing a hyperlink.
• Removing a hyperlink
• Linking data within a worksheet
• Linking data between worksheets
• Linking data between workbooks
• Linking data between applications
• Updating a link.
• Breaking a link.
• Importing delimited data from a text file.

AUTOMATION

• Recording a simple macro like changing the page setup.
• Recording a simple macro like applying a custom number format
• Recording a simple macro like applying AutoFormatting to a cell range.
• Recording a simple macro like inserting fields in worksheet header or footer.
• Running a macro.
• Assigning a macro to a custom button on a toolbar.

TRACKING AND REVIEWING

• Turning 'track changes' on or off..
• Tracking changes in a worksheet using a specified display view.
• Accepting or rejecting changes in a worksheet.
• Comparing worksheets.
• Merging worksheets.

SECURITY

• Adding 'open' password protection to a spreadsheet.
• Adding 'modify' password protection to a spreadsheet.
• Removing 'open' password protection from a spreadsheet.
• Removing 'modify' password protection from a spreadsheet.
• Protecting cells with a password.
• Protecting a worksheet with a password.
• Un-protecting password protecting cells within a worksheet.
• Un-protecting password protected worksheets within a workbook.
• Hiding formulas.
• Un-hiding formulas.