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.
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.
If you're okay with having multiple sheets (each with its own category) in one Google spreadsheet, two functions will help.
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.
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:
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.
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'")
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.
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:
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.
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.
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.
Tip. Or even create a special folder and move all these spreadsheets there:
There's one more standard solution — a bit more elegant:
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:
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:
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.
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:
Tip. Press Shift to select contiguous sheets and Ctrl for individual sheets. Or make use of the checkboxes next to the sheet names.
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 ;)
Table of contents