How do I create a separate spreadsheet?
Whenever you work with large Google spreadsheets, chances are you constantly filter the table to see and assess particular information only. Show
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 valuesImagine 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 fileIf you're okay with having multiple sheets (each with its own category) in one Google spreadsheet, two functions will help. Example 1. FILTER functionThe 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: FILTER(range, condition1, [condition2, ...]) 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:
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 functionThe 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! QUERY(data, query, [headers]) 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?
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 bother with formulas at all, there's this add-on — Split Sheet — that will do everything for you. Take a look below. Split your sheet into several sheets in another fileIf 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:
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 formulasThe 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. Split one Google spreadsheet into separate Google Drive files by tabsSometimes 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 tabsThis 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.
Сopy each tab to a new spreadsheet manuallyThere's one more standard solution — a bit more elegant:
Tip. There's a way to avoid this manual copying — Sheets Manager add-on. It sees all sheets in the file and quickly splits them to separate files in Drive. I introduce it at the very end. Copy the ranges using the IMPORTRANGE functionThere'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. Sheets Manager add-on — quickly move several Google sheets to multiple new spreadsheetsWhile 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:
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 it from the Google Sheets store as a single tool or as part of Power Tools along with 30+ other time-savers for spreadsheets. Hope these solutions will help you out! Otherwise, I'll meet you in the comments section down below ;) You may also be interested inHow do I split up an Excel spreadsheet?Split a sheet into panes
Select below the row where you want the split, or the column to the right of where you want the split. On the View tab, in the Window group, click Split. To remove the split panes, click Split again.
Can you save an Excel sheet as a separate File?Note: This process moves your worksheet from the original workbook. If you want to keep your worksheet in both workbooks, in the Move or Copy dialog box, select the check box for Create a copy. Click File > Save in your new workbook. Tip: You can also save your Excel workbook with the keyboard shortcut, Ctrl + S.
How do I create a spreadsheet from another spreadsheet?Copy all the data on the sheet by pressing CTRL+C. Open the workbook in which you want to paste the data, then click the + in the status bar to add a new blank worksheet. Click the first cell in the new worksheet, then press CTRL+V to paste the data into that worksheet.
|