Split Google Sheets table into multiple sheets

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 Common values 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:
  

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.
4. Toggle the last setting on to insert each split table as a formula. This enables each resulting sheet to update automatically once something changes in your original data set.

   Note. This is a beta feature. Your feedback is eagerly anticipated and much appreciated! 🙂

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.

Limitations of standard functions used in the formula

Along with our own custom functions, the resulting formula uses some standard ones including QUERY and IMPORTRANGE. Here are some things you should know about the result returned by the formula:

1. If you place the formula to a new spreadsheet or multiple separate files, our custom functions (COLUMNS_SGS, HEADERS_SGS, SPLIT_RANGE_SGS) won't start working until you run Split Sheet once. Since those files are newly created, this way you enable our custom function there. Do that before granting the IMPORTRANGE access and you won't have to reload the spreadsheet as well.
2. Formulas won't work without our add-on. Whether you use the tool to create formulas or duplicate yourself shared files with formulas, you should have the add-on installed. Otherwise, there's just nothing to call for our custom functions with.
3. The key records you're splitting by must not be blanks. Otherwise, too many empty rows will appear in the result.

   Note. Make sure there are no completely empty rows within the table — this will also affect the result.

4. If there are mixed data types (e.g. numbers and text) within a column, the QUERY function will return only the majority data type leaving the rest as empty cells.
5. Due to the way Google Sheets processes formulas, the number of columns in the outcome table is always fixed. It means that new columns added to your source tables (both within & at the end) won't appear in the result — Google Sheets will not adjust the ranges.
6. Google Sheets functions cannot process and take any objects (e.g. images, charts, conditional formatting, etc.) but values to a resulting table.