# Excel Functions and Formulas

This course is for anyone who needs to learn about the functions and formulas in Excel. It follows on from our Excel Tools course.

## Modules

## Downloads and Course introduction

## Function and Formula Basics

### Lessons

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

## Formula Tips and Tricks

### Lessons

- See the numbers behind a formula
- Adding cells by moving sheets around
- Aggregation Exercise 1
- 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

### Lessons

- 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
- Text Examples-Intermediate
- Create a find/ replace formula
- Find where a certain character is in a cell
- Force excel to treat number as text
- Text Examples- Advanced

## Key Lookup Functions

### Lessons

- VLOOKUP explained in simple terms
- Build your first VLOOKUP
- VLOOKUP Examples
- Handling Error Messages- Remove the impact of #NA, #DIV/0! and more
- Vlookup approximate match (True instead of False)
- VLOOKUP Examples- categorise, bands
- Vlookup partial text from single cell- fuzzy lookup
- VLOOKUP Examples- fuzzy logic
- Automatic Sorting in Excel Exercise
- OFFSET
- OFFSET- Simple Example
- OFFSET- Time Lag Example
- OFFSET- Allow Insert Rows Example
- OFFSET- Monthly Management Reporting Example
- OFFSET- Other Examples
- 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 Example to summarise divisions across sheets

## SUM, COUNT, AVERAGE IFS

### Lessons

- Sum, count or average if certain cells match
- SUMIF et al Examples
- Sum, count or average cells e.g. bigger than
- SUMIFS et al Examples- Bigger or less than
- Sum, count or average with many conditions
- SUMIFS et al Examples- multiple conditions
- Sum, count or average with partial matches
- SUMIFS et al Examples- partial matches

## Date functions and issues

### Lessons

- 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
- Date Examples- Intermediate
- How many months/years between two dates
- Date Examples- Advanced

## Avoiding Errors

### Lessons

- 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