TLDR;
Alright doston, this video is about 10 new Excel formulas that will save time and make your work easier. You'll learn how to use these formulas in real-world scenarios. Key formulas discussed include TOCOL, TOROW, TEXTBEFORE, TEXTAFTER, TEXTSPLIT, VSTACK, HSTACK, TAKE, DROP, and how to extract data from a picture.
- TOCOL and TOROW arrange data into a single column or row, respectively.
- TEXTBEFORE and TEXTAFTER extract text before or after a specified delimiter.
- VSTACK and HSTACK combine arrays vertically or horizontally.
- TAKE and DROP extract or exclude specific rows or columns from a dataset.
Intro [0:00]
This video introduces 10 new superfast Excel formulas designed to save time and simplify tasks. The presenter promises a comprehensive understanding of these formulas through real-world applications. These new formulas are very important for productivity.
Torow formula [0:27]
The TOCOL formula arranges data from multiple cells into a single column. The formula is =TOCOL(array, [ignore], [scan_by_column]). The first parameter is the array of cells you want to convert into a column. The second parameter lets you specify what to ignore: 0 means ignore nothing, 1 ignores blank cells, 2 ignores errors, and 3 ignores both blank cells and errors. The third parameter determines whether to scan by row (default) or by column. Scanning by row arranges data row-wise, while scanning by column arranges data column-wise.
Tocol formula [4:19]
The TOROW formula arranges data into a single row. It works similarly to the TOCOL formula, but arranges the data horizontally instead of vertically. The syntax is =TOROW(array, [ignore], [scan_by_column]). The 'ignore' parameter works the same way as in TOCOL, allowing you to specify whether to ignore blanks or errors. The 'scan_by_column' parameter also functions similarly, determining whether the data is scanned row-wise or column-wise before being arranged into a row.
Textbefore formula [6:28]
The TEXTBEFORE formula extracts the text that appears before a specified delimiter in a string. The syntax is =TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]). 'text' is the string you are searching in, and 'delimiter' is the character or string that marks the separation. 'instance_num' specifies which occurrence of the delimiter to use (default is 1). 'match_mode' allows you to specify case-sensitive (0) or case-insensitive (1) matching. 'match_end' specifies whether to treat the end of the text as a match. 'if_not_found' specifies the value to return if the delimiter is not found.
Textafter formula [9:15]
The TEXTAFTER formula extracts the text that appears after a specified delimiter in a string. It's similar to TEXTBEFORE, but it returns the text after the delimiter. The syntax is =TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]). The parameters function the same way as in TEXTBEFORE. For example, you can use it to extract a number after specific text.
Textsplit formula [11:59]
The TEXTSPLIT formula splits text into multiple columns based on a delimiter. The syntax is =TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]). The 'text' parameter is the text you want to split, and 'col_delimiter' is the delimiter to split the text into columns. For example, using a space as a delimiter will split a sentence into individual words in separate columns.
Vstack formula [13:07]
The VSTACK formula vertically stacks arrays or ranges into one. The syntax is =VSTACK(array1, [array2], ...). This formula combines multiple arrays into a single column. The presenter demonstrates how to combine data from different tables into a master sheet, ensuring that new records added to the original tables are automatically updated in the master sheet. To use this practically, convert data into table format by selecting the data and pressing Ctrl+T.
Hstack formula [16:23]
The HSTACK formula horizontally stacks arrays or ranges. The syntax is =HSTACK(array1, [array2], ...). Unlike VSTACK, this formula combines data side by side. The presenter shows how to combine product IDs and quantities sold into a single table using HSTACK.
Take formula [17:38]
The TAKE formula extracts a specified number of rows or columns from the beginning or end of an array. The syntax is =TAKE(array, rows, [columns]). 'array' is the data range, 'rows' is the number of rows to take (positive for top, negative for bottom), and 'columns' is the number of columns to take (positive for left, negative for right). The presenter explains how to extract the top or bottom N records from a dataset, and how to specify the number of columns to include.
Drop Formula [21:40]
The DROP formula excludes a specified number of rows or columns from the beginning or end of an array. The syntax is =DROP(array, rows, [columns]). It's the opposite of the TAKE formula. 'array' is the data range, 'rows' is the number of rows to drop (positive for top, negative for bottom), and 'columns' is the number of columns to drop (positive for left, negative for right).
Data From Picture [22:54]
Excel can extract data from a picture. The presenter shows a picture of data and demonstrates how Excel can convert it into a table. Excel highlights cells where it has some confusion, allowing you to review and correct the data. While the formatting may not be perfect, the data is accurately extracted.
