How to merge data in Google Sheets
Every once in a while each Google Sheets user faces the inevitable: combining several sheets into one. Copy-pasting is tedious and time-consuming, so there has to be another way. And you're right – there are several ways, in fact. So get your tables ready and follow the steps from this article. Show
All the ways I describe can be used to process large tables. But to keep this guide as clear as possible, I'll keep my tables short and am going to cut down to a couple of sheets. Reference cells in Google Sheets to pull data from another tabThe easiest way comes first. You can pull entire tables to one file by referencing cells with data from other sheets. Note. This will do if you need to merge two or more sheets within one Google spreadsheet. To merge multiple Google spreadsheets (files) into one, jump right to the next method. So, my data is scattered all over different sheets: June, July, August. I'd like to pull data from July and August into June to have one table as a result:
Though this is probably the first way you may think of to pull data from another tab, it's not the most elegant and quick. Luckily, Google prepared other instruments specially for this purpose. Copy the tabs into one spreadsheetOne of the standard ways is to copy the tabs of interest into the destination spreadsheet:
Export/import sheetsAnother way to import data from multiple Google Sheets is to export each sheet first, and then import them all to a necessary file:
Google Sheets functions to combine data from multiple spreadsheetsOf course, it wouldn't be Google if it didn't have functions to merge data in Google Sheets. IMPORTRANGE to import data from multiple Google sheetsAs the name of the function suggests, IMPORTRANGE imports data from multiple Google spreadsheets into one sheet. Tip. The function helps Google Sheets pull data from another document as well as from other tabs from the same file. Here's what the function requires: =IMPORTRANGE(spreadsheet_url, range_string)
And here's the pattern I follow to import data from multiple Google Sheets using IMPORTRANGE:
Note. You'll need this URL even if you're going to combine sheets from the same file. Tip. Though Google says the function requires the whole URL, you can easily get by with a key – a part of the URL between /d/ and /edit: Note. Remember, the link should be surrounded by double quotes. Note. By allowing access, you let the Sheets know you don't mind any existing or potential collaborators on this spreadsheet accessing data from another file. Note. IMPORTRANGE doesn't pull the formatting of the cells, only values. You will need to apply formatting manually afterwards. Tip. If the tables are rather big, just allow some time for the formula to pull all records. Note. The records returned by the function will be updated automatically if you change them in the original file. Google Sheets QUERY to import ranges from multiple sheetsAnd thus, without haste, we've come to the QUERY function once again. :) It is so versatile that can be used in Google spreadsheets to combine data from multiple sheets (within the same file) as well. So, I want to merge three different Google sheets (from one file): Winter 2022, Spring 2022, and Summer 2022. They contain the names of all employees who became best in their jobs in different months. I go to the first sheet – Winter 2022 – and add my QUERY right under the existing table:
Let's see what it all means:
As a result, two tables from other sheets have been consolidated into one sheet one under another: Tip. If you'd like to use Google Sheets QUERY to import ranges from multiple separate spreadsheets (files), you will have to implement IMPORTRANGE. Here's a formula to pull your data from other documents: Tip. I use the keys from URLs rather than entire links in this long-enough formula. If you're not sure what that is, please read here. Tip. You can also use QUERY to merge two Google sheets, update cells, add related columns & non-matching rows. Check this out in this blog post. 3 quickest ways to merge multiple Google sheetsIf standard ways of Google spreadsheets to combine data from multiple sheets seem dull, and the functions scare you off, there's an easier approach. Combine Sheets add-onThis first special add-on – Combine Sheets – was designed with a single purpose: import data from
multiple Google sheets. It's clever enough to recognize the same columns in different sheets and bring data together accordingly if you need. All you are to do is:
Here's a quick demonstration of how I combined my three small tables with the add-on: Of course, your tables can be much bigger and you can merge lots of different sheets as long as the resulting spreadsheet doesn't exceed the 10M cell-limit. Tip. Make sure to check out the help page for Combine Sheets. One of the options this add-on offers is to add more sheets to your previously combined data. In this
case on step 1, you need to pick not only the data to combine but also the existing result. Here's how it looks: Consolidate Sheets add-onConsolidate Sheets is a relatively new addition to our add-ons. Its main difference from the aforementioned tool is the ability to add up data in columns in Google Sheets (or rows, or single cells, for that matter). Consolidate Sheets also recognizes common headers in all the Google sheets to join, even if they are in the leftmost column and/or the first row. There's always an option to merge Google sheets and calculate cells based on their place in the tables. Let me break it down into steps for you as well:
Here's how this process looks: There's also an option to consolidate all your sheets using a formula. This way your result will change in sync with the values in the source sheets: Note. There are some peculiarities you need to know about how the formula works. For example, if you consolidate from multiple different files, there will be an extra step to connect the sheets for the IMPORTRANGE in use. Please visit the instructional page for Consolidate Sheets for these and other details. Or here's a short tutorial about the add-on work:
Merge Sheets add-onThere's one more add-on worth mentioning. Though it merges only two Google sheets at a time, it couldn't be more useful. Merge Sheets matches records from the same column in both sheets/documents and then pulls related data from the lookup sheet/document into the main one. Hence, you always have an up-to-date spreadsheet at hand. There are 5 straightforward steps:
If these words don't speak much to you, here's a video tutorial instead:
On this note, I'm going to finish this article. Hope these ways of pulling data from multiple different sheets into one will be of use. As always, looking forward to your comments! You may also be interested in |