5 Advanced Excel Formulas You Probably Didn't Know!

Excel introduces the TEXTSPLIT function to efficiently break apart names using a space as the initial delimiter. The approach is refined by incorporating additional delimiters such as specific title markers like 'Doctor' and 'Mis' to filter out undesired parts. An option to ignore empty cells further ensures robust and consistent data extraction across multiple rows.

A dynamic Excel formula is created to compute total profit based on the number of workdays, enabling planning for holidays. The function starts with a fixed profit cell and adjusts the range by combining SUM with OFFSET. By using the OFFSET function to shift the range dynamically—subtracting one to include the reference cell—the formula accurately sums the profit over the designated days of work.

XLOOKUP can be adapted to handle company names with additional trailing text by concatenating the lookup value with an asterisk. This approach retrieves corresponding sales figures even when the names include extra details like 'Inc' or regional identifiers. Utilizing the wildcard in the match mode enables the accurate extraction of data, as seen with examples like Apple and Tesla, while also highlighting its broader application in financial analysis.

A technique is introduced that extends the typical INDEX MATCH beyond two criteria by combining additional parameters with an ampersand. An array of sales figures is identified, with one match pinpointing the country while a second, concatenated match handles both the year and month simultaneously. The method dynamically retrieves the correct sales figure, such as returning 110,000 for Spain in January 2022, and adjusts seamlessly when the lookup values change.

A formula is crafted to compute total revenue brought in by a manager by filtering through multiple instances in a dataset. The approach circumvents the limitations of an index match by employing a filter function that selects all revenue figures for a manager and then further refines them by a specified year. Wrapping these filters within a sum function consolidates the values into one dynamic output that adjusts when the year parameter changes. This method provides a robust and flexible solution for financial modeling in complex, real-world data scenarios.