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

1. 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.

2. 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:
     
3. 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.

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 by picking the option New spreadsheet.

     Note. The file will appear next to your source spreadsheet if you use Power Tools. A standalone version of Split Sheet will create the file in the root of your Drive.

   • Choose Multiple separate spreadsheets to place each table to a completely new file.

     Note. The add-on will create a new folder either next to a file with your original table in Drive (if you use Power Tools) or directly in the root of your Drive (if you use a standalone Split Sheet), 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. Here, you can specify a different number of rows for each sheet, e.g. 1:10, 11-50, 51-1000

   Tip. Skip the number for the last row to process all remaining rows for the last sheet, e.g. 1:70, 70:

3. Decide where to place the resulting sheets:
   
   • Have a brand new file created with the new sheets by picking the option New spreadsheet.

     Note. The file will appear next to your source spreadsheet if you use Power Tools. A standalone version of Split Sheet will create the file in the root of your Drive.

   • Choose Multiple separate spreadsheets to place each table to a completely new file.

     Note. The add-on will create a new folder either next to a file with your original table in Drive (if you use Power Tools) or directly in the root of your Drive (if you use a standalone Split Sheet), 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.

What is a scenario?

A scenario is a saved set of those options that you select on each step of the add-on. Once the data is split, you can save the options the add-on has just used into a new scenario. In the future, you can run the whole tool in just a click: by running this scenario.

Save the scenario

To save the settings into a scenario, go over all the steps, split the data, and when you see the resulting message, click Save scenario:

You will see a short summary of all options you have used just now — this is your scenario preview:

1. Give your scenario a speaking name so you always know what it does by seeing it in the menu.
2. Select the sheet that you want to split with this scenario:
   • To process a sheet where your mouse cursor stands when you run the scenario, choose [Selected sheet].
   • Or pick any particular sheet to always handle it no matter where you stand in the file when you start the scenario.
3. Set the range that you want to process using the scenario:
   • Choose [Selected range] to split all selected cells.
   • Pick [All data] to split the entire used range (all cells on the active sheet).
   • Or specify a particular range to process it each time.
4. Look through the options you're about to save to make sure everything is correct.

   Note. These settings cannot be modified here. If you want to adjust some of them, you will need to restart the add-on and fine-tune the options while going through the steps.

Once you're ready, click Save.

Run your scenario

To start the scenario, go to Extensions > Split Sheet > Scenarios, select the required scenario and click Start.

Split Sheet will begin splitting data according to the scenario settings.

Once it's done, you will get the result message saying what scenario has just worked and what it's processed:

Edit or delete scenarios

To review the scenario setting or to change the sheet and/or the range it handles, go to the same Extensions > Split Sheet > Scenarios menu, pick the scenario and select Edit:

You will see the whole scenario outline again:

You can rename it and change the sheet and the range to process.

If you make any changes, press Save to keep them. Click Run to start the scenario with the new changes right away, or hit Delete to remove it completely.

Share scenarios

You can easily export one or more scenarios and share them with your teammates or sync across your different Google accounts.