Combine all columns into one in Excel

When analyzing data in Excel, you often need to collect the values from all columns of a table into one long list: for statistics, a summary report, removing duplicates or preparing data for other systems. Let’s look at the main ways.

Copying the columns manually

The simplest but very inefficient method is copying each column one under another. For a table with 10 columns and 1,000 rows, that’s 10 copy operations and a lot of time, plus the risk of pasting over existing data.

Formula to combine columns into one

In Excel 365, the TOCOL function does exactly this. The second argument 1 skips empty cells, and the third argument TRUE reads the range column by column:

=TOCOL(A1:D100,1,TRUE)

Without TRUE, the values are read row by row: A1, B1, C1, D1, A2 and so on. In older versions, a classic formula with INDEX does the job for a fixed range of 100 rows and 4 columns. Put it in F1 and fill it down 400 rows:

=INDEX($A$1:$D$100,MOD(ROW()-1,100)+1,INT((ROW()-1)/100)+1)

It takes the rows of the first column, then the second, and so on. Empty cells return 0, so you’ll need to filter them out. As you can see, with more columns or different column lengths, this gets complicated fast.

VBA macro

A more efficient but more technical solution. The macro goes through all filled columns of the active sheet and writes their non-empty values into one column, leaving an empty column after the table:

Sub CombineColumnsIntoOne()
    Dim ws As Worksheet, lastCol As Long, lastRow As Long
    Dim i As Long, j As Long, outRow As Long
    Set ws = ActiveSheet
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    outRow = 1
    For i = 1 To lastCol
        lastRow = ws.Cells(ws.Rows.Count, i).End(xlUp).Row
        For j = 1 To lastRow
            If ws.Cells(j, i).Value <> "" Then
                ws.Cells(outRow, lastCol + 2).Value = ws.Cells(j, i).Value
                outRow = outRow + 1
            End If
        Next j
    Next i
End Sub

Problems with the traditional approaches

• complexity: formulas and VBA require technical knowledge;
• errors: it’s easy to make a mistake in a complex formula;
• time: manual copying takes a lot of it;
• maintenance: the code has to be stored somewhere, for example in the Personal Macro Workbook;
• security: VBA macros may be blocked by company policies.

Free solution in !SEMTools

The free “Combine all columns into one” procedure in !SEMTools solves all these problems:

• go to the sheet you want to process;
• run the procedure from the !SEMTools menu;
• it detects all filled columns automatically;
• the result is placed at the end of the sheet as a separate column. Empty cells are kept, just in case :)

No code to understand or store, no macros to enable, thousands of rows in seconds.

Option 2: combine only the selected columns

In 2025, I was asked to add an option that combines not all columns, but only the selected ones. Done! The button is in the same menu:

Summary

I hope you found what you were looking for on this page and were able to combine your columns into one. In Excel 365, TOCOL does it in one formula; in older versions, use INDEX or a macro, or the free !SEMTools procedure that does it in two clicks. You may also be interested in how to combine all sheets into one.