University of Bath

Excel: advanced

Learn advanced Excel techniques including the IF, IFERROR, HLOOKUP and VLOOKUP functions.

6 Jun 201810.00am
6 Jun 201812.00pm

About the workshop

This course will show you how to use conditional formulae and formatting that will automatically decide what information or formatting to put into cells, based on the rules applied to them. You will also learn how to use HLOOKUP and VLOOKUP to return information displayed in other lists within a formula.

The course assumes you already have a good working knowledge of Excel including basic Functions & Formulas.

Learning outcomes

After completing this course, you will be able to:

  • create an IF function to display text or to calculate values
  • create a nested IF function
  • use the IFERROR function
  • use an AND, OR and NOT statements
  • use the COUNTIF and SUMIF functions
  • create a HLOOKUP function (using a horizontal table)
  • create a VLOOKUP function (using a vertical table)
  • edit LOOKUP functions
  • use Lookups to display exact matches
  • use Lookups to display non-exact matches
  • understand Reference Functions