Google Sheets: Create separate sheets for each value in your spreadsheet (Filter Formula)

Подписаться 3,6 тыс.

Просмотров 37 тыс.

50% 1

If you have a spreadsheet with a lot of information that you'd like to separate out into view-only tabs with only some of the data rows, use the filter formula.
#spreadsheet #spreadsheettutorial #spreadsheethelp #spreadsheets #googlesheets #spreadsheetformulas #spreadsheetfunctions #googlesheetsfilters
Sample uses:
-Separate out information by department/group/team (based on a department/group/team column in the main spreadsheet) into separate view-only spreadsheets, one for each department/group/team
-Separate out lists of projects into separate view-only spreadsheets by project status (based on a status column in the main spreadsheet
Use named ranges to make the formula even easier.
Copy Demo Sheet: docs.google.com/spreadsheets/...
Slides: docs.google.com/presentation/...
0:00 Intro
0:23 Filter Formula
0:45 Simplify the Formula with Named Range
1:30 Name the data range
1:50 Name the condition range
2:16 Name the header range
2:40 Create new sheet/tab for first filter
3:08 Add query formula to bring headers
3:30 Add filter formula to bring in filtered data
4:25 Copy the sheet and adjust for another filter
5:07 Dynamic formula - filter formula shows new data as new rows are added

Кино

Опубликовано:

30 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 51

@salmanCCIE 2 года назад

This is exactly what I was unable to do today morning. In google search results, this video was listed in the bottom of the 1st page. Adding relevant tags to it might bring it on top of the search results.

@awftutoring 2 года назад

This is superb - thank you from the bottom of the heart of this administrator!

@donavonlewis1039 4 года назад

Great explanation and VERY easy to understand. Good teacher!

@Nikhil_Bhat6 2 года назад

Thank you for the easy to understand explanation. I was able to set this up in spite of being a total newbie :)

@MohammedOmair07 Год назад

Amazing explanation, I was searching for this for the past 2 days. Thanks!!!!

@troooooper100 Год назад

OMG so simple.. i was having a mental breakdown

@Perfect2buy 3 года назад

This is what i was searching since 4 days

@jobaterah4708 Год назад

This was an awesome presentation! Thank you!

@reginaduck7652 2 года назад

Thank you thank you thank you! Exactly what I needed!

@dchennaraidu Год назад

Thanks a lot!

@rosannasuarez6593 2 года назад

so easy to understand, thank you so much

@AmanSharmaAmbalacity 3 года назад

Very Nice explanation, super helpful

@eortizs73 3 года назад

Thanks, very helpful!

@localcoupons1 2 года назад

WOW! Way to explain!

@Neocamel 2 года назад

This is a great technique for filtering data out of a master set! Can I show my appreciation by buying you a pop filter? X-D

@nickcowie8265 3 года назад

Great stuff thanks for this

@jasonlking5350 10 месяцев назад

Super well explained! Question for you! Let’s say one of the columns in your data was a date And when click the tab “ABC” Cell A2 is “07/04/2023 and Cell A3 is “08/04/2023”. These can be changed by the dept manager How do I get it to “filter” the data for ABC company to only show between those dates for The dept manager?

@DoITTrainingSBU 10 месяцев назад

add filter criteria by those dates (see the new tab here called "ABC with Dates" and the new Start date and End date on that: doittraining.page.link/copyfilterintotabs

@francisphan 2 года назад

Excellent!

@ashutoshchikane362 Год назад

Really helpful 👍🏻

@chrispage558 2 года назад

Thank you!

@andyhalliday807 2 года назад

Is it possible to make the new tab have the same formatting as well? EG: if you have used colour, or a text box, or bold text? Thanks it's a great tutorial.

@summersalt3629 3 года назад

THANK YOU!!!!

@ChetnaHospital 8 месяцев назад

Automatically update the sheet whenever a new value is entered in the main sheet?

@eljinet 2 года назад

So on Google Sheets I have this: =iferror(FILTER('RAW DATA 2'!$B$2:$G,'RAW DATA 2'!$B$2:$B>=$B$1,'RAW DATA 2'!$B$2:$B

@DoITTrainingSBU 2 года назад

Try the Query function, instead: ru-vid.com/group/PL6Xrs39_KA2bpNJGr8TFCAZkwCJnmtsuF

@RintzA0809 2 года назад

it didn't work for me, the error message "Formula parse error. " what did i do wrong?

@chic3en887 2 года назад

Quick question, what if I don't want the range to be printed

@annieneilson7538 3 года назад

This is so useful thank you!! How would I do it when the words in the column changes, but I just want it to filter if the column has any text in it at all?

@DoITTrainingSBU 3 года назад

Use this formula and leave the value in A1 empty =filter(data, depA1) means "doesn't equal", so if A1 is empty, this filter formula will return only cells that have a value in the dep range. To only show data with letters in the dep range, for the condition, use the regular expression match formula for any letters: =filter(data, REGEXMATCH(dep,"[a-z A-Z]")) The condition REGEXMATCH(dep,"[a-z A-Z]")) will only return rows with just a-z lowercase or uppercase letters in them

@DoITTrainingSBU 3 года назад

See this demo sheet, last tab: doittraining.page.link/copyfilterintotabs

@annieneilson7538 3 года назад

@@DoITTrainingSBU Thank you so much for this! Do you ever do one-to-one tuition for specific advice/training?

@MerrillGF-nq2wh 5 месяцев назад

I LOVE this! Thank you! In my case, I assign my team or the client to a task in a list of task...I have several tabs representing different types of activities...how do i use this to put the selected client tasks from the different tabs in one "Assigned to Client" list for the client to reference and complete? And same for tasks across all tabs "Assigned to Team?" PLEASE PLEASE PLEASE HELP!

@DoITTrainingSBU 5 месяцев назад

I find it easier to have one master sheet and separate out from there (rather than having disparate sheets and trying to bring them together). Get your data in one sheet and then use filter views ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-15sjPLLsP4g.html to create separate Editable views of the data, one view for each Client. This is the only way to make separate editable views :)

@woodc1014 3 года назад

What if you want a tab with more than one value for example I want to see dep ABC and XYZ on the same tab

@DoITTrainingSBU 3 года назад

Try a query formula instead, assuming you want to pull columns A, B, C, and D from original data in Sheet1!A1:F and column C has the departments, use something like =query(A1:F, "select A, B, C, D where C = 'ABC' or C='XYZ'") see more: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-W8C9TcYt47M.html

@philipcrawford3890 2 года назад

Is there a way to make a drop down for multiple departments, to keep this on one sheet?

@DoITTrainingSBU 2 года назад

Try a query formula instead of filter. See the new tabs (XYZ or ABC Query Formula and XYZ or ABC Query Formula VS) here: doittraining.page.link/copyfilterintotabs. See this video for more on Query() ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-W8C9TcYt47M.html. OR use a pivot table (see the new Pivot Table with Slicer Option tab here doittraining.page.link/copyfilterintotabs)

@brbicecream1249 3 года назад

What if I didnt want the tab to be view only? But I wanted the tab to have the live data...and if I edited it then the original data is also updated? Help please :)

@DoITTrainingSBU 3 года назад

The filter formula will show the data from the main range, even if the data in that main range is changed. So if you change a value in the main data, the filter formula will show that changed data (so long as the changed data is within the range of the filter formula). The filter formula shows view only data in that you cannot edit the data displayed through the filter.

@WheelieTruther 2 года назад

@@DoITTrainingSBU Is there really no way to do this? It's super user unfriendly in my current sheet as the user has to go back to the main one to edit the status.

@mev443443 3 года назад

Not working for me. Was there any change recently?

@mev443443 3 года назад

Just use ";" insted of "," in formula

@arpitdoshi 2 года назад

What if we want combination of abc and xyz

@DoITTrainingSBU 2 года назад

@arpitdoshi 2 года назад

@@DoITTrainingSBU thanks a lot.

@ChetnaHospital 8 месяцев назад

CAN WE MAKE THIS REAL TIME?

@troooooper100 Год назад

how do you use =SORT(....) with =FILTER

@DoITTrainingSBU Год назад

Try =query() instead: ru-vid.com/group/PL6Xrs39_KA2bpNJGr8TFCAZkwCJnmtsuF, which allows you to filter AND sort and much more (it's like SQL for sheets)

@troooooper100 Год назад

@@DoITTrainingSBU I found out, it was like this: =sort(filter(allbooks, selected="x"),1, TRUE) allbooks and selected being named ranges

@DoITTrainingSBU Год назад

Great! Query really is helpful when you want to return only some columns in a range. You can also use conditions, sort (order), and grouping; it's very powerful. For your books example, it'd be something like this (changing, A, B, C for the columns you want to return): =query(allbooks, "select A, B, C where C ='x' order by A asc")