It is often that we find it difficult to organize and manage sheets that contain data. Unorganized files can result in the loss of important data. Therefore, it would be convenient if the sheets were combined into one Master Sheet so that all the important and required information can be displayed in a single sheet.
In addition to that, it makes cross-comparisons and running statistical analysis even easier. So, let us take a look into different ways regarding combining multiple Google Sheets.
Combining Multiple Google Sheets Using Tabs
The first method of combining multiple Google Sheets into one Master sheet requires using tabs.
First, you will need to upload sheets that contain the data in addition to a blank Master sheet. The Master Sheet is the sheet where the data will be combined.
Now, head over to the Master Sheet and Type = {.
Move towards the first sheet that contains the data and select everything except the columns.
Repeat the process for the remaining sheets and make sure that you add ; while adding sheets in the function bar above.
After you are done copying data, close the function in the command bar by adding } and hit Enter.
You will have the data of different sheets combined into one Master Sheet.
Combining Multiple Google Sheets Using Powertools (Add-On)
Another way of combining multiple Google Sheets is by using Add-Ons such as Power Tools.
To install Powertools, simply visit the Add-ons section and click Get add-Ons. Search for Power Tools and install.
Once you have launched Power Tools, a new Window will pop up on the right, providing several options regarding merging and combining. Select Combine Sheets.
Power Tools will automatically upload all the Google Sheets located in your drive. You can click the Add files from Drive option to view all the files present in the drive and select the ones you wish to combine.
Select the files and click Next.
You can choose a custom location for the new combined file or a New sheet or Spreadsheet. In this tutorial we have selected New Sheet. Click Combine and you shall end up with a separate sheet termed as Combined Data.
Combining Multiple Google Sheets Using the ImportRange Function
The final method of compiling Google Sheets involves using the ImportRange function.
Open the sheet where you want the data to be imported along with the sheets that you want to pull data from.
Now, type = and select ImportRange. ImportRange function requires the link of the original sheet (the sheet from which you wish to pull data from) and the range of cells.
In simpler words, the ImportRange function will consist of three things: Original Sheet link, Sheet Name and Range of cells.
The ImportRange function will look similar to this:
=IMPORTRANGE(“1CGzy5oWpPI7hN-RIvlZZ3zn12IL8m4UlIkSC_ZfizvM”,”Sales Sheet A!A1:E4)”)
You might come across a prompt such as the one displayed in the image above. This is because initially Google Sheet is not allowed to fetch information from other sheets. Therefore, simply click Allow Access.
Once access is allowed, the data will be copied but without the original formatting.
The ImportRange function is particularly important if you have data present on individual google sheets. This makes it easy to import data from multiple Google sheets in a single go by using a single function.
