Advanced Excel Functions

This practical 2 – day course leads delegates through advanced Excel functions and specialised spreadsheet techniques, explaining the functions and their uses.

Learn about all the most important Excel functions and formulas. Includes VLOOKUP, SUMIFS, AVERAGEIFS, OFFSET and more.

Advanced Excel Functions

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

Function and Formula Basics

  • Function Wizard- why you should use it
  • Trace where a formula comes from and goes to (even across sheets)
  • Using Absolute and Relative Referencing ($ signs)
  • Shortcut to add $ signs to cell references
  • The basic “IF” function
  • How to (safely) build a nested IF formula
  • IF this AND this AND this is True

Formula Tips and Tricks

  • See the numbers behind a formula
  • Adding cells by moving sheets around
  • Enter formula into many cells (at the same time)
  • Change formula across many sheets at the same time
  • Transpose rows to columns but keep them linked
  • Custom Format a word into a number cell e.g. 100 tons
  • Custom Format numbers into thousands or millions
  • Update the calculations on only one sheet (for large spreadsheets)

Text Functions

  • Join text from many cells
  • Extract parts of the text from cells
  • Force Excel to see the value (instead of text)
  • Change text to lower, UPPER or Proper case
  • Remove unnecessary spaces
  • Create a find/ replace formula
  • Find where a certain character is in a cell
  • Force excel to treat number as text

Key Lookup Functions

  • VLOOKUP explained in simple terms
  • Build your first VLOOKUP
  • Handling Error Messages- Remove the impact of #NA, #DIV/0! and more
  • Vlookup approximate match (True instead of False)
  • Vlookup partial text from single cell- fuzzy lookup
  • OFFSET
  • MATCH and INDEX as an alternate VLOOKUP?
  • Lookup across rows and columns (at the same time)
  • Lookup to the left instead of right (like VLOOKUP)
  • Get Excel to find the columns you want to bring back from a lookup
  • MATCH, INDEX, HLOOKUP and OFFSET Combined
  • Lookup on different sheets
  • INDIRECT

SUM, COUNT, AVERAGE IFS

  • Sum, count or average if certain cells match
  • Sum, count or average cells e.g. bigger than
  • Sum, count or average with many conditions
  • Sum, count or average with partial matches

Date functions and issues

  • How Excel Handles Times and Dates
  • Pull the day, month or year out of the date
  • Create a VALID Excel date with a day, month and year number
  • Calculate the proper end of month
  • Determine what day of the week a date is
  • When is the next workday e.g. 30 days from invoice date
  • How many working days between 2 dates
  • What week number is a date
  • Force Excel to format a date to your version
  • How many months/years between two dates

Avoiding Errors

  • What can go wrong with a formula when copying/ pasting
  • The risks on formula when inserting columns and rows
  • Formula risk with a insert or delete of a cell
  • Formula impact with hidden rows and columns
  • Errors that arise from linking spreadsheets together
  • Simplify your formula using Excel’s inbuilt functions

Protecting spreadsheet information and formulas

  • Hide formulas in Excel
  • Trick: Allow user to only see their information in Excel
  • Trick: Protect rows and columns without protecting sheet

Tips and Tricks- Advanced

  • Trick Excel into Changing where Absolute references ($) look
  • Change the $ signs on multiple cells with FIND/ REPLACE

Pivot Tables

  • Add a calculation INSIDE a Pivot Table
  • Get information out of a Pivot into a cell
  • Switching off the GETPIVOTDATA
  • Add up ITEMs within a Pivot Table Field

Conditional Formatting

  • Conditional Formatting- Linking to different cells
  • Control what is entered in an Excel cell- Data Validation
  • Control the inputs in a cell based on another cell

Charting In Excel

  • Remove Zeros from chart labels
  • Add linked commentary directly to the chart
  • Add commentary to the axis
  • Add commentary to labels and call outs
  • Add commentary to labels for older versions of Excel
  • Hiding a series with NA()

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