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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>