ICDL - Perform Advanced Spreadsheet Functions (2 Days)
ICDL Advanced Spreadsheets is a high level certification programme which presents learners with the opportunity to bring their spreadsheet skills to an expert level. This allows them to use the full potential of the spreadsheet application to produce higher quality management information.Individuals with ICDL Advanced Spreadsheets certified skills can:
Produce higher quality information
Pinpoint key information quicker and more easily
Provide more sharply-defined analysis
Produce more sophisticated reports
Use advanced editing, data handling, functions and analysis features
Use macros within the spreadsheets application
Target Audience
Prospective learners should ideally have the ICDL certification and/or previous experience using computers and common software applications. Typically, these would be individuals who use a wide range of advanced functionalities within a spreadsheet application in a personal or professional context. Spreadsheet applications enable learners to manipulate and produce precise information and conduct crucial data analysis. The ICDL Advanced Spreadsheets certification has been specifically designed to provide learners with the skills to exploit the full potential of the spreadsheet application.
Course Content
Formatting
Considerations In Designing Spreadsheets
The purpose and structure of the spreadsheet
The intended audience or users
Other considerations
Formatting
Table AutoFormat
Conditional formatting
Apply data formats
Using multiple worksheets
Insert, move, remove spilt
Hide and show rows, columns, worksheet
- Functions And Formulas
Using cell references in formula
Function syntax and categories
Logical functions
IF, AND, OR
Nested functions
IF + AND, IF + OR
Mathematical functions
SUMIF, ROUND, ROUNDDOWN, ROUNDUP
Statistical functions
COUNTA, COUNTBLANK, CUNTIF, RANK/RANK.EQ
Date and Time functions
TODAY, NOW, DAY, MONTH, YEAR
Financial functions
PV, FV, PMT
Text functions
CONCATENATE, LEFT, MID, RIGHT, TRIM
Lookup and Reference functions
VLOOKUP, HLOOKUP
Database functions
DSUM, DAVERAGE, DCOUNT, DMA, DMIN
Creating Three Dimensional worksheets
Past Special options
Use paste special options
- Charts
Creating Charts
Create a combined column and line chart.
Add a secondary axis to a chart.
Change the chart type for a defined data series.
Add, delete a data series in a chart.
Formatting Charts
Re-position chart title, legend, data labels.
Change scale of value axis.
Change display units on value axis without changing data source.
Format columns, bars, plot area, chart area to display an image.
- Analysis
Managing data in a spreadsheet
PivotTable
Create, modify a pivot table
Modify the data source and refresh the pivot table
Filter, sort data in a pivot table
Automatically, manually group data in a pivot table and rename groups
Data Tables
Use one-input, two-input data tables/multiple operations tables
Scenario Manager
Create named scenarios
Show, edit, delete scenarios
Create a scenario summary report
Sorting a database
Sort data by multiple columns at the same time
Create a customized list and perform a custom sort
Using Auto and Advanced Filters
Automatically filter a list in place
Apply advanced filter options to a list
Subtotals and Outlining
Use automatic sub-totalling features
Expand, collapse outline detail levels
Validating And Auditing
Data Validation
Set, edit validation criteria for data entry in a cell range.
Enter input message and error alert.
Formula Auditing
Trace precedent, dependent cells. Identify cells with missing dependents.
Show all formulas in a worksheet, rather than the resulting values.
- Enhancing Productivity
Range Names
Name cell ranges, delete names for cell ranges.
Use named cell ranges in a function.
Using Templates
Create a spreadsheet based on an existing template.
Modify a template.
Linking Data
Link data within a spreadsheet, between spreadsheets, between applications.
Update, break a link.
Inserting Hyperlinks And Bookmarks
Insert, edit, remove a hyperlink
Import Data To Excel
Import delimited data from a text file
Macro
Record a simple macro.
Run a macro.
Assign a macro to a custom button on a toolbar.
- Collaborative Editing
Working With Comments
Insert, edit, delete, show, hide comments
File protection
Add, remove passowrd protection for a spreadsheet
Protect, unprotect cells, worksheet with a password.
Hide, unhide formulas.
Accessing And Sharing Data
Turn on, off track changes. Track changes in a worksheet using a specified display view.
Accept, reject changes in a worksheet.
Compare and merge spreadsheets.
Funding
Additional Info
2 Days