Spreadsheets ECDL ICDL Advanced
Outline
Outline
Microsoft Excel 2003 Edition for ECDL Advanced
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
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
• To link data on one worksheet to a chart in another worksheet
• 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 Word
• To Paste Link selected Excel data / chart to Word
• 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
• 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
• To use 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
• The NPV (Net Present Value) function
• NPV Syntax
• The PMT function
• PMT Syntax
• The PV (Present Value) function
• PV syntax
• To use the RATE function
• To use the HLOOKUP function
• To use 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
Check back soon there are more topics