PS Training Ltd

Printer-friendly document

Course Booking and enquiries: 01352 810443
enquiries@pstraining.co.uk

Prerequisites:
Attendees should have attended, or have knowledge equivalent to the Introductory, Intermediate, Advanced & Expert Level courses.

Duration:
This course can be delivered as a full day course or as individual half day modules

Module 9 - Automating the Workbook and using Advanced Formulas

• Using IS functions - Useful for preventing error messages in complex spreadsheets
• Rounding
• Advanced Data Validation- Identifying Incorrect entries by using Formulas for Data Validation Rules
• Creating Custom Formats
• Date Functions - Creating formulas to display the number of days elapsed between two dates without weekends, holidays etc.
• Time Calculations - Adding up times
• Recording Macros and attaching to Buttons
• Creating Custom Views to allow specific areas to be printed quickly
• Building Reports using the Report Manager to group together Custom Views
• Using Match Function - To find whether information in two cells is exactly the same
• Outlining - Useful for summarising spreadsheets and simply hiding detail

Module 10 - Advanced Filtering & Use of Analysis Tools

• Use the consolidation feature to bring information together
• Advanced Database Filtering - Inserting Subtotal Calculations with Filters display filtered information on a separate sheet
• Entering Database Statistics - Calculations to summarise information with DSum, Daverage etc where is returns data dependent upon the criteria specified
• Using Goal Seek to automatically arrive at a required result by changing the variables used within a formula
• Use of Solver (similar to Goal Seek with the additional option of setting specific criteria)
• Creating Solver reports
• Using the What If feature to build cross-reference tables
• Overview of the Scenario Manager
• Creating Multiple Scenarios within a worksheet
• Displaying various Scenarios
• Editing and Deleting Scenarios