Extract Data to Separate Sheets the Right Way!

[@esltube]

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 🎉

[@MyOnlineTrainingHub]

So pleased you liked my video 🙏

[@DebbieRuston-n8s]

You literally just read my mind. I created data in 3 separate tabs for fiscal years and then a combined file of all fiscal years. My boss then edited the combined file to add columns, etc. and I thought "Oh, now what do I do for new data??" Now I know!! Thank you for years of my life I just got back!!!

[@MyOnlineTrainingHub]

Glad it was helpful! 😁

[@jms9057]

I manage registration for a youth sports group, and I can pull a master report to figure out who still needs to give us what information or forms, etc. It is constantly-changing data, and it gets split out by division and team so that coaches can be given lists of what their players still need before they can get equipment or participate. You have saved my evenings and weekends!!!!!!! I cannot thank you enough for this video!!!

[@MyOnlineTrainingHub]

Aw, that's wonderful to hear 🥰

[@rwfrench66GenX]

You are the bomb! Thank you so much! I work in a very large AP department and I reconcile our two largest suppliers. Our buyers log into their websites and place orders but they also have to approve those orders before they get added to the file that gets uploaded into our payment processing system. Our contracts say we have 7 days to pay them or we start paying an 18% late fee. I send out a spreadsheet a few days before the next statement will come out so the buyers are reminded of what invoices still need to be approved. I’ve been using the filter formula but we don’t order from the same locations every week and as a healthcare company our facilities are bought and sold quite often and there’s also a turnover in employees so who gets the report from week to week changes. This pivot table option is much easier and will save a great deal of time! Cheers 🍻

[@MyOnlineTrainingHub]

So pleased to hear this will be helpful 😊

[@mirasrenda]

Mynda, I love your tutorials, thanks for them! Just a note to the PROS/CONS section. It would be worth to mention as CON for the filter-approach (as well as many other array producing functions) is its speed. If there are many array formulas and/or the source table is big, work with the workbook may be very slowed down by long recalculation.

[@MyOnlineTrainingHub]

Good point on FILTER. I'll add it to the blog post as a Con.

[@therealjeffg]

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

[@MyOnlineTrainingHub]

Thanks so much, Jeff!

[@anthonievanriessen]

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

[@MyOnlineTrainingHub]

Wow, thanks so much 😊

[@gregbernard7861]

This is my go to Excel channel.

[@MyOnlineTrainingHub]

Wow, that's awesome to hear, Greg 🙏

[@anyandeveryistaken]

Most favorite Excel Teacher

[@MyOnlineTrainingHub]

Wow, thank you, Nahas!

[@sacstatesfinest]

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

[@MyOnlineTrainingHub]

Awesome, thank you!

[@jerrymiles7804]

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

[@MyOnlineTrainingHub]

Thanks so much, Jerry!

[@jerrymiles7804]

😘

[@michaelkoster8412]

Very nice topic and explanation! I always enjoy your videos. Thank you.

[@MyOnlineTrainingHub]

Thanks so much! 🥰

[@Akash-py3yi]

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

[@MyOnlineTrainingHub]

Awesome to hear!

[@Fabi_terra]

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

[@MyOnlineTrainingHub]

So great to hear! 🙏

[@paolodigennaro7725]

supercool approaches, i personally prefer pivot tables adding the Worksheet_PivotTableUpdate functionality on a module

[@Cat24714]

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?

[@MyOnlineTrainingHub]

You can update the data on the separate tabs by clicking the Refresh All button on the Data tab of the ribbon.

[@darrenvanslyke7759]

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)

[@MyOnlineTrainingHub]

Glad it was helpful! Sort would have to wrap around filter, so the header row isn't sorted. e.g. =CHOOSECOLS(VSTACK(Table1[#Headers],SORT( FILTER(Table1,Table1[New Assign?]=Sheet6!C4,"") ) ),2,5,6,8,9)

[@shoppersdream]

Very Nice, Mynda! Mine is Filtered Values which I wanted to paste on a different sheet. Thanks

[@MyOnlineTrainingHub]

Glad I could help.

[@TheOscarProject1]

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

[@MyOnlineTrainingHub]

Great to hear!

[@hieunguyen-dd1nm]

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.

[@MyOnlineTrainingHub]

Yes, if you don't need it to always be linked to the original source, then you can copy and paste it as values.

[@darrylmorgan]

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

[@MyOnlineTrainingHub]

Great to hear, Darryl!

[@vijayarjunwadkar]

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

[@MyOnlineTrainingHub]

Thanks so much, Vijay!

[@PaulEBrownbill]

Very informative, thanks Paul

[@MyOnlineTrainingHub]

Glad you found it helpful.

[@sachin.tandon]

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

[@MyOnlineTrainingHub]

All the best for your job search. Keep learning. It'll pay off.

[@sachin.tandon]

@@MyOnlineTrainingHub Thanks Mynda!

[@rezaamini9830]

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.

[@MyOnlineTrainingHub]

I would use Power Query to add the columns to the source data so that it's integral to the PivotTable and automatically pulls through. If you have further questions, post your question and sample Excel file on our forum where someone can help you further as follow-up replies here won't be seen due to the volume of comments I receive: www.myonlinetraininghub.com/excel-forum

[@rezaamini9830]

@@MyOnlineTrainingHubThere is no problem with my base data which are updated each month. But every month in base data, we will assign each rows to one person to analyze and I have used "Filter function" to show assigned data to each person in different sheet names. For analyzing, they need to add columns to filtered data, such as data validation filling, comment,... and I need to relate theses added columns to filtered array. So every month I will not have problem with updated data and I can upload the analyzed data to Power BI. Would you please help me to understand the right way of doing this. Best Regards

[@Seftehandle]

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

[@MyOnlineTrainingHub]

😁 great to hear, Tina!

[@simonmassen]

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

[@MyOnlineTrainingHub]

Awesome to hear!

[@tobiewaldeck7105]

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?

[@MyOnlineTrainingHub]

There's no way to automatically remove PivotTables from multiple sheets without VBA. You can press CTRL+Z immediately after adding the date field to the PivotTable to undo the grouping into years, quarters, months etc.

[@tobiewaldeck7105]

@@MyOnlineTrainingHub Thank you.

[@joshnoteboom6479]

This is a great tutorial. I did run into one issue, if I were to add a new “sales person“ with a different name than that name does not get added to a new sheet. Is there any workaround for this?

[@MyOnlineTrainingHub]

Glad it was helpful. No, if you add a new item, you have to generate that report separately, or create them all again.

[@roweboy1974]

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?

[@MyOnlineTrainingHub]

Change the cell formatting to hide zero dates. e.g. d/mm/yyyy;; or m/d/yyyy;; The two semi-colons on the end are important.

[@petermarcussen6265]

is there a way automate transfer of data from one sheet to a master sheet. i.e i want to add data from all cell in a row on a sheet to a master sheet, but only if a specific cell in the row has information added to it? I hope my question makes sense.

[@MyOnlineTrainingHub]

I would use a PivotTable to extract data from your master sheet to your other sheets based on criteria as explained here: www.myonlinetraininghub.com/excel-pivot-tables-to-extract-data

[@Prafful000]

How do I provide data to each individual separately and gather their updates to the data into my master data set? Please advise.

[@MyOnlineTrainingHub]

I would create a separate file for each user containing a table that they can maintain. You can then use Power Query to consolidate the tables from each user's file into a master dataset: www.myonlinetraininghub.com/power-query-get-files-from-a-folder

[@GiuseppeDucaDiParma]

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

[@MyOnlineTrainingHub]

Yes, it works in all currently supported versions of Excel. I don’t recommend external links (too easy to break them). Instead, use Power Query to connect to the PivotTable source data and extract the totals into the other file/s.

[@wayneedmondson1065]

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

[@MyOnlineTrainingHub]

Glad you liked it, Wayne!

[@klinsmannlanghanz6507]

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

[@MyOnlineTrainingHub]

Glad you liked it!

[@VincentSlootmans]

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?

[@MyOnlineTrainingHub]

You can use Conditional Formatting to automatically apply colours based on a condition: www.myonlinetraininghub.com/excel-conditional-formatting-with-formulas

[@VincentSlootmans]

​@@MyOnlineTrainingHub In my question I mean that the cells in the 'base table' are colored manually and not based on a mathematical calculation rule. It seems that the function 'Filter' does not include the formatting of the cells in the base table.

[@BABYDOVIO]

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?

[@MyOnlineTrainingHub]

No, unfortunately it's not possible.

[@ghargettCarolina]

Love your channel. How do you select data from a huge .csv file to only get rows for the current year (there is a field for that)? I've not discovered how I can do that with Data Model or Power Pivot. Additionally, if you select a second year, can we separate it into two different sheets?

[@MyOnlineTrainingHub]

I would use Power Query to do this, that way you're not bringing data into your file that you don't need. You could separate the query to generate two separate sheets/tables for the different years, but I would advise against this as it will prevent you comparing the year on year data and other comparative analysis you might want to do.

[@glennhargett2243]

@@MyOnlineTrainingHub THANKS!

[@reg_inforeg_info3691]

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.

[@MyOnlineTrainingHub]

I'd use Power Query to rearrange the data into a subset of the two tables. Introduction to Power Query here: kzbin.info/www/bejne/gmWlpoiwmMh_ptE

[@andrewsumithsylvester9095]

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

[@MyOnlineTrainingHub]

If you want it in one report, then move the field from the Filters area to the Row/Column labels.

[@barrymeredith-m4q]

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!

[@MyOnlineTrainingHub]

I'd use Power Query to get the data from the separate files and bring it into the master file as shown here: www.myonlinetraininghub.com/introduction-to-power-query

[@barrymeredith-m4q]

@@MyOnlineTrainingHub Thanks for your reply, I was actually meaning the other way around. So I'm wanting to have one master excel file which gets new data added to daily. The master file then acts as the source of data for around 20 smaller files which will be used by different people. I'm not sure of the best way to create these 20 smaller excel files that update when the master excel file is updated. Does that make sense?

[@amitlibby]

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

[@MyOnlineTrainingHub]

You can't edit PivotTables, so you'd have to use Power Query to consolidate the individual sheets: www.myonlinetraininghub.com/power-query-consolidate-excel-sheets

[@SGSubra]

Wow, This is an absolute God send. Thank you

[@MyOnlineTrainingHub]

Glad it was helpful!

[@didotbasmayor]

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

[@MyOnlineTrainingHub]

No, FILTER can't reference a closed workbook, so you'd have to have the other workbook always open. Better to use Power Query to bring the data into the file you want it in: kzbin.info/www/bejne/gmWlpoiwmMh_ptE

[@didotbasmayor]

@@MyOnlineTrainingHub thank you so much for your quick reply

[@balajisimeon4019]

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

[@MyOnlineTrainingHub]

Glad you liked it! I'll think about the Macro version, but no promises.

[@marysoto4815]

Is there a way to bring over the formatting?

[@MyOnlineTrainingHub]

No. You'd have to write some VBA to apply the formatting after extracting the sheets.

[@maldrich-sarafianos158]

this was amazing - thank you.

[@MyOnlineTrainingHub]

Glad it was helpful!

[@jisoospurple2627]

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

[@MyOnlineTrainingHub]

Not sure what you mean. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@DannyFox-y2k]

Hi if one of my rows contains a link to external documents can I still use this?

[@MyOnlineTrainingHub]

You can, but the link won't be brought through to the extracted PivotTables, which probably isn't what you want.

[@inebr2368]

Thanks, but my Show Report Filter Pages is greyed out - HELP! FYI - My pivot is based on a connection from an CSV file.

[@MyOnlineTrainingHub]

It’s not available when you load the data to the power pivot data model.

[@inebr2368]

Unsure how i managed to solve it, but it is now solved. hehe...

[@ddp2049]

Thanks the combine method was great and clear to understanding.

[@MyOnlineTrainingHub]

Glad it was helpful!

[@KV21A]

Thanks for the tutorial Mynda ✌.

[@MyOnlineTrainingHub]

You’re welcome 😊

[@prosserlm]

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?

[@MyOnlineTrainingHub]

Hmm, not sure why that would be. Might be an Excel version difference. I'm using 365.

[@hhasanat]

Thank you so much for your beneficial tutorial. I just wanted to ask about the combo box, how did you add it?

[@MyOnlineTrainingHub]

Not sure what combo box you're referring to, but I suspect it's part of the PivotTable and if so, it's automatically inserted when you put a field in the PivotTable Filters area.

[@hhasanat]

thank you for prompt replying The combo box I mean the one you in use to choose the salesperson

[@MyOnlineTrainingHub]

Yes, that's part of the PivotTable. i.e. the Salesperson field is in the Filter well.

[@myadventuresinflight]

​@@MyOnlineTrainingHubI believe the question is referring to your data validation list on the sheet where you used the filter formula. Perhaps linking your video where you teach the data validation to create a filter selection dropdown would answer the question.

[@myadventuresinflight]

​@@hhasanatcheck out Mynda's data validation dropdowns. That's how she made the list of salespeople on the sheet with the choosecols, vstack, and filter formula.

[@besslau3570]

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

[@MyOnlineTrainingHub]

I suspect you added your data to the data model (aka Power Pivot) when creating the PivotTable. This functionality is only available for regular PivotTables.

[@amykoszalkahhroofingaccoun6454]

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

[@MyOnlineTrainingHub]

Glad it was helpful, Amy!

[@kimbui1]

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

[@MyOnlineTrainingHub]

PivotTables by default sort alpha/numerically. If you want to sort in a fixed order, it's easiest to add an index column to your sort data and put that as the first field in your row labels.

[@grahamparker7729]

Another great video Mynda 👍🏻

[@MyOnlineTrainingHub]

Thanks so much, Graham!

[@imapkru]

How are your selected rows ending up in columns?

[@MyOnlineTrainingHub]

You build the PivotTable you want, dragging the fields to the row, column and values areas, then generate the report filter pages.

[@charlesthomas183]

How would you add a sort function to the formula in 365 to sort from highest to lowest sales.

[@MyOnlineTrainingHub]

You can sort the PivotTable before extracting.

[@saumyasurendran6928]

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?

[@MyOnlineTrainingHub]

If you have the option to see 'More tables' then you are working with Power Pivot PivotTables and these do not have the same functionality as regular PivotTables. You will have checked the 'load data to data model' box when creating your PivotTable, which is Power Pivot.

[@emmanuelle8032]

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.

[@MyOnlineTrainingHub]

Glad it was helpful! Unfortunately, there's no non-VBA way to automatically split multiple sheets into separate Excel files.

[@othompson23]

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?

[@MyOnlineTrainingHub]

Hmm, that's odd. I haven't seen this problem. It should return the filtered sheets based on the filter irrespective of sorting.

[@ankitkarwa3548]

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

[@MyOnlineTrainingHub]

You'd need to use VBA to automate that part: www.myonlinetraininghub.com/automating-emailing-pivot-table-reports (or Power Automate)

[@ankitkarwa3548]

@@MyOnlineTrainingHub thank you so much for sharing your view. Can you share a video on power automate? This would be very useful for all those working with sales report.

[@winxclub8045]

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

[@MyOnlineTrainingHub]

You'd have to write some VBA to do that. There's nothing built into the PivotTable creation process to allow for that.

[@Goodexcerpts]

Brilliant. Thank you so much!

[@MyOnlineTrainingHub]

You're welcome! Glad you found it helpful.

[@mehdihdr1]

Hi Mynda, very informative video related to pivot table

[@MyOnlineTrainingHub]

Glad you think so, Mehdi!

[@SD-ko4jd]

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

[@MyOnlineTrainingHub]

I have? I don't recall. Which one?

[@SD-ko4jd]

@@MyOnlineTrainingHub I have to check but I believe you have taught this. But nevertheless its good, may be I am mistaken because I know this.

[@MyOnlineTrainingHub]

@@SD-ko4jd I wrote about this back in 2013, but I don't recall doing a video on it.

[@alexnjsc]

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

[@MyOnlineTrainingHub]

You can either run it again but it will generate all the sheets again or for just one, copy a sheet and change the salesperson in the filter.

[@alexnjsc]

@@MyOnlineTrainingHub I SEE! THANK YOU!!!

[@bobf1267]

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

[@MyOnlineTrainingHub]

I'd try using Power Query for this. If you get stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@HowToAnalyst]

Helpful guidance for a problem I frequently face!

[@MyOnlineTrainingHub]

Glad I could help, Pete!

[@chriswall4795]

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.

[@MyOnlineTrainingHub]

Hmmm, I'm not convinced using Power Query is as easy because you have to create separate queries one by one...manually. Even if you copy one query and edit it, it's still manual. I guess once it's done, you can refresh...but you can do that with the PivotTable too and you haven't had to do all the upfront set up work.

[@bukainka]

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

[@MyOnlineTrainingHub]

I suspect you have some entries with a trailing space on the end of the salesperson's name, so they're not technically the same. Check the source data.

[@Atabei]

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?

[@MyOnlineTrainingHub]

Sounds like Power Query will be of use to extract the data from the separate files into a summary file: kzbin.info/www/bejne/gmWlpoiwmMh_ptE

[@Atabei]

@@MyOnlineTrainingHub Thank you! 😁

[@anuragbaldawa7242]

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

[@MyOnlineTrainingHub]

Excel wasn't designed to segregate data at a user level. You'd be better to use Power BI as it has Row Level Security built in.

[@peterbartholomew7409]

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! 😅

[@MyOnlineTrainingHub]

😁 thanks for sharing, Peter! I love to see advanced techniques like this.

[@muhammedps8066]

could you tell me how to automatically add a new sheet when new name is added

[@MyOnlineTrainingHub]

You'd have to write a VBA macro to do that.

[@extraktAI]

Great video, thanks a lot!

[@MyOnlineTrainingHub]

Glad you liked it!

[@akancal6596]

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

[@MyOnlineTrainingHub]

It's only available if you put a field in the Filters area of the PivotTable.

[@rezaamini9830]

Thanks for you useful effort and made these videos😀

[@MyOnlineTrainingHub]

You're most welcome 😊

[@ctgoedyXD]

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

[@MyOnlineTrainingHub]

Wow, I've never seen that before. Maybe try loading the data to the Data Model to see if it can handle it better.

[@daimpi]

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

[@MyOnlineTrainingHub]

I'm so glad! 😊

[@ADfamily81]

Your videos are great, thank you!

[@MyOnlineTrainingHub]

Glad you like them!

[@amoebotspaceship5476]

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?

[@MyOnlineTrainingHub]

You can't edit a PivotTable value fields, so this wouldn't work. Perhaps you could use Excel forms and capture the data, have it update the PivotTable source data: www.myonlinetraininghub.com/gather-data-with-excel-forms

[@stephencooper3583]

Nice! I had no idea PivotTables could do that.

[@MyOnlineTrainingHub]

Glad you liked it, Stephen!

[@meditationrelaxation4786]

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

[@MyOnlineTrainingHub]

You'd have to use VBA for this.

[@meditationrelaxation4786]

@@MyOnlineTrainingHub any suggestions where to find that Vba code ? thanks :)

[@christina1796]

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!

[@MyOnlineTrainingHub]

You can reference the other sheet as the source of your data validation list. Just click in the source field and then click on the sheet tab containing the list, and select the cells.

[@christina1796]

@@MyOnlineTrainingHub Thank you so much! what if the list in one sheet is dependent on the data in the other sheet? I give an example: i have two cells with drop down list : type of condition (sheet 1cell one), list of the type of condition (sheet 1cell 2)while data is in another sheet (sheet 2). I appreciate your advice on that.

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@cat91264]

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)

[@MyOnlineTrainingHub]

You'll have to create the new sheet manually, or recreate all the sheets again.

[@cat91264]

Thank you!

[@stuontwo677]

why would you not use an importrange query here ?

[@MyOnlineTrainingHub]

Because this is Excel, not Google Sheets.

[@TheMostObliviousGirl]

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. :(

[@MyOnlineTrainingHub]

I suspect your source data isn't in an Excel Table, so the PivotTables don't realise there is new data added. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@jasonroy4131]

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

[@MyOnlineTrainingHub]

You might find the step by step written instructions easier to follow: www.myonlinetraininghub.com/excel-pivot-tables-to-extract-data

[@ntorrente]

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!!

[@MyOnlineTrainingHub]

Using Power Query would result in less data in your file overall, so probably a better approach in that case.

[@guitarclasses-]

huglo jaker

[@guitarclasses-]

sorry

[@chrism9037]

Awesome Mynda!

[@MyOnlineTrainingHub]

Thanks, Chris!

[@sheryoncarter133]

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?

[@MyOnlineTrainingHub]

Glad you liked it, Sheryon! The technique for gathering data is quite different. You can use Power Query for this: www.myonlinetraininghub.com/power-query-consolidate-excel-sheets

[@mattschoular8844]

Fantastic... Thanks Mynda

[@MyOnlineTrainingHub]

Cheers, Matt!

[@alvarorodriguezlasso]

Like, regards from Cali-Colombia

[@MyOnlineTrainingHub]

Thanks, Alvaro!

[@ioanniskokorotsikos5816]

Great video. Thanks!

[@MyOnlineTrainingHub]

Glad you liked it!

[@evolutionclouds]

This is really good!

[@MyOnlineTrainingHub]

Awesome to hear 🙏

[@ajaysharmaDistrictShamli.]

always good knowledge received.

[@MyOnlineTrainingHub]

Glad to hear that!

[@AshwaniSharma-fc2nz]

Very much helpful Maam

[@MyOnlineTrainingHub]

Great to see you enjoying my videos, Ashwani!