Workshop Duration
1 day
Prerequisites
Delegates must have attended an Excel Intro/Inter course, or have an equivalent working knowledge of Excel.
Who will the course benefit?
Users who wish to gain a comprehensive, in-depth knowledge of this application.
Course Content
Overview
- Short overview of intermediate topics e.g. databases, if statements
Named Ranges
- Defining and moving to a named range
- Applying range names in formulae
- “Sparklines”
Lookup Functions
- Creating a Lookup table
- Using the Lookup functions: VLOOKUP, MATCH, INDEX, INDIRECT
- “Dropping down” versus Exact Matches
Advanced Functions
- Financial, Date and Time, Text
- SUMPRODUCT
- OFFSET
Data Validation
- To Create Lists
- Using INDIRECT with Validation
Workspace
- Saving open files to a workspace
File Linking
- Linking data between spreadsheets
- Refreshing, updating and redirecting links
Templates
- Creating and modifying templates
- Storing styles in a template and date/time formats
Custom Views
- Using the “Custom Views” to create different views of your spreadsheet
- Displaying a view of the Worksheet
- Using “Report Manager” to print a view
Protection
- Protecting a Sheet and Workbook
- Define the levels of access
Number Formats
- Creating customised, number, currency, and date/time formats
Consolidation
- Producing a Consolidation spreadsheet
- Consolidation by position and category
Macros
- Creating macros in Excel
- Saving the macro to the Global Macro Sheet
- Assigning macros to keystrokes, buttons and Toolbar icons
Customising
- Customising the QAT (Quick Access Toolbar)
- Customising the Ribbon
Goal Seek
- Using Solver and Goal Seek to find solutions to problems (Scenarios if required)
Skills gained
On successful completion of the course, delegates will be able to create and apply templates, understand Lookups and Data Tables, solve problems by using Goal Seek, record macros and assign to buttons and link objects between different applications.
If you would like to book onto a course or find out more information please fill out our contact form or call us on 01454 203 355.