8 Ways to Merge Sheets + Data in Google Sheets

Last Updated on January 9, 2024 by Jake Sheridan

In this guide, you will learn all about merging data, rows, columns and cells in Google Sheets.

Have you ever tried to merge data in Google Sheets?

If you have, then you know it can sometimes be a little tricky. But it doesn’t have to be.

Let’s get merging! (worst catchphrase ever)

What is a merge in Google Sheets?

Merge simply means to combine.

You can merge cells in Google Sheets to combine two or more cells.

You can also merge cells vertically, horizontally, or in both directions using the Format menu.

Here’s an example of merging some cells in a Google Sheet:

See how the yellow cell merges with the other two to become one big cell?

Pretty useful for adding data labels and making pretty spreadsheet. It’s a pretty simple function.

8 Ways to Merge Google Sheets

There are two main ways to “merge” in Google Sheets: merging cells, and merging sheet data

Below we’ll look at how to do these as well as some more ways to merge in Google Sheets:

1. Merging cells in Google Sheets

First, you can merge cells in a Google Sheets document.

This action allows you to combine two or more cells horizontally, vertically, or both.

It allows you to extend the content of a particular cell across multiple cells.

So for example, we’ve got the year ‘2023’ above each quarter:

We can make the table easier to read by merging cells A1:D1.

After merging, it becomes clearer that all the quarter columns fall under the year 2023.

That was merging cells, now let’s look at merging sheets:

2. Merging sheets in Google Sheets

Merging sheets in your Google Sheets document involves combining data from multiple sheets into a single sheet.

Google Sheets includes several tools and features that allow you to merge sheet data.

If you want to copy individual sheets into another spreadsheet, we can use the Copy Sheet option.

To copy a sheet from one document to another document, you can follow the following steps:

Step 1

Open the Google Sheets document containing the sheet you want to copy.

Step 2

Click on the arrow icon on the current sheet’s tab.

Select Copy to > Existing spreadsheet.

Step 3

In the Select a spreadsheet dialog box, you can select which document you want to copy the current sheet to.

Once you’ve selected a sheet, click on Insert to proceed.

Step 4

After a few seconds, Google Sheets will notify you that the sheet has been copied to the destination document successfully.

Click the Open spreadsheet option to open the destination spreadsheet where your data has been copied to.

Step 5

The copied sheet should now appear as the rightmost sheet tab in the destination Google Sheets document.

In the example above, our copied sheet is labeled “Copy of Sheet1”.

3. Merge without losing data

How do I merge cells in Google Sheets and keep all data?

Here’s how:

If you would like to merge cells in Google Sheets while also keeping all data, we must use a Google Sheets function rather than using the built-in Cell Merge feature.

Step 1

Select the cell where you want to add the merged data.

In this example, we want to output a value in column F that merges all cell values in the previous columns.

Step 2

Type the TEXTJOIN function on the selected sheet.

The TEXTJOIN function requires three main arguments: delimiter, ignore_empty, and the array of text to join.

In our example above, we merged the values in row 7 using the formula =TEXTJOIN(“,”,TRUE,A7:E7)

For our example, we’ll set our delimiter to a comma “,” character.

This will allow our output to separate each value by a comma.

Next, we’ll set ignore_empty to TRUE to ignore empty cells. Lastly, we’ll input A7:E7 as the values we want to merge into a single string.

Step 3

Hit the Enter key to evaluate the TEXTJOIN function.

The selected cell should now contain a single string that merges all the values in the range.

In our example, the TEXTJOIN function returns the string “January,18,57,80,80”

We can use the Fill Handle feature to merge each of the remaining rows into a single string as well.

4. Merge data from multiple sheets

How do you combine data from multiple sheets?

Here’s how you can use this formula in Google Sheets to combine data from multiple sheets.

Step 1

One way to combine data from multiple sheets is through the QUERY function.

Given two cell ranges range1 and range2, we can combine the sheets using the following formula.

=QUERY({{range1};{range2},”Select * where Col1 is not null”)

This function works when your data has already been consolidated into a single spreadsheet but are placed in different sheet tabs.

In this example, we want to combine order data in the sheets data_2022 and data_2023 and place them in the all_data tab.

To start, select an empty cell in the destination sheet where you want to output the combined data.

Step 2

For the data argument in our QUERY function, we’ll create an array that combines the two ranges we want to merge.

In our example, we’ll combine our two data ranges by using the array {{data_2022!A:F};{data_2023!A:F}}.

Step 3

Next, we’ll specify a specific query that will ensure that all rows from both ranges will be returned.

We’ll use the query “Select * where Col1 is not null” which will return all items from both ranges.

Step 4

Hit the Enter key to evaluate the formula.

The formula should now return a range of cells containing data from both ranges.

5. Refer to cells from other sheets

Let’s learn how to refer to cell values found in other sheets using cell references.

Step 1

In our example, we’ll select cell B1 in Sheet1.

Step 2

Next, we’ll type the formula that includes a reference to a cell from another sheet.

To create a cell reference to a cell from outside the current sheet, we’ll need to type the name of the sheet followed by the “!” symbol then specify the cell address.

For example, if we need to refer to cell A2 in Sheet2, we’ll use the formula =Sheet2!A2

If the sheet name includes spaces, we’ll need to surround our sheet name with single quotation marks.

For example, to refer to cell A2 in “Sheet 2” we can use the formula =’Sheet 2’!A2.

Step 3

Press Enter to evaluate the formula.

The content from the other sheet will now appear in your selected cell.

Step 4

Step 5

We can also use an array formula to reference and output a range of cells from another sheet.

Simply type the cell range you want to reference. Don’t forget to include the sheet name in the reference.

Wrap the cell reference with opening and closing curly braces to let Google Sheets know that you want to output an array.

In the example above, we’ll use the formula ={Sheet2!A2:A11} to output the cell range A2:A11 in the sheet named Sheet2.

6. Import a sheet

Here’s how you can import a sheet in Google Sheets.

Step 1

First, open the Google Sheets document where you want to import another sheet.

Step 2

Click on the “File” menu in the top-left corner of the screen. In the dropdown menu, select the Import option.

Step 3

You can upload a local file such as an Excel document or CSV file.

Alternatively, you can choose an existing file in your Google Drive account or enter a specific URL.

In this example, we’ll input a Google Drive URL into the search bar that links to a CSV file we’ve uploaded.

Step 4

Choose where to place the new sheet – either as a new sheet, replacing the current sheet, or appending data to the current sheet.

Other options include creating a new Google Sheets document altogether, replacing data at the selected cell, or replacing the current spreadsheet.

Ensure that the “Convert text to numbers, dates, and formulas” option is checked if you want your numerical data to be imported as numbers and not text.

Once you’ve set the right settings, click on Import data to proceed.

Step 5

The data from your selected file should now be imported into a Google Sheets document.

7. Combine data from multiple sheets with the IMPORTRANGE function

Here’s how you can combine data from multiple sheets with the IMPORTRANGE function.

Step 1

Open the Google Sheets document where you want to combine or merge data across multiple sheets.

Step 2

The IMPORTRANGE function includes two main arguments: spreadsheet_url and range_string.

Using these arguments, the function will output a range of cells from the specified sheet.

Select an empty cell and type the IMPORTRANGE function.

Step 3

For the function’s first argument, write the URL of the sheet you want to import from.

The second argument should be a string indicating the cell range to import.

Ensure that both of these values are written as strings when typing it directly into the function.

Alternatively, you can use cell references as input and place the spreadsheet_url and range_string values into those cells.

Hit the Enter key to evaluate the IMPORTANGE function.

Step 4

Google Sheets may ask for permission to access the other spreadsheet. Click Allow to proceed.

The function should now return an array containing the data you want to import.

You can proceed to use the IMPORTANGE function multiple times in the same spreadsheet to allow data from different cells to be accessible in the same document.

8. How to vertical merge (+ how to remove it)

Did you know that doing vertical merges in Google Sheets is pretty simple?

It’s the easiest way to merge two or more columns of data into one column.

Here’s how to vertical merge in Google Sheets: Select the cells you want to vertically merge together and then: 

  • Go to the top bar menu
  • Click on the Merge cells option
  • Select the Merge vertically option

But what if you want to remove a vertical merge…

Although it can be useful, a vertical merge can sometimes be a pain.

It can cause problems when you are trying to create a table or chart, as the merge can cause cells to overlap. 

Here’s how to remove a vertical merge in Google Sheets:

It’s really simple to stop cells being merged vertically:

  • Select the cells you want to unmerge
  • Go to the top bar and click the Merge cells option
  • Select Unmerge.

Help! How do I Un-merge Cells in Google Sheets?

Sometimes the merged cells don’t quite line up the way you want them to, or they become jumbled up when you try to edit them.

Fortunately, there is an easy way to fix this.

Here’s how to unmerge cells in Google Sheets: Select the cells you want to stop being merged together and then: 

  • Go to the top bar menu
  • Click on the Merge cells option
  • Select the unmerge.

FAQs (about merging in spreadsheets)

Got questions? Here’s some frequently asked questions related to merging stuff in Google Sheets:

What does merging cells in Google Sheets do?

Using the Merge tool in Google Sheets, you may merge cells to combine two or more smaller cells into one bigger cell. When using Google Sheets in a web browser, you can merge cells vertically, horizontally, or in both directions from the Format menu.

How do I merge first and last name in GSheets?

There’s no need to copy and paste each name manually (like an animal) into a new column.

You can use the concatenate function to combine them automatically.

Here’s how to merge cells in Google Sheets using the CONCATENATE function:

Put the first and last names in separate columns and then:

  • Use this formula: =CONCATENATE(A2, " ", B2)to merge the two cells
  • In this example…
    • A2 = the first name
    • B2 = the last name
    • (the blank space in speech marks between the two cells simply adds a space between the names)
  • Done, now you have combined the first and last names.

How do I group sheets in Google Sheets?

Press “Ctrl” to pick individual tabs and “Shift” to choose consecutive tabs to select many tabs at once.

How do I sum data from multiple sheets in Google Sheets?

You can use the following formula/syntax to sum values across multiple sheets in Google Sheets: =SUM(Sheet1! A1, Sheet2! A1, Sheet3! A1)

Can I merge data from Google Sheets to Google Docs?

If you’re anything like me, you have a ton of data scattered across different Google Sheets and Google Docs.

It can be a pain to keep track of it all, and especially annoying to combine them into one document. Fortunately, there’s an easy workaround.

Here’s how to merge data from Google Sheets to Google Docs:

Put the data you want to copy into a Google Sheet and then:

  • Highlight your data
  • Paste it into your Google Docs
  • When prompted, select Link to spreadsheet

Now, the table in your doc is synced with the data in your sheet.

Summary

Hopefully this guide has given you an overview of merging data, rows, columns and cells in Google Sheets.

What’s Next?

Explore some of the other useful resources on Sheets for Marketers: