Excel TOCOL Function
On March 16, 2022, Microsoft announced 14 new Excel functions. Most of them are text functions. The new Excel TOCOL function is one the most useful functions. It is high time to learn about this function.
Summary
When you have multiple columns and NEED to combine them into a single column, you have the magic Excel TOCOL Function. It is highly useful when you need to prepare one list from separate list of names or other form of data. The TOCOL function returns a column vector that contains all the items in the source array.
Syntax
=TOCOL(array, [ignore], [scan_by_col])
where,
- array = the array or reference to return as a column.
- ignore = Whether you want to ignore a certain types of values. By default, all values are considered.
- scan_type_column = Scan the array by column. The array, by default, is scanned by row.
Example 1: We have three lists and want to prepare one list from them, indicating that we want to prepare one list from three list. We want to combine the values of three lists into one list.
Answer: The formula : =TOCOL (B5:D7), which returns the table with names of all group.
Example 2: TOCOL with UNIQUE: We want to list the unique names from all the group in one list. In that case, we need to use the combination of UNIQUE and TOCOL.
Answer: The formula : =UNIQUE(TOCOL(B5:D9)), which returns the new list of unique people.
Further reading
Microsoft announced 14 new Excel Functions
More New Functions
I’m still learning from you, as I’m making my way to the top as well. I certainly enjoy reading everything that is posted on your site.Keep the posts coming. I loved it!