COMPUTER SKILLS
Computer skills are a valuable addition to any employee’s personal portfolio and can greatly increase your desirability to employers.
Overview
This one-day course is designed to help a delegate develop spreadsheets using more advanced functions and formulas.
Objectives
- Working with Tables
- Creating PivotTables
- Grouping dates in a PivotTable
- Using the VLOOKUP, HLOOKUP and IF Functions
- Using the PMT, FV functions and using What-if-Analysis
- Recording Macros
- Importing and Export data
- Templates
- Sharing Workbooks
- Conditional Formatting
Course Prerequisite
Language of Delivery
English
Delivery Methods
Course is facilitated by a competent & experienced subject matter trainer, who utilises the following techniques to ensure that the session is practical and experiential:Â Questions; Discussion; Exercises & Assessments.
Who should attend?
Any delegate wanting to develop their skills in using Microsoft Excel to design spreadsheet solutions and use tools for creating and formatting more complex spreadsheets, that solve more difficult problems.
Duration
1 Day
Includes:
- Comprehensive Manual
- Lunch & Refreshments (not applicable to on-site training)
- Electronic Certificate (on successful completion of the course)
- Electronic Trainer Feedback Report
- Electronic Delegate Feedback Questionnaire
Course outline
| Module 1 | Pivot Tables Convert Data into Excel Tables Insert a Table Introduction to PivotTables View Source Detail Information Summary Functions PivotTable Default Summary Function Group Data Remove Subtotals Remove Grand Totals Report Layouts Format a PivotTable Update & Refresh Data Present Data with Pivot Charts Filter using Slicers Filter using Timelines |
| Module 2 | The VLOOKUP Function The VLOOKUP FALSE The VLOOKUP TRUE The HLOOKUP Function Logical Functions IF Function Nesting IF Functions Nesting VLOOKUP with an IF Function Financial Functions The PMT Function The FV Function |
| Module 3 | What-If Analysis Tools Data Tables One-Variable Data Tables Two-Variable Data Tables The Goal Seek Feature Scenario Manager Solver Install the Solver Add-In Understand Solver Forecasts Use the Forecast Sheet Forecast Options |
| Module 4 | Macros Where to Create Macros Unhide & Hide the Personal Macro Workbook Assign a Macro to the Quick Access Toolbar Attach Macros to Shapes Save an Excel Workbook with Macros |
| Module 5 | Import & Export Data Import Access Data Import Web Data Import Text Data Web Pages Publish A Worksheet to The Web Audit Workbooks/Worksheets using Excel’s Auditing Tools Trace the Flow of Data & Formulas Use the Error Checking Tracing Errors The Watch Window Add & Remove Cells to/from The Watch Window Share Workbooks Review Changes |
| Module 6 | Templates Create & Save Workbook Templates Open a Copy of a Template Conditional Formatting (Highlight Cells Rules, Top Bottom Rules, Data Bars, Color Scales, Icon Sets) |