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.
Users who need to use and explore more complex formulas that will make Excel ‘do the work’ for them.
- IF statements and nested IF’s
- Conditions - AND/OR/NOT
- SUMIF/s; COUNTIF/s; AVERAGEIF/s
- Range Names review
- VLOOKUP (range and exact match’s)
- INDEX and XMATCH
- Using logical parameters
- Using date ranges
- Using formulas
Using Mixed references
Find and Remove duplicates
- Dynamic and Data dependent lists – OFFSET, INDIRECT
- Joining Text and Cells together (&)
- LEFT, MID, RIGHT, LEN, FIND, TRIM,
- SUBSTITUTE, VALUE, EXACT, DATEVALUE,
- TEXTBEFORE, TEXTAFTER
- SUMPRODUCT, RANK
- Calculations using Dates and Times e.g. DATEDIF, TEXT, WEEKNUM
Text to Columns feature
- 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
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.
“"Fab, brilliant, really is going to save some time" - Nicky
"Fantastic" - Paige
"Really informative and engaging" - Kayleigh
”Nicky, LWC Drinks, Excel Introduction
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).
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.