I need help with splitting my Google Sheets data across different tabs automatically. Right now I have all my information on one main sheet but I want to create separate sheets for each category.
For example, my current data looks something like this:
A | Pizza
B | Burgers
C | Pizza
D | Tacos
What I want is to automatically create a “Pizza” tab that only shows:
A | Pizza
C | Pizza
I’m looking for a way to filter and display only matching entries on each category sheet without having blank rows where non-matching data would be. Is there a formula or function in Google Sheets that can help me achieve this kind of automatic data separation? I’ve tried basic filtering but that doesn’t seem to work across multiple sheets.
Another approach that’s worked well for me is using Google Apps Script if you want fully automated tab creation. I had a similar situation with inventory data and wrote a simple script that automatically creates new sheets for each unique category and populates them with the relevant data. The script runs whenever the main sheet is updated, so everything stays synchronized without manual intervention. If you’re not comfortable with scripting though, you could also consider using UNIQUE function combined with FILTER - first use =UNIQUE(Sheet1!B:B) to get all your categories, then use that list to create your individual sheets with FILTER formulas. This method gives you better control over which categories get their own tabs and prevents empty sheets from being created for categories that might not have data yet.
try using FILTER function! something like =FILTER(Sheet1!A:B,Sheet1!B:B=“Pizza”) should work for your pizza tab. just replace “Pizza” with whatever category you want on each sheet. works pretty well for me when i need to seperate stuff like this
I’ve dealt with this exact scenario before and found QUERY function to be more flexible than FILTER for this purpose. You can use something like =QUERY(Sheet1!A:B,“SELECT * WHERE B=‘Pizza’”) on your Pizza tab. The advantage is that QUERY automatically removes blank rows and gives you cleaner results. What’s really helpful is creating a reference cell on each category sheet where you put the category name, then reference that cell in your QUERY formula instead of hardcoding the category. This way you can copy the same formula across all your category sheets and just change the reference cell value. Much easier to maintain when you have multiple categories to manage.