by Whenever you work with large Google spreadsheets, you most likely have to filter the table to concentrate on the specific information.
Wouldn't it be best to split that info into multiple separate sheets or even files in Drive? So there's each sheet devoted to its own thing: whether it's a name, number, date, etc. Let alone the emerging possibility of sharing only related information with other people.
If you share my opinion, let me show you a few ways of splitting Google sheets into multiple sheets or files.
How to split Google sheet based on column values
Imagine this: you track expenses in a Google Sheets document. Each day you fill in the date, the amount spent, and the category. The table grows, so it makes more and more sense to split it by category:
Let's consider your options.
1. Split Google sheet into multiple sheets within the file
If you're okay with having multiple sheets (each for its own category) in one Google file, two functions and one add-on will help.
Example 1. FILTER function
You're most likely already using the FILTER function. It filters your table by a certain condition (Category in my case) and returns only the related values. It's like splitting Google Sheets by common values in a column:
Note. I won't cover the function basics here since FILTER already owns its tutorial on our blog.
Let me start by bringing all expenses for Eating Out to another sheet.
I create a new sheet in my file first, and enter the following formula there:
=FILTER(Sheet1!A2:G101,Sheet1!B2:B101 = "Eating Out")
As you can see, I literally take all existing records from my original sheet — Sheet1!A2:G101 — and pick out only those that have Eating Out in column B — Sheet1!B2:B101 = "Eating Out".
Then you just duplicate this sheet into as many sheets as there are categories to split by and adjust each formula for each new category. The formula will continue adding all new related info automatically.
Not the most elegant way if you ask me, but still — a working one.
Example 2. QUERY function
The next function for the task is QUERY. I also already covered its basics on the blog. It's like Nathan in the uncharted waters of Google Sheets — deals with the impossible :) Yes, even splits Google sheet into multiple sheets by common values!
So what QUERY exactly will get all expenses for Eating Out?
=QUERY(Sheet1!A1:G101,"select * where B = 'Eating Out'")
The logic is the same as for FILTER:
- it looks at the entire range from my source sheet — Sheet1!A1:G101
- and selects all those rows where column B is saying Eating Out — "select * where B = 'Eating Out'"
The formula will also continue adding all new related info automatically.
Alas, lots of manual preparations here as well: you will still need to add a new sheet for each category with its own formula there.
Example 3. Filter and Extract Data add-on
There's one more way to split your Google sheet into multiple sheets within the file. It's a special add-on for Google Sheets: Filter and Extract Data, a user-friendly replacement for those FILTER and QUERY functions.
- Set up the split.
The whole add-on window is like a filter you set up, only in simple sentences. You don't need to worry about the formula syntax anymore. You simply choose the required conditions from the drop-downs, like Category = "eating out":
There are plenty of different operators that will split your Google sheet by common data in multiple columns. For instance, quickly pull all Eating Out spendings but for March only, and get only 4 related columns:
- Preview the split result.
Once you tweak these settings, you'll notice that the add-on automatically creates a formula for this filter. In addition, you can preview the results you'll get with these settings. This lets you quickly alter the options until you see the result you'd like to insert into the file:
- When the preview is exactly as you need, add a new blank sheet in your file and pick its leftmost cell as a place to insert the split result:
- And finally press one of the buttons in the tool to insert the result either as a ready-made formula (that will continue to split your Google sheet automatically once you enter new data there) or as values ready to be processed:
2. Split Google sheet into several sheets in another file
If you don't want to generate multiple sheets within one spreadsheet, there's a way to split Google sheet but put the results into another file: the QUERY + IMPORTRANGE duo.
- Create a new spreadsheet in yout Drive.
- And enter your formula there:
=QUERY(IMPORTRANGE("1dbTp-ZhEfLlPDn8PiJrCiQ7GJIJxM-Lu27X-Qq1uytI","Sheet1!A1:G101"),"select * where Col2 = 'Eating Out'")
- QUERY does the same as I mentioned right above: it goes to the original table and takes those rows where B contains Eating Out. As if splitting the table!
- What's with the IMPORTRANGE then? Well, the original table is in another document. IMPORTRANGE is like a key that opens that file and takes what you need. Without it, QUERY will not pass :)
Tip. I described IMPORTRANGE in detail earlier in our blog, have a look.
As soon as you hit Allow access, all data will load in seconds (well, or minutes if there is a lot of data to pull).
As you can see, in this case, you have to manually create a new spreadsheet with the new sheets inside it, and build QUERY + IMPORTRANGE functions for each required value.
If this is too much, I urge that you try our Split Sheet add-on described below — I promise, you won't regret it :)
Split your Google sheet into multiple separate files
The next step would be to split each category into its own Google Sheets file.
There's this one user-friendly tool for this task — Split Sheet add-on.
Its main purpose is to split your Google sheet into multiple sheets/spreadsheets by values in a column of your choice (or even by a specified number of rows).
All it takes is 3 steps:
- Select the table & a way to split by: columns or rows.
- Choose the columns or specify the number of rows to split by.
- Decide where to place the result.
It will literally take just a few clicks to set up your requirements. Split Sheet will do the rest.
How to split one Google file into separate files by sheets
Sometimes splitting just one table into multiple sheets is not enough. Sometimes you may want to go further and place each table (sheet/tab) to a separate Google spreadsheet (file) in your Drive. Luckily, there are a few ways for that as well.
Sheets Manager add-on — quickly split Google sheets into multiple new spreadsheets
Let me pull the quickest and easiest way to split your spreadsheet out of my tool belt.
Sheets Manager add-on lists all sheets on its sidebar and provides a button for each action. So yes, it sees all sheets in the file and quickly splits them to separate files in Drive.
Install it and you'll need to do 2 things only:
- Select all sheets (on the add-on sidebar) that no longer belong in your currently open spreadsheet.
Tip. Press Shift to select contiguous sheets and Ctrl for individual sheets. Or make use of the checkboxes next to the sheet names.
- And click just one option: Move to > Multiple new spreadsheets:
The add-on will cut the sheets from your current spreadsheet and paste them into new spreadsheets in your Drive. You will find those files in a folder named after your original file:
Sheets Manager will also inform you with a result message and give you a link to open that new folder with split sheets in a new browser tab right away:
And that's it!
No need to build formulas and copy-paste them, manually create new files in advance, etc. The add-on does everything for you once you click the corresponding button.
Get your Sheets Manager as a standalone add-on:
Or get it as part of Power Tools along with 40+ other time-savers for spreadsheets:
Below are other ways that don't require add-ons, but unravel the aim bit by bit and require a lot of manipulations.
Duplicate spreadsheets and remove unwanted tabs
This one is quite clumsy but it's still a solution.
- Find and select the spreadsheet that you want to split in Drive:
- Right-click it and make its copy:
- Create more copies till you have as many of them as there are sheets in the file. E.g. if there are 4 sheets (tabs), you will need 4 separate Google spreadsheets — one per tab:
- Open each file and remove all unnecessary sheets. As a result, each file will contain only one required tab.
- Rename each spreadsheet based on the sheet it contains:
Tip. Or even create a special folder and move all these spreadsheets there:
Copy each tab to a new spreadsheet manually
This standard solution splits your Google Sheets file into multiple files a bit more elegantly:
- Open the spreadsheet that you want to split into multiple spreadsheets by tabs.
- Right-click each sheet that you'd like to see in another file and choose Copy to > New spreadsheet:
Tip. A new spreadsheet will be created right in your Drive, but it will be untitled. Don't worry — with every sheet being copied to a new spreadsheet, you will get a link to open that file in a new tab:
and rename it accordingly:
- Then you'll just need to go back to the original file and delete all remaining sheets there but one:
Copy the ranges using the IMPORTRANGE function
There's always a function for any task in Google Sheets, right? Splitting Google sheets into multiple separate files is not an exception. And the IMPORTRANGE function is again perfect for the task.
Here are the steps to follow for each sheet in your Google Sheets file:
- Start by creating a new spreadsheet in Drive:
- Open it, and enter your IMPORTRANGE function:
=IMPORTRANGE("1Uk2YVGpTStLiA9M-T0xkBpRTOcCvZZEntCLFnQ4EHVQ","I quarter!A1:G31")- 1Uk2YVGpTStLiA9M-T0xkBpRTOcCvZZEntCLFnQ4EHVQ is a key from the URL of the original spreadsheet. By 'a key' I mean that unique mix of characters between 'https://docs.google.com/spreadsheets/d/' and '/edit#gid=0' in the URL bar that leads to this particular spreadsheet.
- I quarter!A1:G31 is a reference to a sheet and a range that I want to get to my new file.
- Of course, the function won't work until I grant it access to pull data from my original spreadsheet. I need to hover the mouse over A1 since it holds IMPORTRANGE, and press the corresponding button:
As soon as it's done, the formula will pull and display the data from the source spreadsheet. You may give this sheet a name and remove the same sheet from the original file.
Also, repeat this for the remaining tabs.
Hope these solutions will help you out! Otherwise, I'll meet you in the comments section down below ;)
You may also be interested in
