Combine 2 Columns Into 1 In Google Sheets

It is often that we find it difficult to organize and manage sheets that contain data. Unorganized files can result in the loss of important data. Therefore, it would be convenient if the sheets were combined into one Master Sheet so that all the important and required information can be displayed in a single sheet.

In addition to that, it makes cross-comparisons and running statistical analysis even easier. So, let us take a look into different ways regarding combining multiple Google Sheets.

Combining Multiple Google Sheets Using Tabs

It can be done using arrayformula function of Google Spreadsheets. Example is given below. Used same ranges of two different sheets to collate data into one sheet. Combine the data in two (or more) different columns in Google Sheets together. Each row's cells can be merged and the output is combined with optional extra.

The first method of combining multiple Google Sheets into one Master sheet requires using tabs.

First, you will need to upload sheets that contain the data in addition to a blank Master sheet. The Master Sheet is the sheet where the data will be combined.

Now, head over to the Master Sheet and Type = {.

Move towards the first sheet that contains the data and select everything except the columns.

Repeat the process for the remaining sheets and make sure that you add ; while adding sheets in the function bar above.

After you are done copying data, close the function in the command bar by adding } and hit Enter.

You will have the data of different sheets combined into one Master Sheet.

Combining Multiple Google Sheets Using Powertools (Add-On)

Another way of combining multiple Google Sheets is by using Add-Ons such as Power Tools.

To install Powertools, simply visit the Add-ons section and click Get add-Ons. Search for Power Tools and install.

Once you have launched Power Tools, a new Window will pop up on the right, providing several options regarding merging and combining. Select Combine Sheets.

Power Tools will automatically upload all the Google Sheets located in your drive. You can click the Add files from Drive optionto view all the files present in the drive and select the ones you wish to combine.

Select the files and click Next.

You can choose a custom location for the new combined file or a New sheet or Spreadsheet. In this tutorial we have selected New Sheet. Click Combine and you shall end up with a separate sheet termed as Combined Data.

Combining Multiple Google Sheets Using the ImportRange Function

The final method of compiling Google Sheets involves using the ImportRange function.

Open the sheet where you want the data to be imported along with the sheets that you want to pull data from.

Now, type = and select ImportRange. ImportRange function requires the link of the original sheet (the sheet from which you wish to pull data from) and the range of cells.

In simpler words, the ImportRange function will consist of three things: Original Sheet link, Sheet Name and Range of cells.

The ImportRange function will look similar to this:

=IMPORTRANGE(“1CGzy5oWpPI7hN-RIvlZZ3zn12IL8m4UlIkSC_ZfizvM”,”Sales Sheet A!A1:E4)”)

You might come across a prompt such as the one displayed in the image above. This is because initially Google Sheet is not allowed to fetch information from other sheets. Therefore, simply click Allow Access.

Once access is allowed, the data will be copied but without the original formatting.

The ImportRange function is particularly important if you have data present on individual google sheets. This makes it easy to import data from multiple Google sheets in a single go by using a single function.

Excel Merge button is not the correct solution to combine columns into one list without losing the data because it keeps only the value from the upper left cell from the selection:

Figure 1. Excel Merge button limitation

Combine 2 columns into 1 in google sheets template

There are several ways to merge columns, but the commonly used are:

  • Combine columns into one list with CONCATENATE function
  • Merge columns using NotePad

This step by step tutorial will assist all levels of Excel users to combine multiple columns in Excel and Google Sheets into one column.

Combine Columns into One List in Excel with CONCATENATE Function

To combine multiple columns into one we should follow the steps:

  • Insert a new column by selecting entire column D
  • Right-click the mouse and choose Insert

Figure 2. Insert a new column in Excel

  • Select the cell D2 and write the formula: =CONCATENATE(B2,' ',C2)
  • Press enter
  • 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.

Figure 3. Merge Excel columns with CONCATENATE function

Note: we use space under quotations as a delimiter in the formula but we can use any other symbol.

Merge Excel Columns Using NotePad

The faster way to merge columns is to use NotePad instead of Excel formulas. As in the previous example, we want to combine multiple columns into one column:

  • Insert new column as explained above
  • Select the columns that you want to combine into one list and click Ctrl+C

Figure 4. Columns to be merged using NotePad

  • Open NotePad: Start, All Programs, Accessories, Notepad
  • Paste values in NotePad using Ctrl+V

Figure 5. Paste column values in NotePad

  • Select the Tab character between columns in NotePad and copy Tab (Ctrl+C or right mouse click and Copy)

Figure 6. Copy Tab character in NotePad

  • Open Replace dialog box with Ctrl+H, paste Tab character in the field Find what and insert space in the field Replace with

Figure 7. Replace dialog box in NotePad

  • Click on the button Replace All to replace the Tab characters with Space and click Cancel to exit the window

Combine Data In Column Into One Cell

Figure 8. Tab characters replaced with space characters

  • Select data in NotePad with Ctrl+A and copy data with Ctrl+C
  • Go back to Excel file and paste data in column D with Ctrl+V

Convert 2 Columns To 1 In Excel

Figure 9. Combine multiple columns into one with Notepad

Combine Multiple Columns in Google Sheets into One Column

To merge columns in Google Sheets we should follow the steps:

  • Insert a new column by selecting entire column D
  • Right click the mouse and choose Insert 1 left

Figure 10. Insert a new column in Google Sheets

Combine 2 columns into 1 in google sheets excel
  • In the cell D2 insert the formula: =CONCATENATE(B2,' ',C2)
  • 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.

Figure 11. Combine multiple columns into one in Google Sheets

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our liveExcelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.