Delegates must be confident in creating their own spreadsheets including formulas and functions requiring absolute and partial absolute cell referencing, range names and multiple worksheets/books. This a high level, fast-paced course and if you are not confident in these areas we would recommend that you consider one of our other Excel courses first.
Aims
By the end of the course you will be confident in creating Range Names, logical functions, date and text functions. You will also be confident in nesting these functions.
What you will learn
In particular you will be able to:
- Create and use Dynamic Range Names to simplify all your formulas and functions and ensure they update when new data is added.
- Use Text and Date functions to extract and create data.
- Cross reference information from specific rows and columns in a table using INDEX and MATCH.
- Make your Lookups and Indexes more powerful using other functions such as, INDIRECT, MATCH, AND, IF.
Requirements
Delegates should be confident in creating their own spreadsheets including formulae and functions requiring absolute and partial absolute cell referencing. In addition delegates should be confident in writing formulae and functions using basic range names and multiple work sheets/books.
Timetable
Session 1
Range names; Scope Range Names; Dynamic Range Names using the Offset function.
Session 2
Date Functions (DATEVALUE, DATEDIFF, DATEADD); Custom Date/Time Formats; Nesting Date Functions.
Session 3
Text Functions (CONCATENATE; LEFT, RIGHT, MID, LOWER, UPPER, PROPER, REPLACE, TRIM, SEARCH ); Nesting Text Functions.
Session 4
Nesting functions (VLOOKUP, HLOOKUP, INDEX, MATCH, AND, OR, NOT, INDIRECT, ISERROR, IF, ISNUMBER, ISDATE, ISEMPTY)
The ‘Small Print’
The course will run from 10am to 4:45pm (light cold buffet 1pm to 1:45pm). Please be careful to arrive by 9.45am for registration and refreshments. The course will start precisely on time and it may not be possible to go back over material missed by late arrivals. If you arrive over 30 minutes late you may be asked to leave and attend another day.
