How to efficiently merge dozens of spreadsheets
It won’t be an understatement to say that most of us handle our data and reports using spreadsheet applications like Google Sheets. The primary reason being they are inexpensive (read free). But they have also come very far in terms of form, functionality, accessibility, and ease of use. And we come across many situations where we will have to manipulate the data sets we are dealing with. One such task is merging the data from various sources. For instance, create the database for overall sales from multiple regional sales data sources.
Let us assume we have dozens of regional company sales data coming from all over the globe. These are separately maintained within individual Google Sheets files. One of the jobs of the finance department is to arrive at the overall numbers – like profitability, margins for the entire company. For that, they will need to combine all these individual data files into one spreadsheet.
The immediate native solution that comes to mind is through the use of the IMPORTRANGE formula. Although this works good in case of a single file, we can’t readily combine the data spread across dozens of spreadsheets. At best, we can allocate a separate worksheet within the consolidated file, for each of these individual sales data files. Having so many worksheets just for the purposes of pulling in information is not an efficient and effective solution. Because we may have brought them into a single file, but not into a single sheet (which is the ideal case to analyze the data). Imagine having to navigate across dozens of sheets, let alone using them for reporting purposes.
Alternative: Use Sheetgo
The Sheetgo add-on is brought to life with an aim to help its users with Google Sheets connectivity like never before. It is such a user-friendly tool that even a beginner could intuitively start using it to build meaningful connections across his spreadsheets. It lets us easily import, export, merge, append and filter data between multiple Google Sheets files. Not to forget the myriad configuration capabilities it offers to customize these connections. Install by clicking the button below.
For the purposes of illustration of this example, let us assume the following as this would result in much lesser effort while configuring connections from dozens of files:
- All the regional sales data files are stored within a single folder on Google Drive (to use Consolidate from a folder feature).
- All the files have identical headers.
- Data is in the first worksheets for all these files.
Open the file that we want the sales data consolidated to. Navigate to Add-ons > Sheetgo > Start to run the Sheetgo add-on. A sidebar opens up within the Google Sheets interface. Move the cursor onto the green plus ‘+’ button, and subsequently, click on Import.
Click on the SELECT FILE(S) button within the Data source section. Doing so will display all the files and folders recently accessed within Google Drive. Option 1 is to select all the files to consolidate separately. And consequently, choose the sheet within the selected file which we need the data imported from.
Now click on ADD MORE option and repeat this step for all those files that we need to import the data from. But, this would be cumbersome if there are so many files. There is a much better alternative, in case we have multiple files. Earlier in the post, we noted that it is better to have all the source files in one folder. We can use that to our advantage. Instead of picking each file repetitively, choose the folder where we have stored all the source files. Then click on the SELECT THIS FOLDER option that appears at the very top of the sidebar. By default, Sheetgo will pull in data from the first sheet from all the files available in the selected folder.
Next up is the Settings section – you may adjust any of the configuration options as needed. Once done with the Settings, click on the SAVE CONNECTION button. Congratulations! We just merged multiple files into a single spreadsheet. We’ll see this in the new sheet that Sheetgo would create for us in a few quick seconds.