In this article, we will explain you 5 awesome formulas in Excel. You will learn 5 new formulas in Excel. we have explained WRAPROWS, WRAPCOLS, TOROW, TOCOL and ARRAYTOTEXT function step by step with examples.
5 awesome formulas in Excel
1- WRAPROWS:
WRAPROWS function converts the single row or column data into multiple rows according to the wrap count given in the parameter.
Syntax
=WRAPROWS(vector, wrap_count, [pad_with])
In this function syntax has the below arguments:
Vector: Range to wrap.
wrap_count: Number of values in each row after that new row will start.
pad_with: #N/A error will appear if no value is available to display from the vector. You can put your value to display in place of #N/A
2- WRAPCOLS:
WRAPCOLS function converts the single row or column data into multiple columns according to the wrap count given in the parameter.
Syntax
=WRAPROWS(vector, wrap_count, [pad_with])
In this function syntax has the below arguments:
vector: Range to wrap.
wrap_count: Number of values in each row after that new column will start.
pad_with: #N/A error will appear if no value is available to display from the vector. You can put your value to display in place of #N/A
3- TOCOL:
TOCOL function returns an entire range into a single column only. It will append the data from multiple columns and rows.
Syntax
=TOCOL(array, [ignore], [scan_by_column])
The TOCOL function syntax has the below arguments:
array: Reference of a range that is to be converted into a single column.
Ignore: This is an optional parameter. If you want to ignore the blanks or errors or both then you can select 1,2,3 respectively. By default, it will consider all values.
scan_by_column: You can choose whether you want to scan your range by column or by rows to convert the data into one column. By default, it will scan by row.
3- TOROW:
TOROW function returns an entire range into a single row only. It will add the data from multiple columns and rows.
Syntax
=TOROW(array, [ignore], [scan_by_column])
The TOROW function syntax has the below arguments:
array: Reference of a range that is to be converted into a single row.
Ignore: This is an optional parameter. If you want to ignore the blanks or errors or both then you can select 1,2,3 respectively. By default, it will consider all values.
scan_by_column: You can choose whether you want to scan your range by column or by rows to convert the data into one row. By default, it will scan by row.
5- ARRAYTOTEXT:
ARRAYTOTEXT function is used to convert an array or range into a comma-separated list of values from the range. It can also create a list of values with escape characters and row delimiters if you select the Strict format type.
Syntax
ARRAYTOTEXT(array, [format])
The ARRAYTOTEXT function syntax has two arguments.
array: Range reference which must be converted into a comma-separated list or list of values with escape characters and row delimiters
format: This is an optional parameter. Here you can select 0 for Concise format which is by default and 1 for Strict format list or list of values with escape. characters and row delimiters
Visit our YouTube channel to learn step-by-step video tutorials
