Combining columns in Excel

Factual Questions
1

I have an Excel table that has six columns with 10 rows. I want to have one column with 60 rows. All I can find about combining columns is about merging data from two columns into one column.
Can I merge columns without merging data?

2

I don’t think there is any way to do this automatically. Just cut and paste, and it will take 30 seconds.

3

This is kind of a non-answer but I’m pretty knowledgable about Excel and know of no built-in functionality that will do what you want. I can think of three approaches:

  1. Write some VBA. Too much trouble for a one-time shot with such a small about of data.

  2. Write formulas in the first column to refer to the data in the other columns, then do a copy-paste values and remove the extra columns. Also too much trouble for a one-shot deal.

  3. Move the cells manually. At 5 seconds per column this will take approximately half a minute. In fact, you could probably have done that faster than the time it took to post your question :slight_smile:

(I have very good luck asking questions at ozgrid.com but unfortunately they have now started a pay-to-play system charging $1 per question. But there are some people there who are full-time Excel consultants so you get good answers.)

4

You might find **Copy **-> **Paste Special **-> Transpose useful, although it would still take several steps.

5

I think the answers so far misunderstood the question. It was about a spreadsheet with 6x10 cells – to simplify, I’ll illustrate with 2x3 cells.

You start with:

A B
C D
E F

And you wat to end with:

A
C
E
B
D
F

In this case. just select the cells with B, D and F, then Cut those cells (with Ctrl-X), move the cursor to the cell under E, then Paste (with Ctrl-V).

With a larger block of cells, you’ll need to cut-and-paste more times. If the final order does not matter, or you are going to use Sort to get the right order, you could cut-and paste several columns at a time to spped things up.

6

That worked thanks so much.

7

Better late than never, here’s a macro:



Public Sub StackColumns()

Dim rng As Range, col As Range
Set rng = ActiveCell.CurrentRegion

For Each col In rng.Offset(, 1).Columns
    col.Cut
    rng.End(xlDown).Offset(1).Select
    ActiveSheet.Paste
Next
    
End Sub