This Microsoft Excel Advanced course is predominantly mathematics based, concentrating on the use of complex formulae and logic within formulae. It also covers some less used but still useful advanced features.
What will you learn?
At the end of this Microsoft Excel Advanced course, delegates will be aware of the capabilities, and uses, of some of the more advanced functions within Excel.
Audience?
Users who need to use and explore more complex formulas that will make Excel ‘do the work’ for them.
Course Contents
Using Logic
- IF statements and nested IF’s
- IFS
- Conditions - AND/OR/NOT
- IFERROR
- SUMIF/s; COUNTIF/s; AVERAGEIF/s
Lookup Tables
- Range Names review
- VLOOKUP (range and exact match’s)
- XLOOKUP
- INDEX and XMATCH
Conditional Formatting
- Using logical parameters
- Using date ranges
- Using formulas
Using Mixed references
Find and Remove duplicates
Data Validation
- Dynamic and Data dependent lists – OFFSET, INDIRECT
String Functions
- Joining Text and Cells together (&)
- LEFT, MID, RIGHT, LEN, FIND, TRIM,
- SUBSTITUTE, VALUE, EXACT, DATEVALUE,
- TEXTBEFORE, TEXTAFTER
Useful Functions
- SUMPRODUCT, RANK
- Calculations using Dates and Times e.g. DATEDIF, TEXT, WEEKNUM
Text to Columns feature
Advanced Charting
- Adding non-adjacent data
- Mixing chart types
- Using dual Y-axis
- Adding Trendlines
- Bar of Pie; Pie of Pie
Optional Topics (time permitting)
- Importing Data
- Import data from text files (Import Wizard)
- Hyperlinks - to link a cell, sheet, document or web page
- Useful Add-ins
- Printing grouped worksheets
Course Materials
Enliten IT will provide each delegate with a workbook and other useful reference materials where applicable. These may be either paper based or in Adobe pdf format or a combination thereof.
“"Fantastic course! Thanks you Marko" - Laura
"Really enjoyable and informative course, would recommend to everyone!" - Matt
"Very entertaining and relevant to the job, Easy to follow!" - Cath
”
Laura, Abcam, Excel 365 Intermediate
Prerequisites
You should be able to follow spoken and written instructions clearly and be comfortable carrying out maths and logic calculations as the course has large elements of mathematical and technical content. To that end you must have attended the Excel Intermediate course (or have equivalent experience).
Additional Information
Enliten IT reserves the right to modify course content and materials as required in the interests of continuous course improvement, at any time, without notification.
The suggested course duration is a guideline. Course topics and duration may be modified by the instructor based upon the knowledge and skill level of the course participants.