While you may spend a lot of time combining data in Google Sheets, you may also need to split the data into different sheets. In Google Sheets, it’s possible to split rows of data into various sheets based on specified criteria. For instance, we could separate the following list of companies based on their headquarters. As seen in 📺this video, there is more than one way to achieve our intended end result.
Splitting rows in Google Sheets with the FILTER function
Let’s start by creating a list of companies headquartered in Australia.
- Create a new sheet and name it “Australia”.
- Copy the header row from Sheet 1 and paste it to the new sheet.
- Next, we’re going to use the FILTER function to give us a list of only Australian-based companies. The syntax we should follow is =FILTER(range, criteria). For us, we want to return all the values in the range Sheet1!A2:C18, provided this criterion is met: column C is equal to Australia. Therefore our formula would be: =FILTER(Sheet1!A2:C18,Sheet1!C2:C18=“Australia”)
- If we plug this formula into cell A2 of the sheet, Australia, we get the following output:
We successfully split rows of data in Google Sheets! The cells containing the word Australia are now in a separate sheet, but what if we were listing all companies worldwide? Would we have to manually create over 200 sheets and tweak the FILTER function accordingly? No we wouldn’t because there’s a powerful tool (excuse the pun 🙂 ) known as Power Tools that can do the heavy-lifting for us.
Splitting Rows using Power Tools by Ablebits
Once Power Tools is installed for the first time, a sidebar should appear on the right side of your sheet. If the sidebar doesn’t appear automatically, you can launch it via Add-ons > Power Tools > Start. After that, make sure you’re on Sheet1 and click on SPLIT in the Power Tools sidebar.
Upon clicking, a set of options should appear, giving us various choices of how we want to split the data. In our case, we want to split an entire sheet so that’s what we’re going to select.
Now the only thing that’s left is to specify the criteria by which we wish to split our data and the destination of the split sheets.
We get this as the output once we click on “Split”:
The entries have been separated into 3 tabs automatically. If we had 20 or 50 tabs, that’s the number of tabs that would appear.