|
|
ECDL / ICDL AM4 Courseware Training Materials
Advanced Spreadsheets
ECDL | ICDL Courseware Training Manual
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
Other ECDL / ICDL Courseware Training materials
|
