Excel Functions and Formulas
Excel Functions and Formulas
This course is for anyone who needs to learn more about the functions and formulas in Excel and how to use them.
- Time Req – 20 hr
- Skill Score >4
- FAQ
- All Online Courses
- Live Course Options
- Ask a Question
Modules
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
