Blue Flower

Display All Data in One Column

Returns a list of all data from one column but no empty cells

One of the common problems in Excel is to display a list of all data or values from one, two, or more columns. Of course, our desire is that the list is no empty cells. In this first tutorial example, review the Excel formulas that we use to copy data from a column without empty cells.

If you've ever had a need to show a list of all data without empty cells and you did not know, look at this collection of Excel formulas. All formulas return the list of data from the 'A' column. In the picture below you can see the data in the 'A' column. There are mixed data text and numbers or values. Our goal is to show a list of all data without empty cells.

How to display a list of all data from a column without empty cells

In the picture above, notice the columns that show the results.

The formula in 'C' column is the following. (this formula is applicable in Excel 2003 and later)
=IF(ISERROR(INDEX(A:A;SMALL(INDEX(NOT(ISBLANK($A$2:$A$20))*ROW($A$2:$A$20);0);COUNTBLANK($A$2:$A$20)+ROW(A1))));"";INDEX(A:A;SMALL(INDEX(NOT(ISBLANK($A$2:$A$20))*ROW($A$2:$A$20);0);COUNTBLANK($A$2:$A$20)+ROW(A1))))

The formula in 'E2' cell is the following:
=IFERROR(INDEX(A:A;SMALL(INDEX(NOT(ISBLANK($A$2:$A$20))*ROW($A$2:$A$20);0);COUNTBLANK($A$2:$A$20)+ROW(A1)));"")

ARRAY Formula (CSE) in 'G2' cell is the following:
=IFERROR(INDEX($A$2:$A$20;SMALL(IF(ISBLANK($A$2:$A$20)+ISERROR($A$2:$A$20);"";ROW($A$2:$A$20)-MIN(ROW($A$2:$A$20))+1);ROW(1:1)));"")

ARRAY Formula (CSE) in 'I2' cell is the following:
=IF(ROW()-ROW($I$2:$I$23)+1>ROWS($A$2:$A$20)-COUNTBLANK($A$2:$A$20);"";INDIRECT(ADDRESS( SMALL((IF($A$2:$A$20<>"";ROW($A$2:$A$20);ROW()+ROWS($A$2:$A$20)));ROW()-ROW($I$2:$I$23)+1);COLUMN($A$2:$A$20);4)))

ARRAY Formula (CSE) in the 'K2' cell is as follows: (This CSE formula returns unique data from the 'A' column without empty cells, so those data that are repeated in 'A' columns are displayed only once)
=IFERROR(INDEX($A$2:$A$20;MATCH(0;IF(LEN($A$2:$A$20);COUNTIF(K$1:K1;$A$2:$A$20);1);0));"")

How to merge data from two columns into one column

If you want to merge data from two columns into one column or so that the list does not have empty cells then study this example below. In the picture below, see the two columns that contain the data. Our goal is to copy data from two columns to one column, but so as not to show empty cells.

In the 'A and B' columns, you can see the data that is not placed in the same number of rows. To copy data from 'A and B' columns in the 'F' column will use the 'helper' column in which we have the ordinal numbers from 1 onwards.

Copy data from two columns to one column without empty cells

In the picture above the formula in the 'F' column is the following: (note that this formula copies the first data from the 'A' column and then continues copying the data from the 'B' column. Note, there should not be empty cells between the first and the last data in the source columns).
=IFERROR(IF($E$2:$E$11<=ROWS($A$2:$A$5);INDEX($A$2:$A$5;$E$2:$E$11);INDEX($B$2:$B$7;$E$2:$E$11-ROWS($A$2:$A$5)));"")

ARRAY formula (CSE) in 'H2' cell does the same job as the previous but does not use the 'helper' column.
=IFERROR(INDEX($A$2:$A$5;MATCH(0;IF(LEN($A$2:$A$5);COUNTIF(H$1:H1;$A$2:$A$5);1);0));IFERROR(INDEX($B$2:$B$7;MATCH(0;IF( LEN($B$2:$B$7); COUNTIF(H$1:H1;$B$2:$B$7);1);0));""))

ARRAY formula (CSE) in 'K2' cell, return a list of all data as well as the two previous formulas, but there should not be empty cells between the first and the last data in the source column.
=IF(ROW(A1)<=COUNTA($A$2:$A$10);INDEX($A$2:$A$10;ROW(A1));IF(ROW(A1)>COUNTA($A$2:$B$10);"";INDEX($B$2:$B$10;ROW(A1)-COUNTA($A$2:$A$10))))

Copy data from two columns to one column using dynamic cell ranges

The previous example above uses the absolute addresses in formulas. If you need to add data to the source columns then use dynamic cell ranges. So if you want to have a dynamic range of cells then convert cell ranges to Table1 and Table2 and use a defined name instead of absolute addresses in the formula.

Copy data from two columns in single column without blank cells

In the image above, dynamic cell ranges were used. The two source 'A' and 'B' columns are converted to Table1 and Table2, as well as the auxiliary column that was converted to Table3.

The formula in 'F2' cell is the following:
=IFERROR(IF(Table3[helper]<=ROWS(Table1[Names1]);INDEX(Table1[Names1];Table3[helper]);INDEX(Table2[Names2];Table3[helper]-ROWS(Table1[Names1])));"")

The formula in 'H2' cell is the following:
=IFERROR(INDEX(Table1[Names1];MATCH(0;IF(LEN(Table1[Names1]);COUNTIF(H$1:H1;Table1[Names1]);1);0));IFERROR(INDEX(Table2[Names2];MATCH(0;IF(LEN(Table2[Names2]);COUNTIF(H$1:H1;Table2[Names2]);1);0));""))

Display a list of all the data from two columns into a single column with a zigzag sequence

If you need to display a list of all data from two columns, but the sequence of retrieved data is a zigzag arrangement in a single column, then review this formula below. So this formula returns the data from two columns, but first returns the data from the first column, then from the second column and again from the first column and so on.

ARRAY (CSE) formula in 'D2' cell in the picture below is as follows:
=INDEX($A$2:$B$7;INT((ROWS(D$3:D3)-1)/2)+1;MOD(ROWS(D$3:D3)-1;2)+1)

How to copy a list of data from two columns into one with a zigzag layout

How to copy data from three columns to one column

When we have the situation of copying or displaying data from three columns in one column, then you can study the example below. In the picture below, you can see multiple columns that contain data (text and values). Our goal is to display all data in one column.

The formula in 'E1' cell is the following:
=IF(ROW()<=COUNTA(A:A);INDEX(A:A;ROW());IF(ROW()<=COUNTA(A:B);INDEX(B:B;ROW()-COUNTA(A:A));IF(ROW()>COUNTA(A:C);"";INDEX(C:C;ROW()-COUNTA(A:B)))))

Display all data from three columns in one column

Display list from multiple columns to one column

In the another case, we have a different formula that will ignore empty cells and return all data from multiple columns to one column.

The formula in 'E2' cell in the picture below is as follows:
=IFERROR(INDEX($A$2:$A$17;MATCH(0;IF(LEN($A$2:$A$17);COUNTIF(E$1:E1;$A$2:$A$17);1);0));IFERROR(INDEX($B$2:$B$17;MATCH(0;IF(LEN($B$2:$B$17);COUNTIF(E$1:E1;$B$2:$B$17);1);0));IFERROR(INDEX($C$2:$C$17;MATCH(0;IF(LEN($C$2:$C$17);COUNTIF(E$1:E1;$C$2:$C$17);1);0));"")))

How to copy all data from multiple columns to one column without empty cells

Of course, you can always convert the cells of the source columns in Table1, Table2, and Table3 to have a variable range if you add data to the source columns.

Attached file in to this Excel tutorial, you have the option to Download Excel example file. (Copy all the data from multiple columns to one column) that contains the Workbook I used to create this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.