Course Overview
Useful Excel Formulas are essential for calculating values, analysing data, automating tasks, and improving productivity when working with Excel. A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already available in Excel.
Every function has the same structure. It’s not easy to remember which function and which arguments to use for each task. Fortunately, the Insert Function feature in Excel helps you with this.
This Useful Excel Formulas course is designed to help participants build a stronger understanding of advanced formulas, functions, data analysis techniques, and formula auditing in Excel. Participants will learn practical techniques for creating complex formulas, working with multiple criteria, retrieving information, analysing data, and identifying and correcting formula errors.
Benefits
By the end of this Useful Excel Formulas course, participants will:
- At the end of this course you will have a greater understanding of building complex formulas using nested functions (such as IF statements).
- You will find quicker ways to customise formulas by taking advantage of the built-in functions available in Excel. Save time by learning more sophisticated search & auditing techniques when working with data-heavy spreadsheets.
Who Should Attend
Our Useful Excel Formulas course is suitable for those with an advanced working knowledge of Excel who want a greater understanding of more sophisticated formulas & functions within Excel.
This course is particularly suitable for professionals who regularly work with complex spreadsheets, data analysis, calculations, reporting, and Excel-based business tasks.
You may also wish to consider one of our finance for non financial managers courses.
Course Content
Day 1: 9am – 5pm
1.0 Building complex formulas with Useful Excel Formulas
- Using nested IF statements
- Creating compound logical tests using AND, OR, NOT functions with IF statements
- Nesting LOOKUP functions
- Source table structure information using CHOOSE function
- Using MATCH function to locate data
- The INDEX function for retrieving information by location
- Using a nested formula containing INDEX, MATCH and MATCH (two-way lookup)
2.0 Advanced functions in Excel formulas
- Using COUNTIFS, SUMIFS & AVERAGEIFS for tabulating data based on single/multiple criteria
- Statistical functions; MEDIAN, MODE, RANK, LARGE, SMALL, ROUND, MOD
- AGGREGATE function to sum data in ranges with errors
- Use a variety of Financial functions such PMT, FV, IRR
Day 2: 9am – 5pm
3.0 Data & Text Functions
- Smarter ways to calculate date & time; TODAY
- NETWORKDAYS, WORKDAY, DATEDIF
- Use TYPE to identify data type of existing cell contents
- Text functions; UPPER, PROPER, FIND, MID, SEARCH, LEFT, RIGHT, LEN
- TRIM excess space in cells
4.0 Introduction to Array formulas
- Using embedded Excel Array formulas
- Create an Array formula
