How to combine multiple columns into one column in Excel

Stack every column with TOCOL

The sheet is a summer camp's sign-up sheet: one column per week, with the children booked into each. The office wants one list of every booking to print name badges and check allergies. Week 2 has a gap where a booking was cancelled, and the weeks have different numbers of children.

TOCOL takes any block of cells and returns its values in a single column. The second argument says what to leave out (1 = blanks), and the third, TRUE, says to read column by column. The result spills down from the formula cell, so the cells below it must be empty or you get #SPILL!.

TOCOL, VSTACK, HSTACK and the other array-shaping functions are in Excel for Microsoft 365, Excel for the web and Excel 2024. Excel 2021 and older show #NAME?; the INDEX method further down works there.

Why the names come out shuffled: row by row vs column by column

Leave out the third argument and TOCOL reads the block the way you read a page: across row 2, then across row 3, and so on. Week 1, 2 and 3 values come out interleaved. That is fine for a plain list you will sort anyway, but it is the number one surprise when people expect "column A, then column B".

Add TRUE as the third argument whenever the order matters, for example when the columns are rounds, weeks or teams and you want them to stay together.

Blank cells become 0, and errors: the ignore argument

With no second argument, TOCOL keeps every cell. Empty cells do not stay empty: each one comes back as 0. Here that is three zeros for B4, A7 and B7, and they would sort to the top of a list and print as a badge named "0". The fix is the ignore argument: 0 keeps everything, 1 skips blanks, 2 skips errors, 3 skips both.

Two catches. First, a formula that returns "" is not blank, it is empty text, so ignore 1 keeps it. Column G is a badge lookup: G2 finds a name, G3 is #N/A because Kai Ross is not booked, and G4 returns "". =TOCOL(G2:G4,1) keeps all three; =TOCOL(G2:G4,3) drops the #N/A but still keeps the "". Second, choose your range so it does not include the header row, or "Week 1" becomes the first item of your list.

Lists of different lengths or on different sheets: VSTACK

VSTACK puts whole ranges one under another, in the order you list them. It is the tool when the lists are not side by side: a column on each sheet, or a list somewhere else on the page. For one list per sheet, the pattern is =VSTACK(TOCOL('Week 1'!A2:A100,1),TOCOL('Week 2'!A2:A100,1)), where the generous A2:A100 leaves room for new rows.

Why the TOCOL inside? VSTACK keeps empty cells and shows each one as 0, exactly like TOCOL with no ignore argument. Stack Week 1 and Week 2 directly and you get 12 rows with zeros in rows 6, 9 and 12. Wrapping each range in TOCOL(range,1) first throws out the blanks before they are stacked, which gives the 9 real bookings.

VSTACK also pads, it never trims. Stack a two-column block on a one-column list, like =VSTACK(A2:B3,E2:E3), and the missing second column shows #N/A. Stack ranges of the same width, or pick the column you need with CHOOSECOLS first.

One sorted list with no duplicates

Children booked for more than one week appear more than once in the stack: Liam Chen is in Week 1 and Week 2. For a badge list you want each child once. Wrap the stack in UNIQUE, and SORT the result. This version also adds the waitlist in column E, which is a separate list, so the two pieces are joined with VSTACK.

UNIQUE is not case-sensitive, but it does treat "Liam Chen" and "Liam Chen " (with a trailing space) as different people. If the names were typed by different people, put TRIM around the stack: =SORT(UNIQUE(TRIM(TOCOL(A2:C7,1)))).

Keep the column name next to each value (unpivot)

A stacked list loses which week each child is in. To keep it, stack the headers alongside the values. IF(A2:C7="",NA(),A1:C1) gives every filled cell its column's header and every blank cell #N/A, so TOCOL with ignore 3 drops exactly the same cells as the value column. HSTACK puts the two side by side.

Power Query does the same without formulas, and works in Excel 2016 and later:

1. Click inside the data and choose Data > From Table/Range. Confirm that the table has headers.
2. In the Power Query editor, select the Week 1, Week 2 and Week 3 columns (Ctrl+click the headers).
3. Choose Transform > Unpivot Columns. You get an Attribute column (the week) and a Value column (the name). Empty cells are dropped.
4. Choose Home > Close & Load. When the sign-ups change, use Data > Refresh All.

Excel 2019, 2016 and older: INDEX or copy and paste

Without TOCOL, an INDEX formula can walk down the columns. Put it in the first cell of the new list and fill it down. ROWS($A$1:A1) counts 1, 2, 3 as you fill; the 6 is the number of rows in the block (A2:C7 has 6). MOD picks the row within a column and INT moves to the next column every 6 rows.

Fill it down 18 rows (6 rows x 3 columns). Blank cells come back as 0, and the 19th row gives #REF! because there is no fourth column. Filter out the zeros afterwards, or paste values and delete them.

For a one-off job, copy and paste is quicker: select B2:B6, press Ctrl+C, click the first empty cell under column A's list and press Ctrl+V. Repeat for each column, then remove the blank rows.

Did you mean joining text from several columns into one cell?

If your columns are First name, Last name and City and you want "Ava Patel, Leeds" in one cell per row, you are joining text across a row, not stacking. Use =A2&" "&B2, or TEXTJOIN to join a whole row with a separator and skip blanks: =TEXTJOIN(", ",TRUE,A2:C2) gives "Ava Patel, Liam Chen, Noah Brooks" here. The guides on combining first and last names and CONCAT vs TEXTJOIN cover that in detail, including Flash Fill and spaces between words.