I couldn't agree more that having to set the number format for each new pivot table is the most annoying time wasters there is so I was really looking forward to seeing how I can get around that. Then you mentioned that it's only available in Excel 2019 or Ofiice 365 and I was darn it---I knew there was a catch! :( Oh well, maybe one day we'll come up with a Macro that can do that. :)
I should have been more specific...the default layout settings are only available in 2019/365, but the data model and number formats are available in 2010 onward :-)
Thanks Mynda, I've done your Power Pivot course and just started using it in earnest in my job. I went to create a calculated field and what?!? It was greyed out. Now I know to use the Add Column feature in the PP Manager
Mynda, you're so EXCELlent. Thank you this tips and tricks. ✌ We can set up Pivot Table default style for each new excel workbook. First, Open a blank workbook. Then Create a waste pivot table for any data and create again a Pivot table new style in to Piwot Table Style Tab and called it a name (for example Emre's table). Second, save as this workbook as Workbook1 on the desktop Third, open the root folder of excel as below: C:\Users\User\AppData\Roaming\Microsoft\Excel\XLSTART\ (it differs from each individual PC. You can find it File--Properties--Trust Centre) Forth, Cut Workbook1 on the desktop and paste this file in to C:\Users\User\AppData\Roaming\Microsoft\Excel\XLSTART\ Close the excel and open it again. Boom✨,,, when you create new pivot data, you will find new created table style as your own Pivot Table Style and use it
@@MyOnlineTrainingHub Hi Mynda, you’re wrong. If we are creating dashboards which are offenly being used professional businees, we have to know your style techniques including pivot table sytling. You are giving wonderful information that nobody knows them.. I admire you 👏
@Emre, I tried your way (it seemed less complicated for me) but it didn't work. I see my template in the root folder you provided above, I closed and reopened my Excel but when I was inserting a new pivot, the template wasn't there to choose. Any idea why?
Thank you! I set my pivot tables to also be in tabular format and repeat row labels. So I have a column with 2022 and then January, and then 2022 and then February, etc. But I want the year and month columns to automatically show together, so it is Jan 2022 in one column. Is there a pivot table setting to auto merge the month and year columns into one?
Use a proper date field in the row label and the right click it and group by month and year. You can then expand it to show them grouped the way you describe.
Got to this video by chance. Is there a way to save a style (I often use a current style, then duplicate and then I like to add borders to all cells). But the new style is only available in this workbook. How can I make sure it is available in all future workbooks?
Help I can’t get this answer anywhere!! I have a pivot table that has clients in the rows and then 3 separate columns totaling revenue between 3 different divisions. I need to highlight the clients that are utilized between 2 or more divisions.
I suspect your source data is in the wrong format to do this. You need to put the revenue in one column and have another column for the division. i.e. the following columns in the PivotTable source data: - Client - Revenue - Division If it's not in this layout, then you can use power query to unpivot it: www.myonlinetraininghub.com/power-query-unpivot-scenarios If you're still 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
how do you change the data format when you double click on pivot data to show details, it opens in new sheet but data is not arranged properly means the column width.
You have to do this manually by selecting the table columns or whole sheet and then double clicking one of the column intersections in the header. This will auto-fit them to the content in the columns.
Hi Mynda i am totally confused let me tell you where i added 5 slicers in my dashboard but the fourth slicer creating a problem when i click on the fourth slicer it shows the hidden cell on other slicers which i have hidden like a blank cell and others i hide which have no data, will you tell me what is the problem why all slicer stats to show blank cell when i click 4th slicer i this happen when i clear filter on 4th slicer
It does this because at that level of filtering there's no data for that item. You can go into the Slicer settings and turn off 'show items with no data'.
Can you save these pivot tables? Every time I close my file, they replace with my original sheet and I can only see the pivot table being replaced by it.
Not sure what you mean. You can post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub you know when you create a pivot table, can you save the sheet as it is? So for me everytime I close the excel file, it completely delete all the pivot tables I created.
For the life of me I cannot find this Edit Default Layout button. In fact there isn't even a Data menu and it goes directly from Formulas to Proofing. In the Advanced menu, there is a Data section, but no Edit Default Layout button.
There is no "Data options" category for setting default layouts & formatting of Pivot Tables in Excel Professional Plus 2016 .... boo, hiss, Microsoft! 😡
@@MyOnlineTrainingHub but only for that spreadsheet, next time you want to do another pivot table on a new spreadsheet you have to go through the steps again.
Thanks, Mynda. Anything that makes pivot tables less of a faff to set up is great.
Cheers, Ian!
I couldn't agree more that having to set the number format for each new pivot table is the most annoying time wasters there is so I was really looking forward to seeing how I can get around that. Then you mentioned that it's only available in Excel 2019 or Ofiice 365 and I was darn it---I knew there was a catch! :( Oh well, maybe one day we'll come up with a Macro that can do that. :)
I should have been more specific...the default layout settings are only available in 2019/365, but the data model and number formats are available in 2010 onward :-)
@@MyOnlineTrainingHub That's great! I'll definitely look into this. Thank you for replying. Have a good day!
Thanks, Minda! Really looking forward to analyzing my GL data, instead of reformatting it!
Great to hear!
Wow this is great!!! Thanks a lot! I even had to have a VBA macro to run and change the number formats. Need to get more familiar with the Data Model
Glad it was helpful!
I never realised I could set pivot table defaults in options....great news 👍 Thank you very much.
Glad it was useful :-)
Thank you (again) a lot Mynda for another clear and useful video!
My pleasure, Olivier!
Thank you a lot Mynda for another clear and useful video! super cool!
Glad it was helpful!
Hi Mynda. Great Pivot Table advice and tips! Thanks for sharing :)) Thumbs up!!
Thanks so much, Wayne!
Queen 👑 of Excel 🙏🙏
Thanks so much 😊
Thanks Mynda, very helpful
Cheers, Chris :-)
Great video! Thanks!
Glad you liked it 😊
Again, thank you so much!
My pleasure 😉
Thank you Mynda. Well, when I'll be back on a PC one day, I'll use these, but on Mac, MS 365 is not so rich, the least to say.
Hope you can get back on a PC soon!
Thanks Mynda, I've done your Power Pivot course and just started using it in earnest in my job. I went to create a calculated field and what?!? It was greyed out. Now I know to use the Add Column feature in the PP Manager
Great to hear, Dianne!
Thank you! This is really useful!
Glad to hear that, Max!
superb lesson - thank you
Glad you liked it 😊
Superb. I needed that. Great video
Glad you liked it :-)
Mynda. As always a very informative video
Cheers, Mark!
Mynda, you're so EXCELlent. Thank you this tips and tricks. ✌
We can set up Pivot Table default style for each new excel workbook.
First, Open a blank workbook. Then Create a waste pivot table for any data and create again a Pivot table new style in to Piwot Table Style Tab and called it a name (for example Emre's table).
Second, save as this workbook as Workbook1 on the desktop
Third, open the root folder of excel as below:
C:\Users\User\AppData\Roaming\Microsoft\Excel\XLSTART\ (it differs from each individual PC. You can find it File--Properties--Trust Centre)
Forth, Cut Workbook1 on the desktop and paste this file in to C:\Users\User\AppData\Roaming\Microsoft\Excel\XLSTART\
Close the excel and open it again.
Boom✨,,, when you create new pivot data, you will find new created table style as your own Pivot Table Style and use it
Cheers, Emre! I was going to demo that, but then thought not many people really care about the style...maybe I'm wrong :-)
@@MyOnlineTrainingHub Hi Mynda, you’re wrong. If we are creating dashboards which are offenly being used professional businees, we have to know your style techniques including pivot table sytling.
You are giving wonderful information that nobody knows them..
I admire you 👏
@Emre, I tried your way (it seemed less complicated for me) but it didn't work. I see my template in the root folder you provided above, I closed and reopened my Excel but when I was inserting a new pivot, the template wasn't there to choose. Any idea why?
Thank you
You're welcome 😊
Thank you! I set my pivot tables to also be in tabular format and repeat row labels. So I have a column with 2022 and then January, and then 2022 and then February, etc. But I want the year and month columns to automatically show together, so it is Jan 2022 in one column. Is there a pivot table setting to auto merge the month and year columns into one?
Use a proper date field in the row label and the right click it and group by month and year. You can then expand it to show them grouped the way you describe.
Thanks Mynda
My pleasure, Graham!
Got to this video by chance. Is there a way to save a style (I often use a current style, then duplicate and then I like to add borders to all cells). But the new style is only available in this workbook. How can I make sure it is available in all future workbooks?
Hi Chris, save the style to your default workbook: www.myonlinetraininghub.com/customize-default-excel-workbook
Excellent
Thank you so much 😀
This should have been in place many years ago. Excel has wasted a major portion of my life just on pivot table layouts.
Hi Mynda!Really Great Tips..Thank You :)
Cheers, Darryl 😊
Thanks so much for this tutorial , is there is a way to put 2 number formats? For example Revenue in $ and units as numbers ?
Yes, you can have a different format for each field/column in your table.
@@MyOnlineTrainingHub that's Awesome , my colleagues are all getting a link to this video 😁😁
Mynda, nice! Thanks
My pleasure 😊
Stuck on Excel 2016😟Hopefully one day my work will upgrade to Office 365!
🤞hope you get 365 soon. I should have been clearer that the number formatting and colour styles is available in Excel 2010 onward.
Thankyou. Is it possible to save a custom TABLE style to use in other workbooks, I can't beleive it's not possible!
You can set a default table style in your default workbook: www.myonlinetraininghub.com/customize-default-excel-workbook
@@MyOnlineTrainingHub Thank you :)
👍 super. thanks
Welcome 😊
Great Video Mynda, I've also found the Group function isn't available in Pivot Tables based on the Data Model
Yes, good point. In earlier versions of Excel you don’t have grouping.
Love this
Great to hear, Michael!
Help I can’t get this answer anywhere!! I have a pivot table that has clients in the rows and then 3 separate columns totaling revenue between 3 different divisions. I need to highlight the clients that are utilized between 2 or more divisions.
I suspect your source data is in the wrong format to do this. You need to put the revenue in one column and have another column for the division. i.e. the following columns in the PivotTable source data:
- Client
- Revenue
- Division
If it's not in this layout, then you can use power query to unpivot it: www.myonlinetraininghub.com/power-query-unpivot-scenarios
If you're still 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
You do Excel. Thank you.
😊 Thank you!
thanks 👍
My pleasure 😊
how do you change the data format when you double click on pivot data to show details, it opens in new sheet but data is not arranged properly means the column width.
You have to do this manually by selecting the table columns or whole sheet and then double clicking one of the column intersections in the header. This will auto-fit them to the content in the columns.
Hi Mynda i am totally confused let me tell you where i added 5 slicers in my dashboard but the fourth slicer creating a problem when i click on the fourth slicer it shows the hidden cell on other slicers which i have hidden like a blank cell and others i hide which have no data, will you tell me what is the problem why all slicer stats to show blank cell when i click 4th slicer i this happen when i clear filter on 4th slicer
It does this because at that level of filtering there's no data for that item. You can go into the Slicer settings and turn off 'show items with no data'.
@@MyOnlineTrainingHub i have turned off but all the slicers the behaving well but the 4th slicer is creating a mess
Hi, Is it possible to create an hyperlink to another document inside a pivot table?
Not without some VBA, as explained here: www.pivot-table.com/2013/11/06/create-fake-hyperlinks-in-excel-pivot-table/
Can you save these pivot tables? Every time I close my file, they replace with my original sheet and I can only see the pivot table being replaced by it.
Not sure what you mean. You can post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub you know when you create a pivot table, can you save the sheet as it is? So for me everytime I close the excel file, it completely delete all the pivot tables I created.
Your PivotTables shouldn't be deleted when you close the file unless you don't save it before closing.
@@MyOnlineTrainingHub I do save the csv file before closing it, but it doesn't seem to work properly unless there is a special way of saving it 🤔
Ah, you can't save PivotTables in a CSV file. You must save your file as a .xslx file to retain the PivotTable.
For the life of me I cannot find this Edit Default Layout button. In fact there isn't even a Data menu and it goes directly from Formulas to Proofing. In the Advanced menu, there is a Data section, but no Edit Default Layout button.
Hi Jeremy, it's only available in Excel 2019, 2021 or with a 365 license.
is there a way to do the same in excel for Mac?
Not sure, sorry. I don't have a Mac to test it on.
There is no "Data options" category for setting default layouts & formatting of Pivot Tables in Excel Professional Plus 2016 .... boo, hiss, Microsoft! 😡
Yes, this is a new feature that came out after 2016..
So, long story short - the biggest time waster, number formatting, is still a time waster.
Not when you add your data to the data model. There you set the format once and it’s done for all pivot tables.
@@MyOnlineTrainingHub but only for that spreadsheet, next time you want to do another pivot table on a new spreadsheet you have to go through the steps again.
Well yes, because you will have new data, but it's only once per dataset.