Jemco can deliver an Advanced Microsoft Excel Training Course at the client’s premises if required, whether Glasgow, Edinburgh or throughout Scotland and the UK.
Duration: 1 Day
Pre-requisites: Delegates should have attended an Excel Intermediate and been using Excel for a reasonable length of time. Some typing skill would be an advantage.
Aimed at: Advanced users of Excel who require to consolidate existing knowledge and extend their knowledge to the more useful high end functions in Excel.
Objectives: On successful completion of the course delegates will be able to access advanced Excel features such as lookups and data analysis.
Advanced Microsoft Excel Training Course Content:
Lesson 1: Pivot Tables
- Creating a pivot table
- Custom calculations for pivot table and pivot chart data fields
Lesson 2: Auditing
- Finding cells that provide data to a formula
- Finding cells that contain a formula related to the cell
- Auditing a whole worksheet
- Tracing values that create errors in a worksheet
- Tracing formulae that create errors in a worksheet
- The ISERROR function
Lesson 3: Validation
- Specify the valid entries for cells
- Creating a list of valid entries
- Specifying valid entries
- Prompt for correct entry
- Displaying a warning for an incorrect entry
- Types of data validation messages
- Check for incorrect entries
- Copy data restrictions and messages to other cells
- Removing data restrictions and messages from a cell
- Change validation definitions for a cell
Lesson 4: The Vlookup Function
- The Vlookup syntax
- The Hlookup function
Lesson 5: Sumif
Lesson 6: Countif
LESSON 7: CountA
Lesson 8: Text Functions
Lesson 9: Mathematical Functions
Lesson 10: Goal Seek
- Using goal seek
- Goal seek options
Lesson 11: Solver
- The solver parameters dialogue box
Lesson 12: Scenarios
- Defining different scenarios within solver
- Using the scenario manager
- Creating scenarios using the scenario manager
Lesson 13: Simple Macros
- Recording a macro
- Recording a macro
- Assigning a macro to a button
- Creating a new macro for multiple copies.
Lesson 14: nested if
- If using and, or, not
- Starting with or
- If functions using “not”
Excel for Windows Workshops
The above is an outline intended as a guide only; topics may not be covered in this order. Actual course content will be tailored to suit client’s needs.
Contact John Cowie on 0791 787 4739 to book your Advanced Microsoft Excel Training Course in Glasgow, Edinburgh or Scotland – or use our enquiry form to send us a message.