Excel Advanced Functions and Formulas

Learn to create formulas in Excel, using advanced functions and formula techniques. Create and use nested formulas and functions for different datasets.

Full Course Description:

Excel is one of the most powerful applications ever created. Learn to harness the power that Excel offers by using more advanced formula techniques. In this 2 day boot camp, we explore not only many different functions and formula creation, we also explain the how, the why, and the when of using various formulas across different data layouts. In this course we'll cover over 50 Excel functions over two days, and once you've worked with these functions you'll be able to make Excel do some pretty amazing things.

Prerequisites: To ensure your success, each student should know how to create a formula, and understand how to follow the syntax of functions. Additionally, the student should know the difference between relative, mixed, and absolute reference, as well as understand which reference to use in the formula. Feel free to contact us to discuss if this Boot Camp is right for you.

Advanced Functions and Formulas (2 Days) Course Objectives: • You will learn advanced lookup techniques • Use functions to manipulate and clean up text • Learn date and time functions • Cumulative and multi-condition sum or average of values • Fix and troubleshoot formulas with error returns using functions • Learn how to use statistical, rounding, and related functions Agenda   Day 1: • Methods for the use of the Lookup techniques and flexible Lookup functions   (VLOOKUP, HLOOKUP, LOOKUP, INDIRECT, OFFSET, INDEX, and MATCH) • Manipulate and clean up data with text functions   (VALUE, TRIM, PROPER, LOWER, UPPER, CONCATENATE, REPLACE, SUBSTITUTE, LEFT, RIGHT, MID, and more) • Nest different logical functions to compare data   (AND, OR, IF, ISTEXT, ISNUMBER, ISDATE, ISNULL, and more) • Advanced techniques and functions to sum or count data   (SUMIFS, COUNTIFS, AVERAGEIFS, COUNTBLANK, DSUM, DAVERAGE, and DCOUNT)   Day 2: • Use and create nested date and time functions   (TODAY, NOW, DATE, DATEDIF, NETWORKDAYS, WORKDAY, EOMONTH, EDATE) • Perform calculations using the frequently used financial functions   (PMT, FV, RATE, ROUND, ROUNDUP, ROUNDDOWN, CEILING, FLOOR, INT, TRUNC, EVEN, ODD, RAND, and FORECAST) • Error handling functions   (ISERR, ISERROR, IFERROR, IFNA, ISNA, EXACT) • Work with single cell and multi-cell array functions   (TRANSPOSE, FREQUENCY, and many more nested functions)   *The examples listed above are only some of the functions taught during this 2-day Boot Camp.*

Contact us for more information or to enroll in this boot camp

Return to top of page.

Microsoft Excel 2013 Training Class Outlines:

Excel 2013 Level 1: Fundamentals of Excel
Excel 2013 Level 2: Intermediate Functions and Features
Excel 2013 Level 3: Advanced Tools and Collaboration Features
Excel 2013 Level 4: Advanced Formulas and Functions
Excel 2013 Level 5: Introduction To VBA Macros
Excel 2013 Level 6: Advanced VBA Macros