 |
|
There are many excellent online courses. This has been hugely beneficial in my day to day work.
|
|
- Louise Nolan
|
|
|
|
|
ECDL AM4 Advanced Spreadsheets with Excel 2002
Course Aims
This course is designed to help you to obtain the skills and knowledge required for ECDL AM4 "Spreadsheets Advanced". You will learn to use Excel 2002 to create an advanced spreadsheet, incorporating different functions, linked data, and custom formats.
You will learn to add PivotTables and apply complex chart formatting. You will also be able to use spreadsheet tools, including PivotTables, scenarios, What-If? tables, macros, and auditing and collaboration tools. |
Assumed Knowledge
You should be able to use Excel to create, edit, format, and print a spreadsheet. You should have mastered the objectives of ECDL Module 4 "Spreadsheets" before attempting this course. |
Course Audience
Students training to obtain an Advanced ECDL or those wanting to become expert in the use of advanced spreadsheet functions. |
|
Table of Contents
|
Excel 2002 - Viewing a Large Worksheet
|
Go To a specific cell or named range
|
|
Split a worksheet into different viewing panes
|
|
Freeze and unfreeze panes as worksheet titles
|
|
View a workbook in multiple windows
|
|
Hide and unhide rows and columns
|
|
Hide and unhide worksheets and workbooks
|
|
|
|
Excel 2002 - Borders and Shading
|
Apply cell borders and shading
|
|
|
|
Excel 2002 - Using Named Ranges
|
Apply a name to a range of cells
|
|
Apply a name to a constant value
|
|
Modify and delete range names
|
|
Print a list of range names
|
|
Use a range names and labels in formulas
|
|
|
|
Excel 2002 - Data Lists
|
Use a form to view, sort, and enter records
|
|
Perform single and multi-level sorts
|
|
Filter a list using AutoFilter
|
|
|
|
Excel 2002 - Filtering and Outlining Lists
|
Apply a filter to a data list
|
|
Extract data using advanced filters
|
|
Group and subtotal data using the outlining tools
|
|
|
|
Excel 2002 - Logical and Nested Functions
|
Use the IF, AND, OR, and NOT functions in formulas
|
|
|
|
Excel 2002 - Mathematical Functions
|
Use rounding functions (ROUND, ODD, EVEN, INT)
|
|
Use other arithmetical functions (PRODUCT, RAND, PI, SQRT)
|
|
|
|
Excel 2002 - Linking Worksheets and Workbooks
|
Create a formula to link worksheets and workbooks
|
|
Consolidate data using 3D references in common functions
|
|
Use Paste Special to link worksheets and workbooks
|
|
Use Paste Special to manipulate data
|
|
Manage and update linked workbooks
|
|
|
|
Excel 2002 - Financial Functions
|
Use financial functions (PMT, FV, PV, NPV, RATE, IRR, SLN)
|
|
|
|
Excel 2002 - Statistical and Database Functions
|
Use other statistical functions (MIN, MAX, MEDIAN, MODE, STDEV)
|
|
Use Dfunctions to perform calculations on a data list
|
|
|
|
Excel 2002 - String, Date, and Time Functions
|
Join strings (CONCATENATE, &)
|
|
Extract text from a string (LEFT, RIGHT, MID)
|
|
Change case (UPPER, LOWER, PROPER)
|
|
Find and replace text within a string (FIND, REPLACE, SUBSTITUTE, TRIM, CLEAN, LEN)
|
|
Convert between text and number values (TEXT, VALUE, FIXED, DOLLAR)
|
|
Insert specific characters from the Windows character set (CHAR, CODE)
|
|
Enter the date and time using functions (TODAY, NOW, DATE, TIME)
|
|
Return selected parts of a date or time value (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND)
|
|
Perform calculations using date/time values
|
|
Use other date functions (NETWORKDAYS, WEEKDAYS, WEEKNUM)
|
|
|
|
Excel 2002 - Lookup Functions
|
Use VLOOKUP and HLOOKUP to get values from multi-column tables
|
|
Sort values in rows or from left-to-right
|
|
|
Office 2002 - Formatting a Chart
|
Add, remove, move, resize, and modify chart elements (titles, labels, legend, axes, gridlines)
|
|
Format chart elements (borders, patterns, font, values)
|
|
Format chart axes (number format, text, line width)
|
|
Format data series (scale, shading, spacing, width)
|
|
|
|
Excel 2002 - Customising a Chart
|
Change the source data for a chart and modify data series
|
|
Explode segments of a pie chart
|
|
Insert an image into a chart
|
|
Add a text box to a chart
|
|
Store a custom chart as a chart template
|
|
|
|
Office 2002 - Inserting Spreadsheets and Charts
|
Insert a worksheet from Microsoft Excel
|
|
Insert a chart from Microsoft Excel
|
|
|
|
Excel 2002 - Importing Text Data
|
Import data from a text file
|
|
|
|
Excel 2002 - PivotTable and PivotChart Reports
|
Create a PivotTable Report
|
|
Modify PivotTable field layout
|
|
Modify field settings and grouping intervals
|
|
Use PivotTable AutoFormat
|
|
Create a PivotChart Report
|
|
|
|
Excel 2002 - Using Analysis Tools
|
Use one- and two-input data tables to resolve What-If? calculations
|
|
Solve a problem using Goal Seek
|
|
Use Solver to calculate complex problems
|
|
Create What-If analyses using the Scenario Manager
|
|
|
|
Excel 2002 - Adding Comments
|
Edit and delete a comment
|
|
|
|
Excel 2002 - Auditing a Worksheet
|
Display formulas in worksheet cells
|
|
Trace precedents and dependents
|
|
Use the Formula Error Checker
|
|
Use the Formula Evaluator to obtain results in part of a formula
|
|
Use the Watch Window to view results in cells
|
|
|
|
Excel 2002 - Protecting Data
|
Apply and remove worksheet protection
|
|
Lock and unlock cell ranges
|
|
Apply and remove file passwords to open or change a workbook
|
|
|
|
Excel 2002 - Custom Number and Conditional Formatting
|
Use formatting options with Paste Special
|
|
Create a custom number or date format
|
|
Use scientific and fractional number formats
|
|
Use conditional formatting
|
|
|
|
Excel 2002 - Templates and Add-Ins
|
Create a template file for frequently used worksheet layouts
|
|
Edit and apply a template file
|
|
|
|
Excel 2002 - Recording a Macro
|
Assign macros to menus, shortcut keys, and toolbars
|
|
Set up macro virus protection
|
|
|
|
|
|
