Mẹo Hướng dẫn How do I create a separate spreadsheet? 2022
Hoàng Quốc Trung đang tìm kiếm từ khóa How do I create a separate spreadsheet? được Cập Nhật vào lúc : 2022-11-13 09:26:04 . Với phương châm chia sẻ Thủ Thuật về trong nội dung bài viết một cách Chi Tiết 2022. Nếu sau khi đọc Post vẫn ko hiểu thì hoàn toàn có thể lại phản hồi ở cuối bài để Tác giả lý giải và hướng dẫn lại nha.Whenever you work with large Google spreadsheets, chances are you constantly filter the table to see and assess particular information only.
Nội dung chính Show- Split one sheet based on column valuesSplit a sheet into different sheets within the fileSplit your sheet into several sheets in another fileSplit your sheet into multiple separate spreadsheets without formulasSplit one Google spreadsheet into separate Google Drive files by tabsDuplicate spreadsheets and remove unwanted tabsСopy each tab to a new spreadsheet manuallyCopy the ranges using the IMPORTRANGE functionSheets Manager add-on — quickly move several Google sheets to multiple new spreadsheetsYou may also be interested inHow do I split up an Excel spreadsheet?Can you save an Excel sheet as a separate File?How do I create a spreadsheet from another spreadsheet?
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 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:
=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-không lấy phí way to split a sheet. Feel không lấy phí 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 — đơn hàng 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?
=QUERY(Sheet1!A1:G101,"select * where B = 'Eating Out'")
The logic is the same:it looks the entire range from my source sheet — Sheet1!A1:G101and 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.
If you don't want to bother with formulas 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 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 byone drop-down — with places for the
resultand 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 thư mục 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:
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 the very end.
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:=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.
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 thư mục named after your original file:
Sheets Manager will also inform you with a result message and give you a link to open that new thư mục 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 in
How 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. Tải thêm tài liệu liên quan đến nội dung bài viết How do I create a separate spreadsheet?