Extract Data to Separate Sheets the Right Way!

There’s a little known tool for PivotTables that will automatically extract the source data to separate sheets based on criteria.
Download the Excel file here: www.myonlinetraininghub.com/e...
View my comprehensive courses: www.myonlinetraininghub.com/
Connect with me on LinkedIn: / myndatreacy
0:00 How to extract data to separate sheets in Excel
0:40 Using PivotTables to extract data
4:11 Using FILTER function to extract data
7:23 PivotTables vs Formulas for extracting data

[@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 🙏

[@vijayarjunwadkar]

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

[@MyOnlineTrainingHub]

Thanks so much, Vijay!

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

[@TheOscarProject1]

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

[@MyOnlineTrainingHub]

Great to hear!

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

[@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! 🙏

[@Akash-py3yi]

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

[@MyOnlineTrainingHub]

Awesome to hear!

[@sacstatesfinest]

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

[@MyOnlineTrainingHub]

Awesome, thank you!

[@amykoszalkahhroofingaccoun6454]

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

[@MyOnlineTrainingHub]

Glad it was helpful, Amy!

[@simonmassen]

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

[@MyOnlineTrainingHub]

Awesome to hear!

[@Seftehandle]

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

[@MyOnlineTrainingHub]

😁 great to hear, Tina!

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

[@darrylmorgan]

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

[@MyOnlineTrainingHub]

Great to hear, Darryl!

[@ddp2049]

Thanks the combine method was great and clear to understanding.

[@MyOnlineTrainingHub]

Glad it was helpful!

[@tonyvanriessen]

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

[@MyOnlineTrainingHub]

Wow, thanks so much 😊

[@HowToAnalyst]

Helpful guidance for a problem I frequently face!

[@MyOnlineTrainingHub]

Glad I could help, Pete!

[@daimpi]

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

[@MyOnlineTrainingHub]

I'm so glad! 😊

[@anyandeveryistaken]

Most favorite Excel Teacher

[@MyOnlineTrainingHub]

Wow, thank you, Nahas!

[@KV21A]

Thanks for the tutorial Mynda ✌.

[@MyOnlineTrainingHub]

You’re welcome 😊

[@gregbernard7861]

This is my go to Excel channel.

[@MyOnlineTrainingHub]

Wow, that's awesome to hear, Greg 🙏

[@grahamparker7729]

Another great video Mynda 👍🏻

[@MyOnlineTrainingHub]

Thanks so much, Graham!

[@ADfamily81]

Your videos are great, thank you!

[@MyOnlineTrainingHub]

Glad you like them!

[@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.

[@jerrymiles7804]

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

[@MyOnlineTrainingHub]

Thanks so much, Jerry!

[@jerrymiles7804]

😘

[@SGSubra]

Wow, This is an absolute God send. Thank you

[@MyOnlineTrainingHub]

Glad it was helpful!

[@mattschoular8844]

Fantastic... Thanks Mynda

[@MyOnlineTrainingHub]

Cheers, Matt!

[@chrism9037]

Awesome Mynda!

[@MyOnlineTrainingHub]

Thanks, Chris!

[@rezaamini9830]

Thanks for you useful effort and made these videos😀

[@MyOnlineTrainingHub]

You're most welcome 😊

[@mehdihdr1]

Hi Mynda, very informative video related to pivot table

[@MyOnlineTrainingHub]

Glad you think so, Mehdi!

[@stopthink9000]

Super useful! Thanks!!

[@MyOnlineTrainingHub]

Glad to hear that!

[@ioanniskokorotsikos5816]

Great video. Thanks!

[@MyOnlineTrainingHub]

Glad you liked it!

[@evolutionclouds]

This is really good!

[@MyOnlineTrainingHub]

Awesome to hear 🙏

[@stephencooper3583]

Nice! I had no idea PivotTables could do that.

[@MyOnlineTrainingHub]

Glad you liked it, Stephen!

[@ajaysharmaDistrictShamli.]

always good knowledge received.

[@MyOnlineTrainingHub]

Glad to hear that!

[@alvarorodriguezlasso]

Like, regards from Cali-Colombia

[@MyOnlineTrainingHub]

Thanks, Alvaro!

[@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

[@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.

[@database_tips_tricks]

Exactly what I need

[@MyOnlineTrainingHub]

Great to hear, Nigel!

[@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.

[@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.

[@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.

[@zarniwhite6249]

Thank you so much!🙏

[@MyOnlineTrainingHub]

Any time!

[@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.

[@AshwaniSharma-fc2nz]

Very much helpful Maam

[@MyOnlineTrainingHub]

Great to see you enjoying my videos, Ashwani!

[@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.

[@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

[@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.

[@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)

[@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.

[@klinsmannlanghanz6507]

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

[@MyOnlineTrainingHub]

Glad you liked it!

[@kikin62]

Thanks ¡¡ very useful tutorial ¡¡

[@MyOnlineTrainingHub]

Great to hear!

[@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.

[@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.

[@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

[@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.

[@davidhampson2463]

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

[@MyOnlineTrainingHub]

No, each PivotTable will create its own set of sheets.

[@vladx3539]

brilliant again

[@MyOnlineTrainingHub]

Thank you 😊

[@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.

[@sk8erdex]

You are the Best

[@MyOnlineTrainingHub]

Thanks so much!

[@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

[@user-gu2cu2pm4z]

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

[@user-gu2cu2pm4z]

@@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?

[@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.

[@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

[@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

[@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! 😁

[@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.

[@ronaldoliver1700]

Thank you

[@MyOnlineTrainingHub]

You're welcome, Ronald!

[@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

[@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

[@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.

[@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.

[@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

[@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

[@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.

[@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.

[@felipesignorellireis7839]

very very good

[@MyOnlineTrainingHub]

Thanks so much!

[@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.

[@alializadeh8195]

Thanks

[@MyOnlineTrainingHub]

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.

[@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 :)

[@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

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

[@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.

[@drsteele4749]

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,""))))

[@MyOnlineTrainingHub]

Nice! I haven't played around with adding totals much. Thanks for sharing.

[@stuontwo677]

why would you not use an importrange query here ?

[@MyOnlineTrainingHub]

Because this is Excel, not Google Sheets.

[@yousrymaarouf2931]

I like formulas because it is Dynamic

[@MyOnlineTrainingHub]

Me too, but for lots of data PivotTables might be more efficient.

[@aleksandrssetela5950]

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!

[@MyOnlineTrainingHub]

This video is teaching how to use PivotTables to extract videos, so I'm not sure what formula you're referring to, but I'm happy to help. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@saifrashid6713]

Ctrl Z to ungrouping is not working for me.

[@MyOnlineTrainingHub]

It only works if you press it immediately after adding the date field to the PivotTable and it being grouped. If you do anything after that, the option is gone and you have to go to the Group menu and remove them.

[@JJ_TheGreat]

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

[@MyOnlineTrainingHub]

Doh, typos!

[@vladx3539]

btw we can only watch it in the future 😊

[@MyOnlineTrainingHub]

😁

[@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.

[@iankr]

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.

[@MyOnlineTrainingHub]

Glad you liked it, Ian!