Microsoft Excel - Advanced Formulas And Functions

Learn to build and copy sum and count formulas in Excel, using ranges, proper syntax, and fill options to preserve formatting across a multi-sheet sales data workbook.

Explore how to access and use Excel's PMT function through the insert function dialog, understand inputs like rate, nper, pv, and optional FV and type, and calculate monthly loan payments.

Explore how to reference entire rows and columns in Excel formulas, count cells with data using count and counta, and apply functions to both consecutive and non-consecutive ranges.

Learn to troubleshoot Excel formulas using background error checking, error indicators, evaluate formulas, and use the watch window to track changes across sheets.

Learn how to convert formulas to static values in a snapshot by using paste values and paste special, preserving number formatting and ensuring totals no longer update.

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 to perform 3d referencing by grouping worksheets and summing or averaging the same cell across January to December using the sheet range and exclamation syntax.

Learn how to use full absolute referencing in Excel to fix specific cells when copying formulas, converting a max score into a consistent percentage across a scores table.

Learn how only part of a cell reference becomes absolute to enable correct replication when copying formulas across and down, using a currency converter example with fixed conversion rates.

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.

Master nested if statements in Excel by embedding one if inside another to test multiple criteria and determine pass or fail for math and history scores.

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

Extend if statements by combining countif, sumif, and averageif to count, sum, and average by criteria. Explore department counts and salary-based averages with practical examples.

Learn to use countifs, sumifs, and averageifs across multiple criteria ranges, and fix references with absolute cells when analyzing department and marital status data.

Use VLOOKUP to fetch names and ages by ID from a lookup table, fix table references with absolute references, and employ named ranges for easier data entry.

Use hlookup on the first row of a named data table to return an exact match from a specified row for monthly sales and seasonal pricing across fruits.

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

Explore nested lookup functions in Excel, combining vlookup and hlookup to retrieve a value by two dimensions (month and department) from a cross-tab table, using exact matches.

Learn how to locate data with VLOOKUP and HLOOKUP, handle no-match errors using IF(ISNA(...)) to display friendly messages like wrong ID, and propagate results across name, town, and department fields.

Explore how to retrieve data across tables using index and match, replacing nested lookups. Use exact-match and array-based techniques to pull department sales by month, creating dynamic cross-tab queries.

Learn how to precisely control rounding in Excel using round, round up, and round down, comparing actual values with formatted displays and applying two or more decimal places.

Explore Excel's mround, ceiling, and floor functions to round numbers to a specified multiple. See how positive and negative values behave and how to force rounding up or down.

Learn to extract whole numbers with the int function and compute remainders with mod, applying these techniques to age calculations without rounding.

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.

Create an Excel password generator using a lookup table of uppercase, lowercase, digits, and special characters; concatenate eight random selections to form the password, with a macro button to generate.

Learn to convert Arabic numbers to Roman numerals and back in Excel using the Roman and Arabic functions, with 1–10 examples and year formats.

Explore how to compute mean, median, and mode in Excel using the average, median, and mode functions. Learn how each measure treats data and when they differ.

Learn how to rank data in Excel without sorting by using the rank function to compare a value against a range, determine its position, and choose descending or ascending order.

Explore how to use max, min, large, and small in Excel to identify largest and smallest values, extract top five and bottom five, and format salaries and dates.

Explore counting in excel with count, counta, and countblank, counting numbers, non-empty cells, and blanks across single or multiple non-consecutive ranges, with dynamic updates as data changes.

Explore Excel text functions left, right, and mid, plus len, to extract parts of strings. Learn to specify start positions and lengths, and using dynamic references for flexible data extraction.

Discover how the exact function compares two cells for a case-sensitive match, while find is case sensitive and search is not, and left builds prefixes from the found position.

Remove leading and trailing spaces and non-printable characters from imported data using trim and clean in Excel, then paste values to lock the cleansed results across a column.

Learn three methods to combine text from multiple cells in Excel: the ampersand, the concatenate function, and flash fill, including adding spaces and converting to values as needed.

Learn to change text case in Excel with upper, lower, and proper functions to produce uppercase, lowercase, or title case. Apply case changes inside strings and paste results as values.

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'.

Format numbers and dates inside text by using the text function in Excel, preserving proper formatting in concatenations.

Learn how to display today’s date and the current time in Excel using the today and now functions, with keyboard shortcuts and formatting with the text function.

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 the date diff function, an undocumented Excel feature, to calculate differences between two dates in days, years, and months, enabling age and time-gap analysis.

Learn to calculate time differences in Excel by using workday and networkdays, including handling weekends and holidays with a holiday list to find working days and next working dates.

Calculate a date forward or backward by adding months with edate from a start date. Use eomonth to return the last day of the resulting month, handling leap years.

Convert numeric date parts into usable dates and times using date value and date function, and time value and time function, highlighting concatenation vs parameter passing and four-digit year handling.

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 is family of information functions—ISBLANK, ISNUMBER, ISODD, ISEVEN, ISTEXT—to test a cell’s contents and return true or false, using a single-cell reference.

Master iserror, iserr, iferror, isnA, and ifNA for error checking in Excel, handling #DIV/0!, #N/A, and lookup errors within array and formula workflows.

Discover how the formula text function in Excel reveals the exact formula used in a cell, and how combining it with isformula creates a dynamic, error-safe view across a range.

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.

Master the indirect function in Excel to convert text into cell references or ranges, enabling dynamic sums, named ranges, and row-column based lookups.