Special Note

The Spreadsheet Skills courses have run very successfully over the last number of years.  To facilitate the continuous development of the programme and in response to Members’ requirements, Spreadsheet Skills is now offered at two levels – Intermediate and Advanced.  This information outlines the Advanced Course.  Should you wish to compare against details of the Intermediate course, please click here.  If you are unsure or need assistance in deciding which course level would be best to meet your needs, please contact Orla Sweeney at the Institute on (01) 631 4653.


Course Outline and Benefits                                                                           

Course Duration:  1 day.

Course description:  This intensive course has been specifically developed for those in financial/accounting positions. The programme will help users manage and share information, access and analyse data, streamlining the way they work.

Who should attend and what are the pre-requisites:  This course is designed for people who use spreadsheets in their day-to-day work.  Participants should have a thorough practical knowledge of working with Spreadsheets.  Completion of an Intermediate Excel, Excel Level 2 or equivalent level of knowledge is recommended.

Delivery Method:  Instructor-led, group-paced, classroom-delivery learning model with structured hands-on activities.


Performance-Based Objectives

Upon successful completion of this course, delegates will be able to:

• Apply conditional formatting; add data validation criteria; customize menus and toolbars; and create, edit, and run macros.

• Create a workspace, consolidate data, view the consolidated data, and link cells.

• Chart non-adjacent data, modify embedded charts and chart items, and add a trend line to their chart.

• Use advanced features of Pivot Tables to include reports, auto formats, automatic sub functions and using Pivot table report on external data sources.

• Use Internet Publishing Basics.

• Use HTML links.

Course Content

Lesson 1:  Advanced Formulae

• DATE Functions – Sort Data based on DATE Criteria.

• Statistical Functions – Standard Deviation, Correlation, Coefficient & Confidence Intervals.

• Conditional Statements, Nested “If” statements.

• Custom Functions.

• Create Vertical & Horizontal Lookup Tables.

• Use Lookup Tables to Create “What If” Models.

• Nest Lookup Tables within Conditional Functions.

Lesson 2:  Link & Embed

• Insert Clip Art, Word Documents, Video, Photographs & Other Visuals into Microsoft Excel Spreadsheets.

• Insert Maps, Organisation Charts & Files as Icons.

• Using Paste Special.

• Import & Export files.

• Copying Data between Worksheets.

• Creating, Applying and Deleting Styles.

Lesson 3:  Database Functions

• Set up a Database in Microsoft Excel to Maximise its Use.

• Use the Data Form to Input, Edit, Sort & Query Data.

• Performing Single and Multi-level Sorts.

• Using AutoFilter and Advanced Filter.

• Control Data Entry with Validation Rules / Alert Boxes.

• Import & Export Data to Database Applications.

• Group Data, Edit Criteria & Change Group Functions.

• Consolidate Data from Multiple Sources.

Lesson 4:  Analyse Data

• Analyse Data with PivotTables.

• Creating, Modifying and Formatting a PivotTable.

• Add Automatic Sub-functions to PivotTables include Averages, Count & Standard Deviations.

• Use Pivot Report on External Data Sources.

• Creating Interactive PivotTables for the Web.

• PivotTable Reports.

• PivotTable AutoFormats.

Lesson 5:  Model

• Design a Model to Help Forecast Future Events.

• Use Scenario Manager to Manage & Model Scenarios.

• Summarise Scenario Results using Reporter.

• Use Solver to Solve Complicated Equations.

• Model Data in Solver to Avoid Solver Constraints.

• Use Solver Options to More Clearly Define Trends in Data.

Lesson 6:  Macros

• Understanding Macros; Recording a Basic Macro.

• Running a Macro; Editing a Macro.

• Assigning Macros to Buttons, Shortcut Keys.

• Macro Virus Protection.

• Detect and Repair.

Lesson 7:  Microsoft Excel and the Web

• Creating Web Queries.

• Creating Documents for the Web.

• Customise your Web Pages.

• Manage your Files and Links.