Split Google Sheets table into multiple sheets

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.

• Before you start
• How to use Split Sheet
  • Start the add-on
  • Step 1: Select your table and a way to split it
  • Step 2 (A): Select columns to split by
  • Step 2 (B): Select rows to split by
  • See the result
• Related pages

Before you start

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.

How to use Split Sheet

Start the add-on

Open the sheet you want to split and go to Extensions > Split Sheet > Start:

Step 1: Select your table and a way to split it

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.

Tip. Or click the Select range icon within the Source range field and specify the exact range to process:

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:

• Go with Selected columns to group the data based on the common info in the specified columns:
  
• Or opt for Number of rows if the size of each dataset is more important for your task:
  
  Note. At the moment, splitting by a number of rows is available only in the standalone Split Sheet. You won't find this feature in Power Tools just yet, but we're working on implementing it there as well.

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.

Step 2 (A): Select columns to split by

If you chose to split your Google sheet by columns on the previous step, they will appear grouped in the table:

1. Use the checkbox in the header row to quickly select all columns and group data by records they all contain. The 1st row content will hint on the values in those columns.
2. You are free to choose any particular columns to split by. If you check more than one column, the add-on will create new sheets based on the common info in all selected columns:
   
3. Decide where to place the resulting sheets:
   
   • Have a brand new file created with the new sheets (in your Drive next to your source spreadsheet) by picking the option New spreadsheet.
   • Choose Multiple separate spreadsheets to place each table to a completely new file. The add-on will create a new folder next to a file with your original table in Drive, and put all these new spreadsheets there.
   • Or select Current spreadsheet to insert all new sheets to the same file where your source table is.

When you're ready, press Split.

Step 2 (B): Select rows to split by

If you chose to split your Google sheet by N rows on the previous step, here you will need to specify those numbers:

1. Pick Every N rows to split the Google sheet by the equal number of rows. In other words, your table will be divided by every number of rows you specify.
2. Or make use of the other radio button to enter certain groups of rows, even those with a different number of rows, e.g. rows 1:10, 11-50, 51-1000.
   Note. Make sure the row ranges here don't fall outside the selected table on Step 1. Hence, if your table ends with row #100, the last correct range will be 50-100, not 50-1000.
3. Decide where to place the resulting sheets:
   
   • Have a brand new file created with the new sheets (in your Drive next to your source spreadsheet) by picking the option New spreadsheet.
   • Choose Multiple separate spreadsheets to place each table to a completely new file. The add-on will create a new folder next to a file with your original table in Drive, and put all these new spreadsheets there.
   • Or select Current spreadsheet to insert all new sheets to the same file where your source table is.

When the numbers are set, hit Split.

See the result

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.

Related pages

• Split values by Characters
• Split cells by Position
• Split Names
• Combine Sheets
• Merge and Calculate Sheets