Back to articles
FormulasIntermediate
2025-08-068 min read

Turn Multiple Excel Columns into One List with TOCOL

Functions in this article

Jump to the reference pages for the Excel functions used below.

Browse library
Table of Contents

To turn several Excel columns into one list, enter =TOCOL(B4:D5) in an empty cell. With 11, 12, 13 in the first source row and 21, 22, 23 in the second, the result runs down one column: 11, 12, 13, 21, 22, 23. Each source value gets its own cell.

The choice to make is which direction Excel should read your grid. You can read across each row first, as above, or finish one source column before moving to the next.

Microsoft lists TOCOL and TOROW for Excel for Microsoft 365 and Excel 2024, on both Windows and Mac. See its TOCOL documentation for the supported versions and arguments.

Put the grid into one column

Download the example workbook to compare both scan directions on List and the different empty-looking entries on Blanks.

To build the example yourself, create a sheet named List and paste these tab-separated values into B4:

11	12	13
21	22	23
Text

The source fills B4:D5. Leave F4:F9 empty, select F4, and enter:

=TOCOL(B4:D5)
Formula

The expected result is six cells, from F4 to F9:

11
12
13
21
22
23
Example

Excel reads B4 → C4 → D4, then B5 → C5 → D5. It puts those values into the output in that order. You enter the formula once, in F4; the neighboring results belong to that formula. This is called a spilled array.

Keep headings outside the source reference unless you want them included as list entries. TOCOL reads the cells you give it; it doesn't identify which entries are headings.

If you want to swap rows and columns while keeping a two-dimensional report, use Paste Transpose or a TRANSPOSE formula. Flattening a grid also doesn't join its values into a single text cell, or preserve source field names beside each value as an unpivoted table would.

Read down each source column first

Suppose each source column is a separate list and you want all of column B before column C. Leave H4:H9 empty and enter this in H4:

=TOCOL(B4:D5,0,TRUE)
Formula

Excel worksheet showing the six source values in B4:D5, the row-first list in F4:F9, and the column-first list in H4:H9 with H4 selected
The same grid produces two list orders. H4 is selected, with the column-first TOCOL formula visible in the formula bar.

The expected result changes to:

11
21
12
22
13
23
Example

This time Excel reads B4 → B5, then C4 → C5, then D4 → D5. Compare F5 with H5: F5 is 12, while H5 is 21. That second entry is a quick way to check which scan order you're using.

The final argument, TRUE, selects this column-first scan. Leaving it out, or using FALSE, reads across rows. The middle argument, 0, keeps all source entries. It occupies the second argument position so that TRUE can go in the third.

Decide what counts as an empty entry

A cell can look empty for several different reasons. Before changing the formula, separate these cases:

Source contentWhat it means for your list
An untouched cellA truly blank source cell; the default TOCOL formula returns a zero in its position.
The number 0An actual numeric value, retained when you ignore blanks.
A formula returning ""Empty text with zero characters, retained when you ignore blanks. This is a formula result, not an unused source cell.
One typed spaceText containing one character, also retained when you ignore blanks. It may look empty, but ignoring blank cells isn't a whitespace cleanup step.

To see why that distinction matters, create a separate sheet named Blanks. Enter 11 in B4, leave C4 untouched, enter 0 in D4, and enter 21 in B5. In C5, enter:

=""
Formula

In D5, type one space and press Enter. You now have four different cases in the same small range. Don't type the word “blank” into C4; that would turn it into a text entry.

In F4, start with the default formula:

=TOCOL(B4:D5)
Formula

The truly blank C4 produces a zero, while D4 supplies its own numeric zero. Those two zeros have different origins. Hiding every zero would also hide the valid source value.

To omit truly blank cells, enter this in H4:

=TOCOL(B4:D5,1)
Formula

Here are the results from the example in Excel for Mac. Empty text and one space below describe the invisible entries; Excel doesn't display those labels in the output.

Position in listKeep everything: F4:F9Ignore blanks: H4:H8
1F4: 11H4: 11
2F5: 0, from blank C4H5: 0, from numeric D4
3F6: 0, from numeric D4H6: 21
4F7: 21H7: empty text
5F8: empty textH8: one space
6F9: one spaceNo sixth entry

With 1, only the untouched C4 is skipped. The result still contains the numeric zero, the empty text from C5, and the space from D5. F8 and H7 contain text of length zero; F9 and H8 contain text of length one. Those last entries may look alike on the sheet, but they aren't the same value.

A space is easy to enter without noticing. Inspect the source before deciding that a blank-looking output is an error; changing TOCOL's blank setting won't remove that space.

Choose whether to keep errors

The second argument controls both blank and error handling:

Second argumentEntries omitted
0, or omittedNone
1Blank cells
2Error values
3Blank cells and error values

To compare all four settings, make a copy of the Blanks sheet and name it Error QA. Leave C4 untouched, D4 as numeric zero, C5 as empty text, and D5 as one space. Replace B5, which currently contains 21, with:

=1/0
Formula

B5 now shows #DIV/0!. Leave K20:K25 empty and enter this in K20:

=TOCOL(B4:D5,0)
Formula

The result has six entries, including the error. Change the second argument to 1 to skip the blank source cell while keeping the error. Then replace the K20 formula with this to omit the error while keeping the blank's position:

=TOCOL(B4:D5,2)
Formula

Finally, replace it with this to omit both the blank and the error:

=TOCOL(B4:D5,3)
Formula

Compare each result with these sequences, listed from top to bottom. As before, empty text and one space describe entries that look empty in the cells.

Second argumentNumber of entriesResult sequence
0611, 0, 0, #DIV/0!, empty text, one space
1511, 0, #DIV/0!, empty text, one space
2511, 0, 0, empty text, one space
3411, 0, empty text, one space

Only 2 and 3 skip the error. Neither removes the empty text or the space. With 3, the remaining zero is the real numeric value from D4.

Check source errors before choosing either option. Omitting this division error makes the list shorter; it doesn't repair B5. Keep the default behavior when errors need to remain visible for correction. To reset your test sheet, put 21 back in B5. The downloaded workbook keeps the original, error-free example; Error QA is a separate sheet you create for this comparison.

Return one row instead

On the List sheet, leave B13:G13 empty and enter this in B13:

=TOROW(B4:D5)
Formula

The expected horizontal result is:

11  12  13  21  22  23
Example

TOROW changes the output orientation. Its default still reads across the source rows. To read down the source columns while returning one horizontal row, replace the formula in B13 with:

=TOROW(B4:D5,0,TRUE)
Formula

The expected sequence across B13:G13 is now 11, 21, 12, 22, 13, 23. Microsoft's TOROW documentation describes the same blank, error, and scan-order arguments used by TOCOL.

Flattening preserves repeated entries. If your next task is to keep only distinct values, see the UNIQUE function reference.

Enjoyed this guide?

Join our newsletter to get the latest Excel tips delivered to your inbox.

You can unsubscribe anytime. See our Privacy Policy.