5 Advanced Excel Formulas You Probably Didn't Know!

Kenji Explains
13 Aug 202311:40
EducationalLearning
32 Likes 10 Comments

TLDRThis tutorial video guides viewers through five advanced Excel formulas, starting with the text split function to separate names into first and last names, considering various titles as delimiters. It then explores a combination of sum and offset functions to calculate workdays needed for financial goals. The video also covers the use of wildcards in the XLOOKUP function for partial matches and the INDEX-MATCH-Ampersand combo for multi-criteria lookups. Finally, it dives into the SUM-FILTER combo for dynamic data analysis, showcasing how to sum revenue by manager for specific years. The video promises to enhance viewers' Excel skills for real-world applications.

Takeaways
  • πŸ“Š The video introduces five advanced Excel formulas to enhance one's skills in data manipulation.
  • πŸ”‘ The 'TEXTSPLIT' function is a new feature in Excel that can split names into first and last names based on a delimiter, such as a space.
  • πŸ”§ To handle inconsistencies like titles in names, additional delimiters such as 'Dr.' and 'Mr.' can be added to the TEXTSPLIT function.
  • βš™οΈ The 'IGNORE EMPTY' option within the TEXTSPLIT function can be utilized to clean up empty spaces resulting from the split.
  • πŸ’° A combination of SUM and OFFSET functions can be used to calculate the total profit based on a dynamic number of workdays.
  • πŸ” The 'XLOOKUP' function with wildcards allows for flexible searching, even when the exact name or data is not remembered.
  • πŸ”— The 'INDEX' and 'MATCH' functions can be combined with the '& (Ampersand)' to perform searches with multiple criteria, such as country, year, and month.
  • πŸ“ˆ The 'FILTER' function can be used to dynamically filter data based on multiple conditions, such as manager and year, to sum up revenues.
  • πŸ“š The video mentions a course that covers financial statement analysis, financial modeling, and valuation using Excel, which includes real-world examples like Apple's annual report.
  • πŸŽ“ The presenter suggests checking out a link in the description for more information on financial evaluation courses, indicating a resource for further learning.
Q & A
  • What is the main focus of the video?

    -The video focuses on teaching five advanced Excel formulas to help users take their Excel skills to the next level.

  • What is the first advanced Excel function discussed in the video?

    -The first advanced function discussed is the 'TEXTSPLIT' function, which is used to split names into first and last names.

  • How does the TEXTSPLIT function handle multiple delimiters?

    -The TEXTSPLIT function can handle multiple delimiters by adding additional delimiters within the function, such as 'Doctor', 'Mister', and a space, separated by commas.

  • What is the purpose of the 'ignore empty' option in the TEXTSPLIT function?

    -The 'ignore empty' option in the TEXTSPLIT function is used to ignore and not create empty cells when splitting text.

  • What combination of functions is used to calculate the total profit for a given number of workdays?

    -The combination of SUM and OFFSET functions is used to calculate the total profit based on the number of workdays.

  • How does the video demonstrate adjusting the OFFSET function for dynamic results?

    -The video shows adjusting the OFFSET function by using a dynamic reference to the number of workdays, which allows the function to change based on the input.

  • What is the purpose of the XLOOKUP function with a wildcard feature?

    -The XLOOKUP function with a wildcard feature is used to find a match in a dataset even when the exact name or value is not known, allowing for partial matches.

  • How does the INDEX and MATCH functions work together to filter data based on multiple criteria?

    -The INDEX function is used in conjunction with the MATCH function to filter data based on multiple criteria by combining the lookup values and using the AND operator to match against multiple columns.

  • What is the final advanced Excel formula discussed in the video?

    -The final advanced Excel formula discussed is the SUM and FILTER combination, which is used to sum values based on multiple criteria.

  • How does the video suggest using the FILTER function to sum values?

    -The video suggests using the FILTER function to create a subset of data that meets specific criteria and then using the SUM function to add up the values in that subset.

  • What additional resources does the video recommend for learning more about financial statements?

    -The video recommends a complete Finance Evaluation course where they teach Financial Accounting, Finance, valuation, and financial modeling on Excel, including financial statement analysis using Apple's real annual report as an example.

Outlines
00:00
πŸ“Š Advanced Excel Formulas: Text Split Function

This paragraph introduces five advanced Excel formulas, starting with the Text Split function, a relatively new feature in Excel. The function is used to separate names into first and last names using spaces as delimiters. The formula is demonstrated with '=TEXTSPLIT', where the name is the text to be split, and a space in quotation marks is the delimiter. The function is then adjusted to include additional delimiters like 'Doctor' and 'Mister' to filter out titles. An 'ignore empty' option is also highlighted to clean up any empty spaces resulting from the split.

05:02
πŸ”’ Combining Functions for Dynamic Calculations

The second paragraph discusses a combination of Excel functions to calculate the amount of money saved up for a holiday based on the number of workdays. The formula uses 'SUM' to calculate the total profit and the 'OFFSET' function to dynamically adjust the range based on the number of workdays entered. The OFFSET function references a starting cell, and then offsets by a certain number of rows and columns, with '-1' used to adjust for the starting reference cell. The paragraph also mentions a course on financial evaluation, which covers financial statement analysis, financial modeling, and valuation techniques using real-world examples.

10:04
πŸ”Ž XLOOKUP with Wildcard for Flexible Data Matching

The third paragraph explains how to use the XLOOKUP function with a wildcard to find sales amounts for companies that may have varying suffixes in their names, such as 'Amazon UK' or 'Tesla Inc'. The formula includes an asterisk (*) within the search term to account for any additional text after the base company name. The XLOOKUP function is used to find the sales amount corresponding to the company name with potential suffixes, and the wildcard character is specified in the 'match mode' argument to enable this feature.

πŸ—‚ Index Match with Multiple Criteria Using Ampersand

This paragraph demonstrates how to use the INDEX and MATCH functions in combination with the Ampersand operator to filter data based on multiple criteria, such as country, year, and month. The INDEX function is used to specify the array of sales figures, and the MATCH function is used twice to filter based on the country and then the year and month. The Ampersand operator is used to combine the year and month into a single lookup value for the second MATCH function, allowing for the retrieval of sales data for a specific time period and location.

πŸ“ˆ Summing Values with FILTER and SUM for Comprehensive Data Analysis

The final paragraph covers the use of the FILTER and SUM functions to calculate the total revenue brought in by a manager for a specific year. The FILTER function is used to isolate the revenues associated with the manager's name, and then another FILTER function is applied to further narrow down the data to a specific year. The SUM function is then used to add up all the filtered values, providing a total revenue figure for the manager in the given year. The dynamic nature of the formula allows for easy updates by changing the year or manager's name.

Mindmap
Keywords
πŸ’‘Text Split Function
The Text Split Function is a feature in Excel used to separate text within a cell into multiple cells based on a specified delimiter. In the video, it is used to split full names into first names and last names using a space as the delimiter. The function is demonstrated with an example where names like 'Dr Fernando Alonso' are split, and additional delimiters like 'Doctor' and 'Mister' are added to handle titles consistently.
πŸ’‘Delimiter
A delimiter in the context of text splitting refers to a character or a set of characters that mark the boundary between separate pieces of information. In the video, spaces are used as delimiters to split names, and the terms 'Doctor' and 'Mister' are also used as delimiters to ensure that titles are correctly separated from names.
πŸ’‘Offset Function
The Offset function in Excel is used to dynamically reference a range of cells based on a starting point and a specified number of rows and columns away from that starting point. In the video, the Offset function is used in combination with the SUM function to calculate the total profit for a variable number of workdays, adjusting the range dynamically based on user input.
πŸ’‘XLOOKUP Function
The XLOOKUP function is a powerful lookup function in Excel that allows for searching for items in a table and returning related information. It has a wildcard feature that enables partial matches. In the script, XLOOKUP is used to find sales amounts for companies like 'Apple' even if the full company name includes additional text, such as 'Apple emia' or 'Amazon UK'.
πŸ’‘Wildcard
A wildcard in Excel is a special character that can represent one or more characters in a search pattern. In the video, an asterisk (*) is used as a wildcard in combination with XLOOKUP to find matches that start with a certain text followed by any additional text, which is useful for partial or uncertain data entry.
πŸ’‘INDEX and MATCH Combo
The combination of INDEX and MATCH functions is a common technique in Excel to perform lookups based on multiple criteria. INDEX retrieves values from a specified range, while MATCH finds the position of an item in a range. In the video, this combo is used to find sales figures for Spain for a specific year and month, overcoming the limitation of having more than one column criterion.
πŸ’‘Ampersand (&)
In Excel, the ampersand (&) is used for concatenation, which means joining strings of text together. In the video, the ampersand is used to combine the year and month into a single lookup value for the MATCH function within the INDEX and MATCH combo, allowing for multi-criteria lookups.
πŸ’‘SUM and FILTER Combo
The SUM and FILTER combo is a method to aggregate data that meets specific criteria across multiple dimensions. In the script, this combo is used to calculate the total revenue brought in by a manager, 'Bill', for the year 2020. It involves first filtering the data for the manager and then for the year, and finally summing the filtered values to get the total.
πŸ’‘Financial Modeling
Financial Modeling is the process of creating an abstract representation of a financial situation or company to make predictions, understand potential outcomes, or support decision-making. The video mentions a course that covers financial modeling on Excel, which likely involves using the advanced formulas discussed to create a model based on real financial statements.
πŸ’‘Dynamic Range
A dynamic range in Excel refers to a range of cells that can change based on certain inputs or criteria. In the video, the term is used in the context of the OFFSET function, where the range adjusts based on the number of workdays entered, and in the SUM and FILTER combo, where the range of revenue figures changes based on the manager and year selected.
Highlights

Introduction to five advanced Excel formulas that took years to learn.

Explanation of the text split function to separate first and last names in Excel.

How to handle inconsistencies in data using additional delimiters in the text split function.

Using the SUM function combined with OFFSET to calculate profit based on variable work days.

Dynamic calculation of profit by adjusting the number of work days with the SUM and OFFSET functions.

Introduction to the XLOOKUP function with wildcard feature for partial string matching.

Dealing with variations in company names using wildcard in XLOOKUP for accurate sales data retrieval.

Combining INDEX and MATCH functions to handle multi-criteria data lookup.

Using the AMPERSAND operator to combine multiple criteria for INDEX and MATCH functions.

The SUM and FILTER functions combination for aggregating data based on multiple conditions.

Filtering data by both manager and year to calculate total revenue with dynamic Excel formulas.

Dynamic Excel formulas that adjust automatically to changes in criteria such as year or manager.

Practical applications of advanced Excel formulas in real-world settings.

Promotion of a financial model video for further application of Excel formulas.

Invitation to subscribe and like for more advanced Excel tips and tutorials.

Transcripts
Rate This

5.0 / 5 (0 votes)

Thanks for rating: