Change data in a range of cells into a single column or row

Oct 27, 2022 | Excel Hints and Tips

A couple of new functions that are available are TOCOL and TOROWThese allow a range of cells to be transformed into a single column or a single row of data. This is much easier than performing the same task manuallyA range of cells larger than those required can always be selected and blank cells ignored as part of the function.

If you haven’t got these functions yet then they are only available in Microsoft 365, not the stand alone versions of ExcelIf are using M365 and haven’t got them then they should roll out to you soon. 

TOCOL Function 

This function changes a range of cells into a single column 

Structure 

=TOCOL(array, [ignore], [scan_by_column]) 

Array – The range of cells to return as a column 

Ignore – Whether to ignore types of values.  By default no values are ignored. 

  • 0 – Keep all values (default) 
  • 1 – Ignore blanks 
  • 2 – Ignore errors 
  • 3 – Ignore blanks and errors 

Scan by column – By default the data is scanned by row.  To scan by column use the word TRUE.  This determines whether the values are ordered by row or column 

NOTE: The function automatically fills down to the appropriate number of rows.  Only the calculation in the first of these cells can be edited and the others change automatically. 

Example 

This example puts the data in cells A1 to C3 into a single column, ignoring any blank cells. 

TOCOL example for Microsoft Excel

TOROW Function 

This is very similar to TOCOL but this function changes a range of cells into a single row 

Structure 

=TOROW(array, [ignore], [scan_by_column]) 

Array – The range of cells to return as a row 

Ignore – Whether to ignore types of values.  By default no values are ignored. 

  • 0 – Keep all values (default) 
  • 1 – Ignore blanks 
  • 2 – Ignore errors 
  • 3 – Ignore blanks and errors 

Scan by column – By default the data is scanned by row.  To scan by column use the word TRUE.  This determines whether the values are ordered by row or column 

NOTE: The function automatically fills across the appropriate number of columns.  Only the calculation in the first of these cells can be edited and the others change automatically. 

Example 

This example puts the data in cells A1 to C3 into a single row, ignoring any blank cells. 

TOROW example for Microsoft Excel

Further Reading

If you’ve enjoyed reading how to change data from a range of cells into a single row or column in Microsoft Excel, there’s some other blogs below that you might find useful:

Want to learn more about Microsoft Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Excel Courses I run.