Microsoft Excel - Advanced Formulas And Functions

Learn how naming cells and ranges in Excel improves formula readability across a workbook, with steps to define, scope, and use named ranges across sheets.

Learn how the if function in Excel evaluates a logical test to return pass or fail for scores above 60, using absolute and relative references and error checks.

Apply the and, or, and not functions inside if to test multiple criteria and determine passes for math, history, and geography scores.

Learn to use Vlookup for exact and approximate matches with ascending lookup tables to determine grades and bonuses, using absolute and relative references.

Generate random numbers in Excel using rand and randbetween, adjust the range with multipliers or integer rounding, and retrieve a random employee name using min, max, and a lookup.

Learn to manipulate strings in Excel with replace and substitute functions, choose start positions and lengths, and target specific occurrences to transform text such as 'Newcastle upon Tyne'.

Learn how Excel converts dates to serial numbers, stores time as fractions of a day, and uses date arithmetic to compute differences, while recognizing formatting versus underlying values.

Use Excel's weekday function to derive a numeric day, adjust the return type, and map numbers to actual day names using a named days lookup table.

Explore how the cell and info functions extract sheet and file information in Excel, including retrieving file names, paths, and directory details for dynamic references.

Explore the offset function to dynamically reference cells, moving across rows and columns, and combine it with sum to create adaptive ranges that update as rows are added.