Awesome formula tricks in Excel
Excel Tips and Tricks

Five awesome formulas in Excel

In this article, we have explained 5 different formulas with examples.  Below is the list of those 5 functions

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

 

WRAPROWS function in Excel
WRAPROWS function in Excel

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

 

WRAPCOLS function in Excel
WRAPCOLS function in Excel

 

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.

 

TOCOL function in Excel
TOCOL function in Excel

 

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.

TOROW function in Excel
TOROW function in Excel

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

 

ARRAYTOTEXT function with ConsiseARRAYTOTEXT function with Concise format
ARRAYTOTEXT function with Concise format
ARRAYTOTEXT function with Strict format
ARRAYTOTEXT function with Strict format

Watch the step-by-step video tutorial:

Click here to download the practice file

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 12 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com