Microsoft Excel Advanced
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 | Working with Excel Tables Structured References Create PivotTables View Source Detail Information Summary Functions PivotTable Default Summary Function Adding a Zero Value to Empty Cells Grouping Data Removing and Redisplaying Subtotals Removing and Redisplaying Subtotals Report Layouts Formatting PivotTables Updating and Refreshing Data Create PivotCharts Using Filter Slicers Using Timeline Filters |
| Module 2 | The VLOOKUP Function Understanding the VLOOKUP FALSE Understanding the VLOOKUP TRUE The HLOOKUP Function Logical Functions The IF Function Nesting IF Functions Using the VLOOKUP with an IF Function Financial Functions The PMT Function The FV Function |
| Module 3 | What-If Analysis Tools Creating a One or Two Variable Data Table Using the Goal Seek Installing the Solver Add-In Using Solver Using the Scenario Manager to Create Multiple Analysis of the same set of Data Creating Names from Selection |
| Module 4 | Understanding Macros Working with VBA Code Editing VBA Code Assigning Macros to the Quick Access Toolbar Assigning Macros to Shapes Saving an Excel Workbook that contains Macros Macro Security Settings |
| Module 5 | Import and Export Data Microsoft Query Starting the Microsoft Query from Excel Using the Query Wizard Working with Columns Sorting and Filtering Records Finishing the Query Updating Query Results Importing and Exporting Text Data Save all or part of a Worksheet as a Static Web Page Publishing a Worksheet to the Web Auditing Workbooks and Worksheets using Excel’s Auditing Tools Tracing the flow of Data and Formulas Using the Error Checking Using the Watch Window to watch Formula Results Sharing Workbooks |
| Module 6 | Creating & Saving Templates Using Templates Editing Templates Conditional Formatting Customize Excel Creating & Using Styles |