8 Awesome New Excel Formulas for 2024 | Do you know them?

Kenji Explains
18 Jun 202311:44
EducationalLearning
32 Likes 10 Comments

TLDRThis video introduces eight powerful Excel features to enhance data manipulation and visualization. It covers the V-Stack and H-Stack functions for merging tables, text split and extraction techniques, and the image function to import images into cells. The tutorial also explores advanced functions like TRANSPOSE for reshaping data, TAKE and DROP for selecting specific rows, and EXPAND for dynamic data sets. Additionally, it promotes an Excel course for business and finance to further improve real-world application skills.

Takeaways
  • 📊 The video introduces 8 new Excel features that can enhance data manipulation and visualization.
  • 🔢 The 'V-Stack' formula allows stacking multiple tables vertically into one for easier inventory management.
  • 🔄 The 'H-Stack' function is similar to 'V-Stack' but stacks tables horizontally, which can be useful for side-by-side comparisons.
  • 📝 'Text Split' function can divide full names into first and last names or separate data by rows and columns based on delimiters.
  • 🔍 'Text Before' and 'Text After' functions help extract specific parts of a text string based on a given separator.
  • 🖼️ The 'Image' function enables the insertion of images from the internet directly into Excel cells, useful for visual representation like flags.
  • 📸 'Picture from File' feature in the 'Data' tab allows Excel to import data from a picture of a table, saving time on manual entry.
  • 📋 'T-Call' and 'T-Row' functions can compile data from different areas into a single column or row, respectively.
  • 📊 'Take' function selects specific rows and columns from a dataset, ideal for extracting top performers or items.
  • 📉 'Drop' function is used to exclude certain rows from a dataset, such as bottom performers, similar to 'Take' but in reverse.
  • 📈 'Expand' function dynamically fills in missing data in a table, useful for datasets that are expected to grow over time.
Q & A

  • What is the 'V-Stack' function in Excel used for?

    -The 'V-Stack' function in Excel is used for vertically stacking multiple tables into one, allowing users to compile data from different sources into a single inventory or dataset.

  • How can the 'H-Stack' function be utilized in Excel?

    -The 'H-Stack' function is used for horizontally stacking tables side by side in Excel. It's similar to 'V-Stack' but operates in a horizontal direction, although the script notes its utility might be limited in some contexts.

  • What does the 'Text Split' function do in Excel?

    -The 'Text Split' function in Excel is used to separate text within a cell into different columns or rows based on a specified delimiter, such as a comma or a space.

  • How can the 'Text Before' and 'Text After' functions be used to extract specific parts of a text string?

    -The 'Text Before' and 'Text After' functions can be used to extract parts of a text string that come before or after a specified delimiter. This is useful when you need to isolate certain elements within a longer string of text.

  • What is the purpose of the 'Image' function in Excel?

    -The 'Image' function in Excel allows users to import an image from the internet into a specific cell. This can be particularly useful for incorporating images such as flags or logos directly into a spreadsheet.

  • How can Excel handle images that contain tabular data?

    -Excel can handle images with tabular data by using the 'Picture from File' feature under 'Data' and 'Get & Transform Data'. This allows Excel to load and convert the image data into an editable format, although formatting may be lost in the process.

  • What is the 'Append' function used for in Excel?

    -The 'Append' function in Excel is used to combine lists or datasets. It allows users to concatenate data from different sources into a single column without having to manually copy and paste.

  • What does the 'Row' function do in Excel?

    -The 'Row' function in Excel is similar to 'Append' but operates in a horizontal manner, concatenating data into a single row rather than a column.

  • How can the 'Take' and 'Drop' functions be used to select specific parts of a dataset?

    -The 'Take' function is used to select the top N rows from a dataset, while the 'Drop' function is used to select the bottom N rows. These functions are useful for quickly accessing the highest or lowest values in a sorted list.

  • What is the 'Expand' function in Excel and how does it help with dynamic data?

    -The 'Expand' function in Excel is used to dynamically fill in missing values in a dataset when new data becomes available. It's particularly useful for datasets that are expected to grow over time, such as monthly sales figures.

  • How can Excel courses like the one mentioned in the script help improve one's skills?

    -Excel courses, such as the one mentioned in the script, can help improve skills by offering theoretical lessons on formatting, formulas, and charts, as well as practical case studies that simulate real-world work scenarios. They also provide support through instructor questions in discussion forums.

Outlines
00:00
📊 Excel's New Features: V-Stack and H-Stack Functions

This paragraph introduces two new Excel functions: V-Stack and H-Stack. V-Stack merges multiple tables vertically into one, as demonstrated by compiling two stories from San Francisco and LA into a total inventory. The process involves selecting the areas to stack and using the V-Stack function. H-Stack, on the other hand, stacks tables horizontally. The paragraph also shows how to use these functions with examples, highlighting their simplicity and utility in data consolidation.

05:02
🔑 Text Manipulation Techniques in Excel

The second paragraph delves into advanced text manipulation techniques in Excel. It covers the Text Split function, which can separate full names into first and last names using a delimiter, and can also be applied to split data by rows. Additionally, the paragraph explains how to extract specific parts of text using the Text Before and Text After functions, which are useful for dealing with complex data strings that require isolation of certain elements, such as an ID number or a price.

10:04
🌐 Importing Images and Advanced Data Functions in Excel

This paragraph showcases the Image function in Excel, which allows users to import images from the internet into specific cells, demonstrated with the example of inserting flags of different countries. It also introduces the Take and Drop functions, which enable the selection of specific rows or columns from a dataset. The Take function is used to select the top performers in sales, while the Drop function is used to identify the bottom performers. The explanation includes step-by-step instructions on how to apply these functions to analyze and manipulate data effectively.

📈 Dynamic Data Expansion with Excel Functions

The final paragraph discusses the Expand function in Excel, which is used to dynamically fill in data for a growing dataset. The example provided shows how to use the function to fill in monthly sales figures from January to July, and how it can be adapted to include data for the entire year. The paragraph explains the syntax and parameters of the Expand function, emphasizing its utility in creating dynamic and automatically updating tables in Excel.

Mindmap
Keywords
💡V-Stack Formula
The V-Stack formula is a function in Excel that allows users to vertically merge multiple tables into a single table. In the context of the video, it is used to compile data from two different stories, one in San Francisco and one in LA, into a total inventory. The script demonstrates how to use the V-Stack function by selecting the areas of the tables and merging them into one, showcasing its utility in data consolidation.
💡H-Stack
H-Stack is the horizontal counterpart to the V-Stack function in Excel, which stacks tables side by side. The video script mentions the H-Stack function as an alternative to vertical stacking, providing a quick demonstration of how to use it to stack tables next to each other. Although the script notes that in the given example, H-Stack may not be particularly useful, it highlights the versatility of Excel's stacking functions.
💡Text Split
The Text Split function in Excel is used to divide a text string into separate components based on a specified delimiter. The video script illustrates this by showing how to split a full name into first and last names using a comma as the delimiter. Additionally, the script demonstrates splitting text by rows instead of columns, emphasizing Excel's flexibility in handling and organizing text data.
💡Text Before/After
The Text Before and Text After functions in Excel are used to extract parts of a text string that come before or after a specified delimiter. The video script provides an example where an ID number and price need to be extracted from a string that includes unwanted characters. By using Text Before for the ID and Text After for the price, the script shows how to effectively isolate and utilize specific data segments within a text string.
💡Image Function
The Image function in Excel enables users to import images from the internet into a specific cell. The video script describes how to use this function to insert a flag of a country, such as France, by copying the image's URL and pasting it into the function. This feature is highlighted for its ability to embed images directly into cells, maintaining the cell's dimensions and position.
💡Picture from File
The 'Picture from File' feature in Excel allows users to convert an image of a table into an editable Excel table. The video script explains the process of using this feature to import data from an image file, which is particularly useful when the data is already presented in a tabular format within an image. Although the formatting is lost during the import, the script emphasizes the time-saving aspect of this feature.
💡Append Function
The Append function in Excel is similar to V-Stack but is used to concatenate lists vertically. The video script demonstrates how to use the Append function to combine employee lists from London and New York under a single category 'All Staff'. This function is showcased as a simple and efficient way to merge data from different sources into a unified list.
💡Transpose Function
The Transpose function in Excel is used to rearrange the data from rows to columns or vice versa. The video script briefly mentions Transpose in comparison to the Append function, indicating that while Append works for vertical lists, Transpose can be used for horizontal lists. The script uses this function to show how data can be reoriented within Excel.
💡Take and Drop Functions
The Take and Drop functions in Excel allow users to selectively extract specific rows or columns from a data set. The video script explains how to use the Take function to select the top three brands by unit sold and the Drop function to find the bottom three. These functions are demonstrated with examples of how to dynamically adjust the data set based on the user's needs, such as focusing on the top or bottom performers.
💡Expand Function
The Expand function in Excel is used to dynamically fill in missing data in a table, especially when the data set is expected to grow. The video script provides an example of using the Expand function to fill in sales figures for months that initially had no data. By setting the 'pad width' to a placeholder value like 'n/a', the script shows how Excel can automatically update the table as new data becomes available.
Highlights

Introduction of 8 new Excel features that many users may not be aware of.

The V-Stack formula for merging multiple tables into a single one.

Demonstration of stacking tables vertically using the V-Stack function.

Introduction of H-Stack for horizontal table merging.

The Text Split function for separating names or other data into components.

Using Text Split to divide data by column delimiters.

Splitting data by row delimiters with Text Split for different data separation needs.

Text Before and Text After functions for extracting specific data segments.

The Image function to import images from the internet into Excel cells.

Practical example of using the Image function to insert a country's flag.

Promotion of an Excel course for business and finance with real-world applications.

The Transpose function for converting data from rows to columns or vice versa.

Using the Transpose function to combine employee lists from different locations.

Take and Drop functions for selecting specific rows or columns from a dataset.

Practical use of the Take function to extract the top performers from a dataset.

Utilizing the Drop function to identify the bottom performers in a dataset.

The Expand function for dynamically updating datasets as new data is added.

Example of using the Expand function to handle growing datasets with monthly data.

Final thoughts on the importance of practicing new Excel functions for proficiency.

Transcripts

Browse More Related Video

Master Data Cleaning Essentials on Excel in Just 10 Minutes

Top 10 Most Important Excel Formulas - Made Easy!

5 Advanced Excel Formulas You Probably Didn't Know!

Top 10 Advanced Excel Tricks for Data Analysis - FREE Masterclass with Sample Files

Master the IF Formula in Excel (Beginner to Pro)

Excel Formulas and Functions | Full Course

Rate This

5.0 / 5 (0 votes)

Thanks for rating:

Related Tags
Excel TipsData ManagementV-StackText SplitImage ImportTable FunctionsDynamic DataTake & DropExpand FunctionBusiness Tools