5 formulas that combine columns in Excel (2023)

5 formulas that combine columns in Excel (1)

There are a variety of different ways to combine columns in Excel, and I am going to show you five different formulas that you can use to combine multiple columns into one.Three of these formulas will combine columns horizontally, and two of them will combine columns vertically.

Directly below are quick instructions for combining columns, but also look further below for detailed examples, as well as additional ways of combining columns.

To combine columns horizontally in Excel, follow these steps:

  1. Type an equals sign and then a column reference, such as =A3:A12 to specify the first column to combine
  2. Type an ampersand (&)
  3. Type the address of the other column that you want to combine with, such as B3:B12
  4. Press enter on the keyboard. The full formula will look like this: =A3:A12&B3:B12 (If you are using an older version of Excel, you will need to hold "Ctrl" and "Shift" on the keyboard before pressing "Enter". If you do this, curly brackets will appear before and after your formula, like this: {=A3:A12&B3:B12}
  5. Optional: If you want, you can add special characters between the combined columns by using additional ampersands, and any character that you want between quotation marks, like this =A3:A12&" "&B3:B12

(See detailed instructions below for how to add spaces or special characters between the columns)

To combine columns vertically in Excel, follow these steps:

  1. Method 1: Enter the following formula in a blank cell / column, to combine columns vertically: =IF(A3<>"",A3,INDIRECT("B"&ROW()-COUNTIF(A$3:A$1000,"<>")))
  2. Method 2: Enter the following formula in a blank cell / column, to combine columns vertically while alternating between rows: =INDEX($A$2:$B$1000,ROW()/2,MOD(ROW(),2)+1)
  3. Use the fill handle or copy and paste the formula downwards

Here are the formulas that will combine columns in Excel:

Horizontal column combination formulas

  • =A3:A12&" "&B3:B12 Legacy / CSE Version: {=A3:A12&" "&B3:B12}
  • =CONCAT(A3," ",B3)
  • =CONCATENATE(A3:A12," ",B3:B12) Legacy / CSE Version: {=CONCATENATE(A3:A12," ",B3:B12)}

Vertical column combination formulas

  • =IF(A3<>"",A3,INDIRECT("B"&ROW()-COUNTIF(A$3:A$1000,"<>")))
  • =INDEX($A$2:$B$1000,ROW()/2,MOD(ROW(),2)+1)

This lesson will teach you how to combine columns in Excel, but check out this article if you want to learn how to combine columns in Google Sheets.

Using arrays in Excel

Notice that some of the formulas we are using in this lesson use what are called "arrays". An array formula is entered into a single cell but applies the formula to a range of cells. If you are using an older version of Excel, when using "arrays", you will need to hold "Ctrl" and "Shift" on the keyboard, and then press "Enter" when entering your array formulas. This is called a CSE (Control / Shift / Enter) formula, and is also sometimes called a "Legacy" array, as opposed to newer "Dynamic" arrays that do not require this extra step.

After doing the Ctrl / Shift / Enter method, your formula will be wrapped in curly brackets { }. Notice that the two formulas below will perform the same task, but were simply entered differently, the bottom formula being the "CSE" formula.

=A3:A10&B3:B10

{=A3:A10&B3:B10}

Combine columns in Excel (Horizontal)

First I am going to show you how to combine columns in Excel horizontally.

If you have two columns that you would like to combine the contents of, where the values of the cells in each row are to be combined together horizontally, then there are a few simple ways of doing this:

  • The first method (using the "&" operator / ampersand), will allow you to not only combine/merge two columns horizontally, but will also allow you to separate the contents with specified values/ strings of text, and will also allow you to combine more than two columns if you wish
  • The second method (using the CONCATENATE function), will allow you to combine the contents of two or more columns horizontally. The CONCATENATE function is almost identical to CONCAT, and I will mention more about this function below.

Both of these methods can be used to combine individual cells, without having to use the ARRAYFORMULA function.

(Video) Functions/Formulas in Excel 5 - Combine Text and Formulas in a Cell

Using the AND operator / ampersand (&) to combine columns

First I will show you the most common and functional way of combining columns horizontally in Excel. In this example we will combine first and last names into a single column.

By using the "&" operator you will be able to combine multiple columns in Excel, and you will also be able to specify values and strings of text that you would like to attach to the column combination.

To combine the data from individual cells in Excel (Such as first & last name), follow these steps:

  1. Type an equals sign, and then type the address for the first cell that you want to combine with, such as A3
  2. Type an ampersand (&)
  3. Type the address of the another cell that you want to combine with, such as B3
  4. Press enter on the keyboard. The full formula will look like this: =A3&B3
  5. Copy / fill down the formula to use it on the entire column, or turn the cell references into range references to turn the formula into an array

In this first example we have a list of first names in one column, and last names in another column… and we want to combine these columns horizontally so that in each row the full combined name is displayed as a single text string, in a single column.

We will also put a separating space between the contents that are combined in this example. This is accomplished by using quotation marks with a space between them i.e. (" ") in the array formula (shown below).

The task: Horizontally combine the "first name" column with the "size" column, and put a space between the contents… so that the full name is displayed in a single column.

(Where the contents of the cells in each row are combined into a single text string separated by a space)

The logic: Horizontally combine the contents of the cells in the range A3:A12, with the contents of the cells from the range B3:B12, and add a space between the text/contents from the combined columns

The formula: The formula below, is entered in the blue cell (D3), for this example

=A3:A12&" "&B3:B12

{=A3:A12&" "&B3:B12} (Legacy / CSE Version)

5 formulas that combine columns in Excel (2)

Remember to hold Ctrl + Shift + Enter to make an array formula, with older versions of Excel.

Combining more than 2 columns horizontally in Excel

If you want to combine more than 2 columns horizontally in Excel, you can do this with the "&" operator, which is also called an "ampersand".

For example, if you wanted to combine columns A, B and C, horizontally (with spaces between), then you could use the formula below.

=A3:A12&" "&B3:B12&" "&C3:C12

Using CONCAT or CONCATENATE to merge columns in Excel

In this example we are going to do the same thing as in the example above, but we are going to use different data. We have a list of clothing inventory items in one column, and the clothing size for that item listed in another column… and we want to combine these columns horizontally so that in each row the clothing item and size are displayed as a single text string, in a single column.

We will be using the CONCATENATE function, which is compatible with arrays, making it easy to combine an entire column with a single formula.

However, note that you can also use the CONCAT function to combine cells and even place spaces / characters between those columns, just like we did with the "&" symbol… but the CONCAT function does not work with arrays, and so if you use this to combine columns you will need to use one formula in each cell.

To combine the data from cells by using the CONCATENATE in Excel, follow these steps:

  1. Type =CONCATENATE( to begin your formula
  2. Type the address of the first cell that you want to combine with, such as A5
  3. Type a comma, and then type the address of the next cell that you want to combine with, such as B5
  4. Press enter on the keyboard. The full formula will look like this: =CONCATENATE(A5,B5)
  5. Copy / fill down the formula to use it on the entire column, or change the cell references into range references to turn the formula into an array

To combine the data from cells with the CONCAT formula in Excel, follow these steps:

(Video) Combining Data From Multiple Cells in Excel

  1. Type =CONCAT( to begin your formula
  2. Type the address of the first cell that you want to combine with, such as A2
  3. Type a comma, and then type the address of the next cell that you want to combine with, such as B2
  4. Press enter on the keyboard. The full formula will look like this: =CONCAT(A3,B3)
  5. Copy / fill down the formula to use it on the entire column

See the example / instructions above for how to add spaces / special characters between the columns.

The task: Horizontally combine the column that shows clothing items with the column that shows clothing sizes… so that the clothing item and size are displayed in a single column.

(Where the contents of the cells in each row are combined into a single text string)

The logic: Combine the range A3:B, and the range B3:B into a single column, so that the contents from the cells are horizontally merged into a single text string

The formula: The formula below, is entered in the blue cell (D3), for this example

=CONCATENATE(A3:A12," ",B3:B12)

{=CONCATENATE(A3:A12," ",B3:B12)} (Legacy / CSE Version)

The formulas above are the array version, but in the image you can see the basic version of the formula with one formula per cell.

5 formulas that combine columns in Excel (3)

Notice that the CONCAT function (Shown Below) does the same thing as the CONCATENATE function (Shown Above)

=CONCAT(A3," ",B3)

5 formulas that combine columns in Excel (4)

Combine columns in Excel (Vertical)

Now I am going to show you how to combine columns in Excel vertically, or in other words "stack columns".

There are 2 different ways to combine columns in Excel vertically by using formulas, depending on how you would like the formula to operate:

  • The first method (using the INDIRECT, ROW, and COUNTIF functions), will stack the column ranges that are specified on top of each other exactly as is
  • The second method (using the INDEX, ROW, and MOD functions with an array), will combine the contents from individual columns into a single column, but will alternate between columns for each row

Formula for combining (stacking) columns in Excel:

=IF(A3<>"",A3,INDIRECT("B"&ROW()-COUNTIF(A$3:A$99,"<>")))

Formula for combining columns, while alternating back and forth between columns in Excel:

=INDEX($A$2:$B$99,ROW()/2,MOD(ROW(),2)+1)

* Note that both of these formulas must be copied / filled into the cells for the entire column (With one formula per cell).

Combining columns vertically while stacking one column on top of another column

In this example we have two different lists of inventory that we want to combine into one. Let's say that two different employees counted clothing items in separate parts of your store, and that you want to combine the lists that they return to you into one list/column.

By using the INDIRECT function, the ROW function, and the COUNTIF function, we can create a formula that will stack the contents from one column, on top of the contents of another column. With the example / formula shown below, the contents of one entire column will be listed with the formula, before the contents of the second column are listed below it (As opposed to the alternating column method in the next example).

The task: Combine two lists of clothing items into a single list of unique inventory items… or in other words, make a single list that shows one of each item that is contained in inventory, by combining two lists

(Video) Combine Multiple Columns in Excel | Formula to Merge Two or More Columns | Stack Multiple Columns

The logic: Combine the range A3:A10 and the range B3:B10 vertically into a single column

The formula: The formula below, is entered in the blue cell (D3), for this example

=IF(A3<>"",A3,INDIRECT("B"&ROW()-COUNTIF(A$3:A$1000,"<>")))

5 formulas that combine columns in Excel (5)

Make sure that you place your formula in the correct row, according to the beginning rows of the references in the formula (If the formula's references begin at row 3, make sure that your first formula is in row 3.

Note in the example image how the formula in cell D3 has been copied into the cells below it. Remember to do this in your sheet as well.

Combining columns vertically while alternating from column to column for each row

In this example we are going to combine the same inventory lists from the previous example, vertically into a single column, but this time we are going to use a different formula that alternates between columns when listing the combined column data (As opposed to stacking one entire column on top of another entire column).

To do this we will use the INDEX function, the ROW function, and the MOD function.

The task: Combine two lists of clothing items into a single list, and show all items found

The logic: Combine the contents in range A3:A10 and the contents in range B3:B10 vertically into a single column

The formula: The formula below, is entered in the blue cell (D3), for this example

=INDEX($A$2:$B$1000,ROW()/2,MOD(ROW(),2)+1)

5 formulas that combine columns in Excel (6)

Like the formula in the previous example, make sure that your formula is placed in the correct row, according to the beginning rows of your formula references. However, with this particular formula, if your data begins on an odd row, such as row 3, your formula reference will need to begin on the previous / even row to make sure that all of the data is captured (As shown in the example image).

Note in the example image how the formula in cell D3 was copied into the cells below. Remember to do this in your sheet as well.

How to combine columns from separate sheets

You may find situations that require you to combine columns that are from separate tabs in Excel.

This can be done by simply referring to a certain tab name when specifying the ranges in the formula. So where you would normally set a range like "A1:C", when referencing another sheet while combining columns you will specify the tab name, by adding the tab name and an exclamation mark before listing the column and row for the range, like "TabName!A1:C"

However when the tab name has a space in it, you will need to use an apostrophe before and after typing the tab name, like 'Tab Name'!A1:C.

Here is an example of how to combine columns from multiple tabs in Excel, where there are two lists in different tabs, to be combined into a single list on a completely separate tab.

Let's say that you have a list of clothing items on one tab, another list of clothing items on a separate tab, and that you want to display a combined list of unique clothing items that are in inventory, on a completely different tab (meaning that there will be no duplicates).

*Note that you can reference specified tabs with any of the formulas that you learned in this article, but for this example we are using an array with the ampersand (&) to demonstrate how to combine columns from different tabs.

The task: Combine the list of clothing items on the tab labeled "List 1", with the list of clothing items on the tab labeled "List 2" and show a combined list of clothing items, on completely separate tab

(Video) How to merge multiple columns into a single column using Microsoft Excel

The logic: Combine the range 'List 1'!A3:A12, with the range 'List 2'!A3:A12

The formula: The formula below, is entered in the blue cell (A3), for this example

='List 1′!A3:A12&" "&'List 2′!B3:B12

Here are two different lists of clothing items, which are held on two separate tabs labeled, "List 1" and "List 2"

5 formulas that combine columns in Excel (7)
5 formulas that combine columns in Excel (8)

And here is a combined list of clothing items, where the formula and combined data are held on a separate tab

5 formulas that combine columns in Excel (9)

Pop Quiz: Test your knowledge

Answer the questions below about combining columns in Excel to refine your knowledge! Scroll to the very bottom to find the answers to the quiz.

Horizontal column combination formulas

=A3:A12&" "&B3:B12

=CONCAT(A3," ",B3)

=CONCATENATE(A3:A12," ",B3:B12)

Vertical column combination formulas

=IF(A3<>"",A3,INDIRECT("B"&ROW()-COUNTIF(A$3:A$1000,"<>")))

=INDEX($A$2:$B$1000,ROW()/2,MOD(ROW(),2)+1)

Question #1

Which of the following formulas will allow you to combine columns vertically? (Select all that apply)

  1. =A3:A100&" "&B3:B100
  2. =CONCAT(A5," ",B5)
  3. =IF(A5<>"",A5,INDIRECT("B"&ROW()-COUNTIF(A$5:A$1000,"<>")))
  4. =CONCATENATE(A3:A100," ",B3:B100)
  5. =INDEX($A$5:$B$1000,ROW()/2,MOD(ROW(),2)+1)

Question #2

Which of the following formulas will put a space between the contents that are horizontally combined?

  1. =CONCATENATE(A3:A100,B3:B100)
  2. =A3:A100&" "&B3:B100

Question #3

True or False: For older versions of Excel, you must hold Ctrl + Shift + Enter on the keyboard before entering an array formula

  1. True
  2. False

Question #4

Which of the following formulas will combine columns horizontally?

  1. =IF(A5<>"",A5,INDIRECT("B"&ROW()-COUNTIF(A$5:A$1000,"<>")))
  2. =CONCATENATE(A3:A100," ",B3:B100)

Question #5

Which of the following formulas will stack / combine columns WITHOUT alternating between columns

  • =IF(A5<>"",A5,INDIRECT("B"&ROW()-COUNTIF(A$5:A$1000,"<>")))
  • =INDEX($A$5:$B$1000,ROW()/2,MOD(ROW(),2)+1)

Answers to the questions above:

Question 1: 3, 5

Question 2: 2

Question 3: 1

Question 4: 2

(Video) Concatenate Excel Tutorial

Question 5: 1

FAQs

How do I combine 5 columns in Excel? ›

How to concatenate (combine) multiple columns into one field in Excel
  1. Use the CONCATENATE function in column D: =CONCATENATE(A1,B1,C1).
  2. In the menu bar, select Insert, Function. ...
  3. Enter A1 in the text1 field, B1 in the text2 field, and C1 in the text3 field.
  4. Click OK. ...
  5. Copy and paste for as many records as needed.

What is the formula to combine two columns in Excel? ›

Combine data with the Ampersand symbol (&)

Type = and select the first cell you want to combine. Type & and use quotation marks with a space enclosed. Select the next cell you want to combine and press enter. An example formula might be =A2&" "&B2.

How do you do 5 Formulas in Excel? ›

Type =5+2*3 in another cell and press Enter or Return. Excel multiplies the last two numbers and adds the first number to the result.
...
Examples.
Data
5
FormulaDescriptionResult
=A2+A3Adds the values in cells A1 and A2=A2+A3
=A2-A3Subtracts the value in cell A2 from the value in A1=A2-A3
10 more rows

What are the 5 functions in Excel? ›

The most frequently used functions in Excel are:
  • AutoSum;
  • IF function;
  • LOOKUP function;
  • VLOOKUP function;
  • HLOOKUP function;
  • MATCH function;
  • CHOOSE function;
  • DATE function;

How will you merge 5 columns into one in a table give example? ›

It can be done by using the rowspan and colspan attribute in HTML. The rowspan is used to merge or combine the number of cells in a row whereas the colspan is used to merge column cells in a table. Example 1: In this example, we will merge two table row and make a single row.

How do you combine 3 columns? ›

You would click inside a cell in the target “combined column” where you wish to merge data. You can then type the array formula =CONCATENATE(AA, BB), but replace the placeholder single cell references with all the cells that you want to combine in the destination column.

How can I combine columns in Excel? ›

2. How to Combine Excel Columns With the CONCAT Function
  1. Click the cell where you want the combined data to go.
  2. Type =CONCAT(
  3. Click the first cell you want to combine.
  4. Type ,
  5. Click the second cell you want to combine.
  6. Type )
  7. Press the Enter key.

How do I combine all columns in Excel? ›

Use the CONCATENATE function to merge multiple columns in Excel
  1. Insert the =CONCATENATE function as laid out in the instructions above.
  2. Type in the references of the cells you want to combine, separating each reference with ,", ", (e.g. B2,", ",C2,", ",D2). This will create spaces between each value.
  3. Press Enter.
10 Jan 2022

How do I combine two columns in a list? ›

How do I combine two columns in a list? Select the cell where you want to put the combined data. Type = and select the first cell you want to combine. Type & and use quotation marks with a space enclosed.

What are the 7 basic Excel formulas? ›

  • Overview of formulas in Excel. Article.
  • XLOOKUP. Article.
  • VLOOKUP. Article.
  • SUM function. Article.
  • COUNTIF function. Article.
  • IF function. Article.
  • IFS. Article.
  • SUMIF. Article.

What are the 10 Excel functions? ›

Read on below for a full overview of each function of MS Excel.
  • Table formatting. What it does: transforms your data into an interactive database. ...
  • Pivot tables. What it does: summarizes data and finds unique values. ...
  • Charting. ...
  • COUNTIFS. ...
  • SUMIFS. ...
  • IF Statements. ...
  • CONCATENATE. ...
  • VLOOKUP.
5 Jul 2018

What are examples of Excel formulas? ›

In Excel, a formula is an expression that operates on values in a range of cells or a cell. For example, =A1+A2+A3, which finds the sum of the range of values from cell A1 to cell A3.

What are the best Excel formulas? ›

Advanced Excel formula and functions
  1. VLOOKUP. The function is used to look up for a piece of information in a large segment of data and pull that data to your newly formed table. ...
  2. Sum Function. ...
  3. MAX MIN function. ...
  4. IF Function. ...
  5. SUMIF Function. ...
  6. COUNTIF Function. ...
  7. AND Function. ...
  8. OR function.

What are the 7 common uses for Excel? ›

The 7 Most Popular Uses Of Excel For Your Business
  • Data Entry and Storage.
  • Collection and Verification of Business Data.
  • Administrative and Managerial Duties.
  • Accounting and Budgeting.
  • Data Analysis.
  • Reporting + Visualizations.
  • Forecasting.

How many formulas are in Excel? ›

Excel has over 475 formulas in its Functions Library, from simple mathematics to very complex statistical, logical, and engineering tasks such as IF statements (one of our perennial favorite stories); AND, OR, NOT functions; COUNT, AVERAGE, and MIN/MAX.

Which option is used to combine 5 cells in one cell Excel? ›

Right-click the selected cells and click Merge Cells.

How do I combine columns and rows? ›

You can combine two or more table cells located in the same row or column into a single cell. For example, you can merge several cells horizontally to create a table heading that spans several columns. Select the cells that you want to merge. Under Table Tools, on the Layout tab, in the Merge group, click Merge Cells.

How do I add 3 columns in Excel? ›

To insert a single column: Right-click the whole column to the right of where you want to add the new column, and then select Insert Columns. To insert multiple columns: Select the same number of columns to the right of where you want to add new ones. Right-click the selection, and then select Insert Columns.

How do I merge 4 cells in Excel? ›

Click the first cell and press Shift while you click the last cell in the range you want to merge. Important: Make sure only one of the cells in the range has data. Click Home > Merge & Center.

What is concat function in Excel? ›

The CONCAT function combines the text from multiple ranges and/or strings, but it doesn't provide delimiter or IgnoreEmpty arguments. CONCAT replaces the CONCATENATE function. However, the CONCATENATE function will stay available for compatibility with earlier versions of Excel.

How do I combine two columns vertically? ›

To merge cells in Excel, select the cells you want to merge, click the Merge list arrow and select a merge option.
  1. Select the cell(s) you want to align.
  2. To vertically align cells, click the vertical alignment button you want.
  3. To horizontally align cells, click the horizontal alignment button you want.

How do I combine two columns in a data frame? ›

Combine Two Columns Using + Operator

By use + operator simply you can combine/merge two or multiple text/string columns in pandas DataFrame. Note that when you apply + operator on numeric columns it actually does addition instead of concatenation.

What are the 4 types of Excel? ›

Excel data types are the four different kinds of values in Microsoft Excel. The four types of data are text, number, logical and error. You may perform different functions with each type, so it's important to know which ones to use and when to use them.

What are the 6 most common functions in Excel? ›

Top 11 Most Used Functions in Excel
  • 1 – SUM() SUM: This is probably Excel's most basic and commonly used formula. ...
  • 2 – AVERAGE() AVERAGE: As the name suggests, this formula returns the average of a range of cells. ...
  • 3 – COUNT() ...
  • 4 – MAX() ...
  • 5 – MIN() ...
  • 6 – IF() ...
  • 7 – VLOOKUP() ...
  • 8 – INDEX() & MATCH()

What are the 3 common uses for Excel? ›

The main uses of Excel include: Data entry. Data management. Accounting.

What is Excel pivot? ›

A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data. PivotTables work a little bit differently depending on what platform you are using to run Excel.

What is a cell in Excel? ›

Cells are the boxes you see in the grid of an Excel worksheet, like this one. Each cell is identified on a worksheet by its reference, the column letter and row number that intersect at the cell's location. This cell is in column D and row 5, so it is cell D5. The column always comes first in a cell reference.

Why Excel skills are important? ›

Microsoft Excel enables users to identify trends and organize and sort data into meaningful categories. Excel also performs Human Resources functions, such as sorting worked hours and organizing employee profiles and expenses, which help businesses better understand the structure and activities of their workforce.

What are the 5 powerful Excel functions that make work easier? ›

5 Functions of Excel/Sheets That Every Professional Should Know
  • VLookup Formula.
  • Concatenate Formula.
  • Text to Columns.
  • Remove Duplicates.
  • Pivot Tables.
23 Jan 2019

How many cells Excel has? ›

The specifications for Excel Worksheet current versions include: Number of Rows per Worksheet: 1,048,576. Number of Rows per Worksheet: 16,384. Total cells per Worksheet: 17,179,869,184.

How do I combine columns in Excel? ›

How to Combine Columns in Excel
  1. Click the cell where you want the combined data to go.
  2. Type =
  3. Click the first cell you want to combine.
  4. Type &
  5. Click the second cell you want to combine.
  6. Press the Enter key.

How do I stack multiple columns into one? ›

Use Ampersand (&) to merge two cells in Excel
  1. Double-click the cell in which you want to put the combined data and type =
  2. Click a cell you want to combine, type &, and click the other cell you wish to combine. ...
  3. Press Enter when you have selected all the cells you want to combine.
10 Jan 2022

How do I combine multiple columns into one worksheet? ›

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Select the rows, columns, or cells to merge.
  3. At the top, click Format. Merge cells, then select how you want your cells to be merged.

How do I get data from multiple columns into one column in Excel? ›

Join columns using the Merge Cells add-in for Excel
  1. Download and install the Ultimate Suite.
  2. Select all cells from 2 or more columns that you want to merge, go to the Ablebits.com Data tab > Merge group, and click Merge Cells > Merge Columns into One.
  3. In the Merge Cells dialog box, select the following options:
19 Oct 2022

How do I combine two columns of names? ›

To join first and last name by merging cells, here's what you do:
  1. Select the two columns of names you want to combine.
  2. On the Ablebits tab, in the Merge group, click the Merge Cells drop-down arrow, and choose Merge Columns into One:
  3. The Merge Cells dialog box will show up. ...
  4. Click the Merge button.
18 Oct 2022

How do I combine columns? ›

Combine data from 2 columns into 1 column
  1. Select the cell where you want to put the combined data.
  2. Type = and select the first cell you want to combine.
  3. Type & and use quotation marks with a space enclosed.
  4. Select the next cell you want to combine and press enter. An example formula might be =A2&" "&B2.

How do I combine two column names in Excel? ›

Let's say you want to create a single Full Name column by combining two other columns, First Name and Last Name. To combine first and last names, use the CONCATENATE function or the ampersand (&) operator.

How do I combine columns into one column in sheets? ›

Combine Multiple Columns in Google Sheets into One Column
  1. In the cell D2 insert the formula: =CONCATENATE(B2," ",C2)
  2. Press enter and drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

How do I merge two columns in Excel and keep all data? ›

How to merge cells in Excel without losing data
  1. Select all the cells you want to combine.
  2. Make the column wide enough to fit the contents of all cells.
  3. On the Home tab, in the Editing group, click Fill > Justify. ...
  4. Click Merge and Center or Merge Cells, depending on whether you want the merged text to be centered or not.
30 Sept 2022

How do I merge columns in numbers? ›

Merge cells: Select two or more cells, then choose Table > Merge Cells (from the Table menu at the top of your computer screen).

Videos

1. How to Merge Data From Multiple Columns Using TextJoin & Concatenate Functions | Excel
(The Organic Chemistry Tutor)
2. Combine Multiple Functions into the Same Formula in Excel
(TeachExcel)
3. How to Combine Cells in Excel (Using formulas) | Combine Columns in Excel
(TrumpExcel)
4. Apply Formula to an Entire Column in Excel (5 Easy Ways)
(TrumpExcel)
5. Combine data from two or more cells in Excel
(Ajay Anand)
6. Top 10 Most Important Excel Formulas - Made Easy!
(The Organic Chemistry Tutor)
Top Articles
Latest Posts
Article information

Author: Melvina Ondricka

Last Updated: 01/09/2023

Views: 5845

Rating: 4.8 / 5 (68 voted)

Reviews: 83% of readers found this page helpful

Author information

Name: Melvina Ondricka

Birthday: 2000-12-23

Address: Suite 382 139 Shaniqua Locks, Paulaborough, UT 90498

Phone: +636383657021

Job: Dynamic Government Specialist

Hobby: Kite flying, Watching movies, Knitting, Model building, Reading, Wood carving, Paintball

Introduction: My name is Melvina Ondricka, I am a helpful, fancy, friendly, innocent, outstanding, courageous, thoughtful person who loves writing and wants to share my knowledge and understanding with you.