The last couple of years MS finally woke up in enhancjng the functionality of many of their tools. All of this Power stuff makes data life a lot more fun and easier. Video’s like these are truly valuable. Although I always state nothing can beat assembly language, you just made my day 🎉

You are a wizard…..loving all these tips on functionality that most people just wouldn’t find…thank you!!

Great tutorial as usual! Thank you Mynda once again for all the help you offer! Stay blessed!

Exactly filled the need I had. Thank you for the great tutorial!

Hi Mynda. Another awesome lesson! Thanks for the tips and for always sharing the practice file.. very helpful! Thumbs Up!!

Hey there! I just wanted to drop a quick message to say thank you for the awesome tutorial. It was super easy to follow along and really helpful.👏👏👏

You are a miracle in the world of Magical Excel ❤This was exactly what I was looking for

This was a game changer! Thank you for the tutorial! You gained a subscriber.

Just the information I needed exactly when I needed it, thank you!!

I have to say that you and excelisfun are the explainers. Thank you.

This is excellent, once again just what I was looking for! thank you

it started so simple with control+t and ended up with choosecols(cstack,filter) I loved it!

Hi Mynda!Great Tutorial,Loved Both Solutions...Thank You :)

Great work Mynda! I wish I was in a job right now, where I could apply all these skills and learnings!

Most favorite Excel Teacher

Thanks the combine method was great and clear to understanding.

Another great video Mynda 👍🏻

Thanks for the great video! This was exactly what I was looking for 😊

Thanks for the tutorial Mynda ✌.

Helpful guidance for a problem I frequently face!

Outstanding dear Mynda! Best regards from Limón, Costa Rica!

Fantastic... Thanks Mynda

Wow, This is an absolute God send. Thank you

This is my go to Excel channel.

Awesome Mynda!

Hi Mynda, very informative video related to pivot table

Your videos are great, thank you!

Thanks for you useful effort and made these videos😀

This is fantastic as always!!! 🤔Can I use the same method to gather data from multiple sheets/workbooks to one master sheet so I can create a dashboard report?

Hi Mynda, Thank you for the clear explanation. A question? What if the base table uses colors to highlight certain fields. How do I get those highlights with the 'filter' function in the result selection?

Like, regards from Cali-Colombia

Great video. Thanks!

This is really good!

always good knowledge received.

Nice! I had no idea PivotTables could do that.

Super useful! Thanks!!

Hi Mynda, great video. Thank you. I had no idea that pivot table can do that. I have a question about adding new data. Every time we refresh to bring in new data, does the row order of existing data remain the same with only new data appearing at the bottom? I want to use pivot table to split up ERP transactions by ledger accounts into each sheets. From each split pivot table, I have some workings in columns to the right of the pivot. I just want to make sure when new data is added, they go down the bottom where I haven't done any workings, so that the workings for the existing rows remain correct and the order is not mixed up. Appreciate any thoughts on have on this. Cheers

Thank you for this excellent video! I added the double quotes at end [ =VSTACK(Complete[#Headers],FILTER(Complete,Complete[Person Covering]=Y2,"")) ]. I have some date fields on my source tab that are blank by design but the formula filled it in the field with date "01/00/00" when it filtered it over to my individual sheet. Any thoughts on why or how to fix that?

Thank you, you have explained in one old video as well. But thanks again :)

Very useful information.... thank you... Can you also upload video doing the same task using some macro please

You could always take the formula approach as step (or more) further. If I were feeling contentious, I could argue the case that any formula that is worth writing could be turned into a Lambda function. The name describes what it does, and the parameters identifies the precedents. All the detail is decently hidden, unless one chooses to delve deeper. = FilterTableλ(SalesTbl[#All], fieldName, selectedValue) allows the user to select the column they wish to filter on but stops short of becoming a full-scale FILTERIFS function able to parse date inequalities for example. FilterTableλ = LAMBDA(table, fieldName, selection, LET( header, TAKE(table, 1), dataTbl, DROP(table, 1), criterion, XLOOKUP(fieldName, header, dataTbl), filteredData, VSTACK( header, FILTER(dataTbl, criterion = selectedValue, "") ), otherFields, FILTER(filteredData, header fieldName), otherFields ) ); Then again, that might be a way of giving many of your followers severe indigestion! 😅

Hi Mynda, Great video. I use Pivot Tables to extract columns from a source data table to a "rearranged columns" dataset. In other words, I only pick the columns that I need. Should I try using Power Query to rearrange the columns? What do you think? Thank you!!

I love this functionality - I only have an issue; I would need to keep the Conditional Formatting created in the original Pivot (i am using Icons to signal visually if a course is complete, in progress or only enrolled) - is it possible?

Will this pivot table from master to separate worksheets function work in earlier versions of Excel? Also, if I were to use it, I would need each separate pivot table subtotal to link to another workbook: is this possible, given that the tables are expanding after master inputs are made? Thanks

Thanks for this tutorial on a "not-so-famous" function in a pivot table that helps us to create separate sheets. Now I was wondering if there is an easy way (without VBA) to split multiple sheets (of a single file) into separate Excel files ? Thanks in advance.

Hello, I’m so grateful for this video as I was having a hard time finding a solution! However, when one of the sorted sheets doesn’t have any entries, it pulls data from another sheet for some reason…any thoughts?

Thank you. It useful. But when it is required to be editable by the each filter sheet, I think it must be converted to be a range of data before sending to another work colleage. Thank you again.

Thanks ¡¡ very useful tutorial ¡¡

Wonderful tutorial, thanks! Where does one embed a SORT or SORTBY function so you can sort the data? Example: =CHOOSECOLS(VSTACK(Table1[#Headers],FILTER(Table1,Table1[New Assign?]=Sheet6!C4,"")),2,5,6,8,9)

Wow! I never used CHOOSECOLS, great input! Thanks, Mynda!

Hi There is another option, which we use and that, once set up, is beautifully simple. It requires refresh, but is Power Query to filter and load to separate tabs.

Once you've separated the date into separate tabs, does the data in the separate tabs also become modified as you're making changes to the "Master" table with the original data?

Hi this is great...But if I create more than one pivot table on the first sheet can I get them all to transfer on to the separate sheets made...Thank you

Exactly what I need

If add column D data on the new sheet for example for row 4 (so there are is some info in E4, just after "882") and then update source sheet (for example change county ) so old row 4 values are changed in the new sheet, it still would have my data in E4 but now it will be in "related" to the new row4. Is there any way to remove e4 data if original row is not there without vba? Sorry if confused you. Basically I have scenario where I need to add some information to a table which is subset of another table just filtered by one of the cells. And trying to keep all that added information relevant to their respective rows in case source table was modified.

Thank you so much!🙏

Thank you so much for the great lesson as usual! I have a question on data validation (list). How can i do that if the source of data is in another sheet? Thank you so much!

Great video! My generated sheets are named generically instead of based on the filter value...any idea what setting I need to change to fix this?

brilliant again

Very much helpful Maam

Very Nice, how to get total of values at the end using filter formula.

You are the Best

Hi! Would you be able to advise me on the best way to have a master excel file and several separate files based on filtered data with in the master file. I would need seperate files not just different sheet tabs. Many thanks!

After using filter function or pivot table and make different reports in different sheets, I need to add some data belong to columns which filtered. When I add data next month to my base data, filtered data will refresh but data which I added beside filtered columns does not go down and remain in the first rows which now are not for data of new month. What can I do? Best Regards.

Hi Miranda, one question :) when I extract separate sheet for each salesperson for some of salesersons adds 2 tabs. What am I doing wrong?

Hi, not sure if you’ll see this but do you know how to extract qualitative data? I want to get it from one sheet to another. Say, hypothetically, I have a large set of data with labels within the data such as “good player, bad player, right footed, left footed” is there a way I can extract the all table entries using a key word such as player? I might’ve explained that poorly

Hi! How would I be able to remove the pivot tables from the resulting output sheets all at once without VBA? I would also be grateful to know how you prevented the date from splitting into Year and Quarter like my data?

I'd like to make myself a sheet that pulls in the next steps for multiple projects, say the next 5 steps and their due dates. Each project timeline is a separate file, I'd just like a summary or overview so I can see the upcoming steps and ideally, the submission steps and due dates, for each project in one place. Which functions should I be using/learning?

hi how do i do reverse update that is update the master sheet by updating the other sheets basically I want to have people work on there own sheet and my source data gets updated

how about using filter formula and the source data is in another workbook? will that work?

Hello, Is there anyway to saw details report in One Excel Tab rather then Opening Multiple Excel tabs for each selection ?

Thank you

We have data where country wise reporting is required and if use the above solution although I have created different sheets based on country but it just filtered... Still privacy is lost where one country gets to know how the other country or region is performing by removing filters.... Can we just have the data split into different sheets based on select criteria without displaying other regions information

Thanks

What if one wished to start with a master table, split into tabs by salesperson, but once the individual tabs are set up, have each salesperson update their own tabs and have that feed the updates back into the master?

Is there a way to bring over the formatting?

very very good

Hi the show report filter pages in my option is greyed out,would you please tell me why? 🙇‍♀️

Why does show report filter pages gets turned off in a pivot, when u click on "More tables" and create a pivot? How to turn that on again?

Definitely good for someone with more in depth knowledge. Personally I found the flow hard to follow.

It says I have too many unique items for a pivot table. What should I do?

this is perfect. what if there is a new salesperson. how can a new tab automatically be created?

No matter how many times I tried, when I add the new data and tried refresh. It doesn't work for me at all for my MS Excel 365. I am a bit disappointed. Even following to your video to the T. The filter tables doesn't show the new data even though the pivot table has the new data. :(

how to export multiple files based on data validation list without having to choose each item in the list everytime is there a way ?

I want to the name the tables automatically from the table order no automatically...how to do this

Hi - this is a great video, but when a new category of data is added (say a new sales person) my pivot table picks up that new person, but doesn't create a new sheet for them. Help! (my knowledge of Excel is thinner than a strand of hair)

My Xcel doesn't show that option. It's blanked out. Kindly help. it's Excel 365. Thank you

why would you not use an importrange query here ?

Very useful. I tinkered around and came up with this monstrosity so we can sum the extracted data. =VSTACK(CHOOSECOLS(VSTACK(Table1[#Headers],FILTER(Table1,Table1[Salesperson]='FILTER Formula'!C4,"")),1,3,4,5),HSTACK("Total","","",SUM(FILTER(Table1[Order Amount],Table1[Salesperson]='FILTER Formula'!C4,""))))

I like formulas because it is Dynamic

Is anyone else struggles with the formulas, because mine just never work... i follow everything step by step and always have the same issue #value error ... literally spend a whole day trying to do this simple filter function but excel aint taking it!

Ctrl Z to ungrouping is not working for me.

btw we can only watch it in the future 😊

You do know that in your header, you misspelled "Extract" as "Exctract", right!

Hi Mynda Many thanks for this. You've just shown me a great tip with CHOOSECOLS(). I've been using INDEX() for this, which is more fiddly! CHOOSECOLS() also enables you to change the order of the output columns, which is very useful.

This is useful, but what if I also have to send these sheets seperately to the customer using Outlook? Please Help!!