Combining Spreadsheets in Google Sheets

Let’s say you had several spreadsheets saved in Google Drive, and we wanted to combine them into one spreadsheet; how would we do this?

As explained in 📺this video, we can use formulas, but this gets tedious when dealing with a large number of sheets. Instead, we can use a Google Sheet add-on known as Power Tools.

The first thing to note is that the data must have matching headers. They don’t have to be in the same order across all spreadsheets, but they need to match. We can combine the data in a static way or make sure that it’s dynamic by auto-inserting the formulas.

In our case, we are going to combine three spreadsheets that contain lists of companies. Here’s a look at one of the sheets to better understand what we’re working with.

On of the three spreadsheets that are will be combined

There are two other similar sheets – Australian companies & USA companies. Now let’s combine them.

Combining Static Spreadsheets

These are the steps to follow when combining the data is a one-time operation that doesn’t need to update when any of the sheets changes:

  1. Launch Power Tools via Add-ons > Power Tools. (If you haven’t installed Power Tools yet, you can follow this link to install.
  2. In the sidebar on the right, select “Merge & Combine.”
  3. Select “Combine sheets” and click on “Add files from Drive” in the pop-up that appears.
  4. Add all the sheets from which you would like to fetch data.
  5. Make sure the boxes are next to the files and click “Next.”

Using a menu in Power Tools to select all of the spreadsheets to be combined

  1. In the following pop-up that appears next, make sure that “Use a formula to combine sheets” is not checked. (This is because we are working with static data). Now click combine.

Power Tools menu with the option to use a formula to combine the sheets unselected

After a few seconds, a new sheet within the spreadsheet will appear with the combined data.

Combined spreadsheet with a success message

Combine Dynamic Spreadsheets

If we need the combined data to be dynamic, Power Tools can automatically insert the formulas we need. To do this, follow the steps we used in the previous example but, this time, make sure that the box labeled “Use a formula to combine sheets” is checked.

Upon combining, the sheet containing combined data will appear with errors since no user has granted permissions yet. (Remember we need to give access when using IMPORTRANGE). An extra sheet will appear, and we can then grant permissions there. That should correct the errors.

Since we’re using formulas, any changes to the source files will reflect on the combined sheet.

Disclosure: This is an independently owned website that sometimes receives compensation from the company's mentioned products. Prolific Oaktree tests each product, and any opinions expressed here are our own.

Cells with data to be combined

Combine Cell Contents from Two Different Columns in Google Sheets

Separate Cells Needing to Be Joined

If you’re using Google Sheets and you have two columns of data that you want to join together, there are a few easy steps that we can walk through to get this done. In the following example, we have people’s first names and last names. This is a fairly common scenario. We are going to join them together and put a comma in between them.

Cells with data to be combined
First name and last name, not yet combined

The Formula

Place your cursor to the right of the two cells that you want to combine. In this example, it would be cell C2. As always, when you are entering a formula in Google Sheets, start out with an equal sign, and then indicate what you want to have joined together using this formula – =A2&", "&B2.

After entering this formula, you should see the first combined name.

Video Explanation

To copy this all the way down, place your cursor in the lower right hand corner of the rectangle. There’ll be a small solid blue square and your cursor will turn into a blue plus sign. Double-click the blue box and it copies the formula all the way down.

The Result

Cells with data to be combined
First name and last name, and combined

Now, all of your names should be combined into one column, viola!

Live Google Sheet with This Data

docs share icon