Advanced Excel Functions

Advanced Excel FunctionsThis practical 2 – day course leads delegates through advanced Excel functions and specialised spreadsheet techniques, explaining the functions and their uses (this excludes Pivot Tables and Graphs which are included in the 3 day course).

Is this the correct course for your needs? Use this quick ‘Which Excel Course should I do‘ questionnaire to establish which course is correct for you.

In order to do this course we also recommend a skill level of >3.5 (we offer a free Skills Assessment to make sure you get the correct level of training).

This is a very hands-on, non-intimidating, course – delegates work through various exercises under the supervision, and with the help, of the facilitator to get to grips with the nuances of Excel’s advanced functions.

Course Outline

Day 1

  • Advanced Excel Techniques for use in modelling
  • How errors happen in models and how to avoid them
  • Financial Model and spreadsheet design principals
  • Essential Excel Knowledge
  • Importing data into a model
  • Data Cleanup using the Data Autofilter and the Advanced Filter
  • GOTO Special tool for working with cells
  • Using the text to column tool
  • Common pitfalls / problems at this stage
  • Working with imported text data
    • CLEAN, CONCATENATE, DOLLAR, EXACT, FIND/SEARCH, LEFT/RIGHT/MID, LEN, LOWER/UPPER/ PROPER, REPLACE, T, TEXT, TRIM, VALUE
DAY 2
  • Handling Dates in your models
    • EOMONTH, DATE, DATEVALUE, DAY, EDATE, MONTH, NETWORKING DAYS, WEEKDAY, WORKDAY, YEAR
  • Manipulating the data
    • (SUMIF, COUNTIF, SUMIFS, COUNTIFS, VLOOKUP)
  • Ranking Results
  • Error checks and spreadsheet integrity
  • Analysing Results
    • Data Filter
    • Conditional Formatting
    • Cell Formatting
    • Cell and spreadsheet protection

Feedback & Who Should Attend

  • Thanks so much for the Advanced Excel course last week. It was interesting, engaging and informative. There were lots of tricks and short-cuts that will make my financial modelling easier. I’m already starting to clean up and reorganise some of my spreadsheets and looking at ways of reporting in a more interesting way. Won’t forget to email after the 3rd time doing the same thing… there’s sure to be a more elegant solution…- Old Mutual
  • It was most interesting and filled with very useful and relevant tricks and tools that one can start to use immediately. The course was well constructed with a logical flow to be able to reference information easily in the take home materials, with exercises covering many different scenarios. Dewet was a very knowledgeable and motivating instructor that kept the tone of the course light and fun, making the course most enjoyable and easy to absorb all this valuable information. Definitely a course I will recommend to all interested in advancing their Excel skills. – Ewaldt J- Hyatt Hotels

Typical Attendees

  • All persons using Excel spreadsheets. Professions that have attended include accountants, actuaries, auditors, business analysts, chartered accountants, civil engineers, construction, consulting, corporate finance, engineers, farming, finance, hr function, landlords, lawyers, quantity surveyors, research analysts, retail, scientists and engineers
  • All persons involved in the construction and use of spreadsheet models, including budgets, projections, evaluations, cash flows, projects, etc.Other areas that would benefit include budgeting, business, data analysis, debt, logistics, non profit, project management, property management, real estate, statistics, and trading
  • All delegates should have a score of above 3.5 on our Excel Skills Assessment
  • Delegates should be familiar with Microsoft Excel (though not necessarily expert in the use thereof, as any relevant Excel function pertinent to an exercise will be discussed prior to tackling the exercise).

Pre Requisite (s)

Excel knowledge required:

  1. Basic Excel skills
  • Opening & closing files
  • Moving around the spreadsheet
  • Inserting & deleting rows and columns
  • Inserting text & formulas
  • Deleting cells
  • Copying formulas
  • Printing
  1. Basic formulas (+-*/) e.g. =C5+C7+sum(C9:C12), and =D5*D6/12 3. Formatting
  2. Formatting