Split Sheet will divide your Google Sheets table into several sheets based on the contents in the selected columns or the number of rows. The resulting sheets can be placed to the same file next to the source sheet, to a completely new Google spreadsheet, or to multiple separate spreadsheets next to the original file.
Please bear in mind that Google Sheets limits the total number of possible cells in your file up to 10 million cells.
If there are filtered or/and hidden rows in your sheet, they will be processed and grouped into multiple sheets with visible records as well.
Open the sheet you want to split and go to Extensions > Split Sheet > Start:
Tip. You will also find Split Sheet in Power Tools. Just proceed to the Split group and look for the add-on icon:
Once the add-on loads, it will automatically select the range with your active table to split:
You can choose any other table in your file by picking any cell within that table and hitting Auto select. The add-on will highlight all cells with data till the first empty row & column.
If columns in your sheet have labels, check the option My table has headers. In this case, each new sheet will contain the same set of titles copied from this original table.
Here you are to also decide on the way to split the sheet of interest:
Click Next, and depending on the hand-picked way to split, the next step will invite you to choose either columns or rows to split by.
If you chose to split your Google sheet by columns on the previous step, they will appear grouped in the table:
When you're ready, press Split.
If you chose to split your Google sheet by N rows on the previous step, here you will need to specify those numbers:
When the numbers are set, hit Split.
Once the add-on divides the table, you will see the confirmation message saying how many new sheets have been created and where they have been placed.
Hey. I am trying to split my spreadsheet every 1500 rows, but it only does one range.
I also tried adding the ranges i wanted to split butit only gives me one.
Thank you for your comment.
Please note that the tool can process just one sheet from your Google spreadsheet at a time. If there are several sheets in your file and you want to split each of them by 1500 rows, then you first need to combine the data from all your sheets into one, and then run the Split Sheet tool again. To join tables from multiple sheets together, you can use the Combine Sheets tool.
If it is not your case, then please describe your task in more detail and send us a few screenshots showing what data you have, what result you want to get, what options for the add-on you choose. You can email the details to email@example.com. We'll help you further.
I would like to ask how can I split a sheet based on the alphabetical order on one column.
I have a list of several thousand of records and I need to split them in alphabetical order on different sheets.
Is there any way to do that with Ablebits? Tried some solutions here proposed but with no success
Thank you for your question.
If I understand your task right, first you need to sort your key column in an alphabetical order using the standard Sort option in Google Sheets. Then extract the first letter from the values in the key column in a separate column with the help of our Extract tool.
After that, you can split your sheet based on the extracted letters in the new column with the Split Sheet add-on.
If this solution doesn't work for you, feel free to email us at firstname.lastname@example.org for further assistance.
How can I split sheets based upon a criteria. For example I have a column named state. I want to ONLY create a new sheet with the rows that have "NC" (for North Carolina) and another sheet with the remaining rows. I don't want to create 50 separate sheets for each state.
Hello Da'Quan Love,
Thank you for your comment.
Unfortunately, the Split Sheet tool won't help you to get the result you need since it doesn't support custom conditions. However, you can use another tool - Multiple VLOOKUP Matches - to solve your task. It will help you to pull out the rows according to the condition(s) you specify to another sheet.
If you have any questions or need further assistance, please email us at email@example.com.
Is there a way to get the new tabs or spreadsheets to automatically update if the source data is changed? Lets say a date in one of the rows changes. Can it automatically update that same field on the separate tab or spreadsheet that is was split off to?
Thank you for your question. Sorry, we don't have the requested functionality yet. I will forward your idea to our dev team for consideration.
How do I split based one one column called category and then on a common value that could either be in Home column or Away column? So any rows that are in NBA category and have Home or Away Column = GSW ... then all of those rows should go into the NBA GSW tab ... and same for NBA LAKERS , etc.
Thank you for contacting us. For us to be able to help you better we need to know more details. I have just requested them in a separate email. Thank you.
How can i split a large list of names, email and phone numbers into groups of 15 on different sheets?
If you would like to split your list into separate sheets containing 15 rows of data each, please try adding an additional column (e.g. Column A) to your source data and filling it in with some values that will be the same for each portion of rows, say, A for A2:A16, B for A17:A31, C for A32:A46 and so on. Then it will be possible to select this additional column (Column A) as a column to split by when using the Split Sheet add-on and get the result of interest.
In case you have any questions, feel free to email us at firstname.lastname@example.org.
I'm happy to let you know that we've incorporated the feature you asked for in the add-on. The updated version of Split Sheet should be already available to you.
Post a comment
Seen by everyone, do not publish license keys and sensitive personal info!