5 Advanced Excel Formulas You Probably Didn't Know!
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
📊 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.
🔢 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.
🔎 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
💡Delimiter
💡Offset Function
💡XLOOKUP Function
💡Wildcard
💡INDEX and MATCH Combo
💡Ampersand (&)
💡SUM and FILTER Combo
💡Financial Modeling
💡Dynamic Range
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
5.0 / 5 (0 votes)
Thanks for rating: