Whenever you work with large Google spreadsheets, chances are you constantly filter the table to see and assess particular information only.
Wouldn't it be best to split that info into multiple separate sheets or even spreadsheets (files) in Drive? Personally, I find having each sheet devoted to its own thing — whether it's a name, number, date, etc. — super convenient. Let alone the emerging possibility to share only related information with other people.
If that's your goal, let's split our sheets and spreadsheets together. Pick the way you want to get your data and follow the steps described there.
Split one sheet based on column values
Imagine this: you track expenses in a Google Sheets document. Each day you enter the date, the amount spent, and the category. The table grows, so it makes more and more sense to split the table by category:
Let's consider your options.
Split a sheet into different sheets within the file
If you're okay with having multiple sheets (each with its own category) in one Google spreadsheet, two functions will help.
Example 1. FILTER function
The FILTER function will most likely come to your mind first. It filters your range by a certain condition and returns only the related values as if splitting the sheet by common values:
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 spreadsheet 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".
As you may have already thought, you will have to create as many sheets manually as there are categories to split by and adjust a formula for each new sheet. If that's not your jam, however, there's a lot more efficient formula-free way to split a sheet. Feel free to hop right to it.
Example 2. QUERY function
The next is the function you may haven't heard of — QUERY. I also talked about it on our blog. It's like Nathan in the uncharted waters of Google Sheets — deals with the impossible :) Yes, even splits the sheet by common values!
Note. It uses a peculiar language (similar to commands in SQL) so if you haven't used it before, make sure to check out this article about it.
So how does the QUERY formula looks so it could get all expenses for Eating Out?
=QUERY(Sheet1!A1:G101,"select * where B = 'Eating Out'")
The logic is the same:
- it looks at the entire range from my source sheet — Sheet1!A1:G101
- and selects all those where the value in column B equals Eating Out — "select * where B = 'Eating Out'"
Alas, lots of manual preparations here as well: you will still need to add a new sheet for each category and enter a new formula there.
Split your sheet into several sheets in another file
If you don't want to generate multiple sheets within one spreadsheet, there's an option to split the sheet and put the results in another file.
The QUERY + IMPORTRANGE duo will help.
Let's see. I create a new spreadsheet in my Drive and enter my 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 my original table and takes those rows where B contains Eating Out. As if splitting the table!
- What's with the IMPORTRANGE then? Well, my original table is in another document. IMPORTRANGE is like a key that opens that file and takes what I need. Without it, QUERY will not pass :)
Tip. I described IMPORTRANGE in detail earlier in our blog, have a look.
When you employ IMPORTRANGE, you need to give it access to connect your new file with the original one by pressing the corresponding button. Otherwise, all you'll get is an error:
But once 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, this way implies that you are ready 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 sheet into multiple separate spreadsheets without formulas
The next step would be to split each category into its own Google Sheets file.
And I'd like to focus on the easiest user-friendly way there is — 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.
All you need to fine-tune is located in just one window:
- a few checkboxes — columns to split by
- one drop-down — with places for the result
- and the finishing button
It will literally take just a few clicks to set up your requirements. Split Sheet will do the rest:
Install Split Sheet from the Google Sheets store and split your sheets into several sheets or files like a pro — in just a few clicks and minutes.
Split one Google spreadsheet into separate Google Drive files by tabs
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.
Duplicate spreadsheets and remove unwanted tabs
This first solution is quite clumsy but it's still a solution.
Tip. If you don't want to waste your time on clumsy solutions, here's a link to get to know the easiest way right away.
- 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 spreadsheet will contain only one required tab.
- And finally, rename each spreadsheet based on the sheet it contains:
Tip. Or even create a special folder and move all these spreadsheets there:
Сopy each tab to a new spreadsheet manually
There's one more standard solution — a bit more elegant:
- 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 one Google spreadsheet into multiple separate spreadsheets by tabs 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:
- 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.
Sheets Manager add-on — quickly move several Google sheets to multiple new spreadsheets
While all aforementioned ways unravel the solution bit by bit and require a lot of manipulations, let me pull another, 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. Yes, including splitting the spreadsheet by sheets into multiple different files in Drive.
Install it and you'll need to do 2 things only:
- Select all sheets (on the add-on sidebar) that are 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.
Hope these solutions will help you out! Otherwise, I'll meet you in the comments section down below ;)
You may also be interested in