Skip to content
Microsoft Excel Advanced course background

Microsoft Excel training courses

Enliten IT have an extensive range of Microsoft Excel training courses to give you the skills to ‘Excel’ at work!  Whether you are new to Excel, are self-taught or you think you know a fair bit, we’ve got a course for you.

We have certified Microsoft Excel trainers and consultants throughout the UK, so why not take advantage of our, cost effective, tailored onsite training options (we come to you!).  Alternatively, you can come to one of our training centers.

We train on ALL versions of Microsoft Excel and to All levels – Excel introduction, Excel intermediate, Excel advanced and Excel Super User.

Microsoft Excel Advanced course logo

Microsoft Excel Advanced course

1 day

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.

Download PDF Book now

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.

“"It was good session, very well organised and presented."”

Dev, G’s Fresh, Excel 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.