Excel TOROW Function: Flatten Any Range into a Single, Dynamic Row

20Oct

Excel TOROW Function: Flatten Any Range into a Single, Dynamic Row

The TOROW function converts any multi-dimensional array into a single horizontal row. Whether you’re consolidating data from scattered ranges, preparing data for iteration, or creating input arrays for other functions, TOROW transforms complex structures into linear sequences with one simple formula.

Table of Contents

What Does the TOROW Function Do?

TOROW takes any array – whether it’s a single column, multiple columns, multiple rows, or a full matrix – and flattens it into a single horizontal row. This function is essential for data transformation, preparing arrays for processing functions, and creating flexible data pipelines that adapt to input dimensions automatically.

Unlike manual concatenation or complex helper column strategies, TOROW intelligently handles any array shape and orientation. When combined with other dynamic array functions like TOCOL, WRAPROWS, WRAPCOLS, and SEQUENCE, TOROW enables sophisticated data reshaping and transformation workflows that would otherwise require extensive manual work.

TOROW Syntax and Arguments

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

array (required): The range or array to convert into a single row. Can be any size or shape – column, row, or matrix.
ignore (optional): Specifies which values to ignore during flattening. Options are 0 (no ignoring), 1 (ignore blanks), or 2 (ignore errors). Default is 0.
scan_by_column (optional): Determines reading direction. Use FALSE or 0 to read row-by-row (default); use TRUE or 1 to read column-by-column down first, then across.

Key Rules:

Output is always a single horizontal row (1 × N array).
All values from the input array are preserved (unless ignored via the ignore parameter).
TOROW spills automatically across as many columns as needed.
When ignore=1, blank cells are skipped; when ignore=2, errors are skipped.
scan_by_column controls whether data is read across rows first or down columns first.

Key Behaviors and Important Notes

Always produces single row: No matter what you input, TOROW outputs exactly 1 row with multiple columns.
Spill behavior: TOROW is a dynamic array function that automatically spills across columns. If cells to the right are occupied, you get #SPILL! error.
Ignore blanks option: When ignore=1, empty cells within the source array are excluded from the output, resulting in a more compact row.
Ignore errors option: When ignore=2, error values like #N/A, #DIV/0!, and #VALUE! are skipped.
Scan direction matters: scan_by_column=FALSE reads row 1 left-to-right, then row 2 left-to-right, etc. scan_by_column=TRUE reads column A top-to-bottom, then column B top-to-bottom, etc.
Input flexibility: TOROW accepts vertical ranges, horizontal ranges, matrices, or spilled arrays from other functions.
Live updates: When source data changes, TOROW automatically recalculates and adjusts output width.

Basic Examples

Example 1: Flatten a vertical column

Convert a vertical list in A1:A10 into a single row:

=TOROW(A1:A10)

Result: A 1×10 array with all values from the column displayed horizontally.

Example 2: Flatten a matrix row-by-row

Convert a 3×4 matrix (A1:D3) into a single row:

=TOROW(A1:D3)

Result: Row 1 cells (A-D), then row 2 cells (A-D), then row 3 cells (A-D), all in one horizontal line (1×12).

Example 3: Flatten column-by-column

Same matrix but read column-by-column instead:

=TOROW(A1:D3, 0, TRUE)

Result: Column A top-to-bottom (A1, A2, A3), then column B, then column C, then column D (still 1×12, but different order).

Example 4: Flatten and ignore blanks

Convert a range with empty cells, skipping the blanks:

=TOROW(A1:A20, 1)

Result: Only non-blank values from the range in a single row, skipping empty cells.

Example 5: Flatten and ignore errors

Convert a range containing #N/A or other errors, excluding them:

=TOROW(A1:D10, 2)

Result: All values except errors flattened into a single row.

Example 6: Flatten and ignore both blanks and errors

Clean flattening excluding both blanks and errors:

=TOROW(A1:D20, 3)

Result: Only valid data values, no blanks or errors, in a single horizontal row.

Creating Practical Row Transformations

Consolidate scattered data into single row

Combine multiple ranges from different locations:

=HSTACK(
  TOROW(Q1_Data),
  TOROW(Q2_Data),
  TOROW(Q3_Data),
  TOROW(Q4_Data)
)

Creates a single quarterly results row for summary reporting.

Prepare data for SEQUENCE processing

Flatten uneven data into processable format:

=TOROW(
  FILTER(Source_Data, Source_Data <> ""),
  1
)

Filters out blanks, flattens to a row, ready for further transformation.

Create lookup array for INDEX/MATCH

Convert vertical list into searchable horizontal array:

=TOROW(
  UNIQUE(Source_List),
  1
)

Removes duplicates and blanks, creates a horizontal unique list for searching.

Transpose and flatten combined operation

Flip orientation and flatten simultaneously:

=TOROW(TRANSPOSE(A1:D10), 1)

Transposes the matrix and immediately flattens to a single row, skipping blanks.

Advanced Function Combinations

TOROW + WRAPROWS for matrix reshaping

=WRAPROWS(
  TOROW(A1:D20, 1),
  5
)

Flattens the matrix skipping blanks, then wraps into rows of 5 items each.

TOROW + SORT for ordered flat array

=TOROW(
  SORT(A1:D100, 2, 1),
  1
)

Sorts the range by column 2 ascending, flattens to a single row, ignoring blanks.

TOROW + UNIQUE + FILTER for clean list

=TOROW(
  UNIQUE(FILTER(A:A, A:A <> "")),
  1
)

Filters out blanks, removes duplicates, flattens unique values into a row.

TOROW for cross-tabulation flattening

=HSTACK(
  VSTACK(
    {"Region"},
    TOROW(Pivot_Table[Regions], 1)
  ),
  VSTACK(
    {"Sales"},
    TOROW(Pivot_Table[Values], 1)
  )
)

Flattens pivot table regions and values into comparable rows.

TOROW + SEQUENCE for numbered lists

=HSTACK(
  SEQUENCE(1, COLUMNS(TOROW(A1:D100))),
  TOROW(A1:D100, 1)
)

Creates sequential numbers alongside flattened data for position tracking.

Multi-stage transformation with TOROW

=TOROW(
  WRAPROWS(
    TOROW(
      FILTER(Raw_Data, Raw_Data <> ""),
      1
    ),
    4
  ),
  1
)

Flattens, wraps into rows, then flattens again – useful for complex reshaping pipelines.

Real-World Applications

Consolidate quarterly reports into single summary row

Flatten financial data from four quarters:

=VSTACK(
  {"Annual Summary Row"},
  TOROW(
    HSTACK(
      TOROW(Q1_Revenue, 1),
      TOROW(Q2_Revenue, 1),
      TOROW(Q3_Revenue, 1),
      TOROW(Q4_Revenue, 1)
    ),
    1
  )
)

Creates a single-row annual summary for dashboard display.

Survey data consolidation

Flatten scattered survey responses into searchable format:

=TOROW(
  FILTER(
    HSTACK(
      Respondent_Names,
      Survey_Responses
    ),
    Survey_Responses <> ""
  ),
  1
)

Creates a flat list of valid responses for analysis.

Inventory reconciliation

Flatten warehouse data for consolidation:

=TOROW(
  FILTER(
    HSTACK(
      Warehouse_A[Items],
      Warehouse_B[Items],
      Warehouse_C[Items]
    ),
    "" <> HSTACK(
      Warehouse_A[Items],
      Warehouse_B[Items],
      Warehouse_C[Items]
    )
  ),
  1
)

Consolidates inventory from three warehouses into a single flat list.

Data export for external systems

Flatten complex data structure for API consumption:

=TOROW(
  UNIQUE(
    FILTER(
      HSTACK(Column_A, Column_B, Column_C),
      HSTACK(Column_A, Column_B, Column_C) <> ""
    )
  ),
  1
)

Removes duplicates and blanks, flattens to export-ready format.

Dashboard metric aggregation

Combine multiple metric ranges into single row:

=HSTACK(
  VSTACK({"Metrics"}),
  VSTACK(TOROW(
    HSTACK(
      Sales_Metrics,
      Customer_Metrics,
      Efficiency_Metrics
    ),
    1
  ))
)

Creates a one-row dashboard with all key metrics in sequence.

Understanding the Ignore Parameter

Ignore Value	Behavior	Example Input	Example Output
0 (default)	Include everything (blanks and errors)	{1, “”, 2, #N/A, 3}	{1, “”, 2, #N/A, 3}
1	Ignore blanks only	{1, “”, 2, #N/A, 3}	{1, 2, #N/A, 3}
2	Ignore errors only	{1, “”, 2, #N/A, 3}	{1, “”, 2, 3}
3	Ignore both blanks and errors	{1, “”, 2, #N/A, 3}	{1, 2, 3}

Understanding Scan Direction

The scan_by_column parameter determines how TOROW reads the input array:

Setting	Scan Direction	Input (3×3 Matrix)	Output Order
FALSE or 0 (default)	Row-by-row (left-to-right, then next row)	A B C / D E F / G H I	A B C D E F G H I
TRUE or 1	Column-by-column (top-to-bottom, then next column)	A B C / D E F / G H I	A D G B E H C F I

Choose scan_by_column based on how you want data ordered in the output row.

TOROW vs. TOCOL: Complementary Functions

Function	Output Shape	Use Case	Example
TOROW	1 row × many columns (horizontal)	Flatten to horizontal for dashboards, exports, or further reshaping	=TOROW(A1:D10)
TOCOL	Many rows × 1 column (vertical)	Flatten to vertical for lists, processing, or iteration	=TOCOL(A1:D10)

Common Mistakes and How to Avoid Them

Mistake 1: Forgetting about #SPILL! with wide output

TOROW can produce very wide rows. If cells to the right are occupied, spilling fails.

Wrong: Place TOROW(A1:Z1000) in column A with data in columns AA onwards → #SPILL!

Correct: Place formula in an area with many empty columns to the right

Mistake 2: Confusing ignore parameter values

Unclear which ignore value does what can lead to unexpected output.

Remember: 1 = ignore blanks; 2 = ignore errors; 3 = ignore both

Mistake 3: Using TOROW when TOCOL is needed

If you need vertical output, use TOCOL, not TOROW.

Wrong thinking: “I want a column” → Use TOCOL, not TOROW

Correct thinking: “I want a row” → Use TOROW

Mistake 4: Not accounting for scan_by_column impact on order

Row-by-row vs. column-by-column scanning produces different output orders.

Solution: Test both options to verify the correct order for your use case

Mistake 5: Expecting TOROW to filter automatically

TOROW flattens but doesn’t automatically remove criteria-based items.

Wrong: =TOROW(A1:D100) won’t filter by value

Correct: =TOROW(FILTER(A1:D100, criteria), 1) filters before flattening

Mistake 6: Nested TOROW when not needed

Multiple TOROW calls can complicate formulas unnecessarily.

Overcomplicated: =TOROW(TOROW(A1:D10))

Simple: =TOROW(A1:D10)

TOROW vs. Alternative Methods

Method	Pros	Cons
TOROW Formula	Simple syntax, handles any array shape, options to ignore blanks/errors, dynamic	Requires Microsoft 365, produces very wide output that needs space
Manual Copy-Paste + Transpose	Works in all Excel versions	Tedious, no automatic updates, error-prone
TRANSPOSE Function	Works in older Excel, familiar to users	Doesn’t ignore blanks/errors, less flexible for complex scenarios
CONCATENATE/TEXTJOIN	Works for text joining	Not true array flattening, limited to text, complex syntax for multi-range
Helper Columns + INDEX/SMALL	Granular control available	Clutters spreadsheet, difficult to maintain, many formulas needed

Availability and Compatibility

TOROW is available in Microsoft 365 for Windows, Mac, and Excel for the web. It is not available in perpetual versions like Excel 2019, 2016, or earlier. For older Excel versions, use TRANSPOSE, manual methods, or helper column strategies as alternatives.

Tips for Mastering TOROW

Always consider ignore parameter: Usually you’ll want ignore=1 to skip blanks, creating cleaner output.
Test scan direction: Verify whether row-by-row or column-by-column scanning produces the order you need.
Combine with filtering: Use FILTER before TOROW to process only relevant data before flattening.
Provide space for output: Ensure many empty columns to the right to avoid #SPILL! errors.
Use with WRAPROWS/WRAPCOLS: After flattening to a row, wrap back into your desired layout.
Remove duplicates first: Use UNIQUE before TOROW when deduplication is important.
Document your flatten logic: Explain why you’re converting to row format in formula comments.
Chain with other array functions: TOROW is most powerful when combined with SORT, FILTER, UNIQUE, and SEQUENCE.

Troubleshooting Guide

Error	Cause	Solution
#SPILL!	Not enough empty columns to the right for output	Move formula to a location with more columns available, or use in a cell with many empty columns to its right
#VALUE!	Invalid ignore or scan_by_column parameter	Verify ignore is 0-3 and scan_by_column is TRUE/FALSE or 0/1
#NAME?	Function not recognized	Verify Microsoft 365 subscription; update Excel to latest version
#REF!	Array reference is invalid or deleted	Check that the source range exists and is correctly referenced
Unexpected output order	scan_by_column setting doesn’t match expectations	Test both FALSE (row-by-row) and TRUE (column-by-column) to get desired order
Blanks or errors in output (unexpected)	ignore parameter not set correctly	Use ignore=1 to skip blanks, ignore=2 to skip errors, or ignore=3 for both

Performance Considerations

Very large arrays: Flattening very large matrices (100K+ cells) can impact performance. Test with realistic data volumes before deployment.
Wide output: TOROW creates horizontal arrays that can span hundreds or thousands of columns. Large output ranges consume memory and may slow rendering.
Nested operations: Combining TOROW with FILTER, SORT, UNIQUE, and other array functions can reduce performance on large datasets.
Dependent formulas: Many formulas referencing the TOROW output can compound performance impact.
Dashboard impact: Very wide spilled arrays on a dashboard can affect refresh time and responsiveness.

Advanced Scenarios

Dynamic data consolidation from multiple worksheets

=TOROW(
  HSTACK(
    TOROW(Sheet1!A:A, 1),
    TOROW(Sheet2!A:A, 1),
    TOROW(Sheet3!A:A, 1)
  ),
  1
)

Consolidates data from three sheets, flattens each, combines horizontally, then flattens again for linear output.

Quarterly to monthly conversion

=WRAPCOLS(
  TOROW(
    VSTACK(
      TOROW(Q1_Months, 1),
      TOROW(Q2_Months, 1),
      TOROW(Q3_Months, 1),
      TOROW(Q4_Months, 1)
    ),
    1
  ),
  12
)

Flattens quarterly data into one row, then wraps into 12 monthly columns.

Matrix transpose with selective flattening

=TOROW(
  TRANSPOSE(
    FILTER(
      Source_Matrix,
      Source_Matrix <> ""
    )
  ),
  1
)

Filters matrix for non-blank values, transposes, then flattens into a single row.

Create searchable product catalog from multi-column source

=TOROW(
  UNIQUE(
    FILTER(
      HSTACK(
        TOROW(Category_A_Products, 1),
        TOROW(Category_B_Products, 1),
        TOROW(Category_C_Products, 1)
      ),
      "" <> HSTACK(
        TOROW(Category_A_Products, 1),
        TOROW(Category_B_Products, 1),
        TOROW(Category_C_Products, 1)
      )
    )
  ),
  1
)

Consolidates products from three categories, removes blanks and duplicates, creates searchable list.

Complex data transformation pipeline

=TOROW(
  SORT(
    UNIQUE(
      FILTER(
        HSTACK(
          TOCOL(Dataset_A),
          TOCOL(Dataset_B)
        ),
        HSTACK(
          TOCOL(Dataset_A),
          TOCOL(Dataset_B)
        ) <> ""
      )
    ),
    1, 1
  ),
  1
)

Converts vertical datasets to columns, combines, filters blanks, removes duplicates, sorts alphabetically, and flattens to row.

Best Excel Tutorial