Excel: functions and formulas

Objectives

To use advanced formulas and functions to develop spreadsheets tailored to your needs.

Targeted audience

Professionals and technicians

Content

Data Consolidation and Organization

• Using relative and absolute addressing
• Evaluating a formula piece by piece
• Working with multiple sheets
• Calculating with data from multiple spreadsheets
• Using names
• 3D references

Time Functions

• Explanation of how Excel handles dates and their use in formulas
• Getting the date with TODAY and NOW functions
• Extracting parts with DAY, MONTH, YEAR, HOUR, MINUTE, SECOND
• Calculating age in years with YEARFRAC
• Creating a date from parts with DATE or from a string with DATEVALUE
• Manipulating with EDATE and EOMONTH
• Calculating the number of working days with NETWORKDAYS

Text Manipulation Functions

• Concatenation with the & operator or the CONCATENATE function
• Extracting substrings with LEFT, RIGHT, and MID
• Searching a string with FIND and SEARCH
• Modifying a string with REPLACE and SUBSTITUTE
• Cleaning up unwanted spaces with TRIM and CLEAN
• Formatting a date and a number with the TEXT function
• Utility functions LEN, REPT, PROPER, LOWER, and UPPER
• Conversions with VALUE and T

Mathematical Functions

• Handling rounding with ROUND, ROUNDUP, and ROUNDDOWN
• Using the modulo, MOD, for periodic calculations
• Calculating with conditions with SUMIF, SUMIFS, and COUNTIF
• Creating subtotals with SUBTOTAL and AGGREGATE
• Sum of products with SUMPRODUCT
• Finding the minimum or maximum with MIN, MINA, MAX, and MAXA

Logical Functions

• Condition testing with IF, AND, OR, NOT
• Using error functions ISNA, ISERROR, ISBLANK
• Using the IFERROR function to test and handle an error
• Working method for nesting conditions

Information Functions

• The CELL function and its arguments
• The N function

Array Formulas and Dynamic Array Functions

• Multi-cell array formula
• Single-cell array formula
• Dynamic array functions FILTER, SORT, SORTBY, and UNIQUE

Reference and Lookup Functions in Arrays

• Finding an exact or approximate value with VLOOKUP and HLOOKUP
• Finding the index corresponding to a value in a series with MATCH
• Extracting a value from an array with INDEX
• Joint use of INDEX and MATCH
• Creating an address with ADDRESS and dynamic addressing with INDIRECT