Another question from Columbus, Indiana. How can you choose a default style (from the PivotTable Design tab) for all future pivot tables? Yes, there is a Set as Default choice, but it only applies to future pivot tables in this workbook. My method involves adding a default to BOOK.XLTM.
Also in this video, a pivot table formatting oddity from Excel MVP Wyn Hopkins.
Table of Contents
(0:00) Introduction
(0:44) The colors on Page Layout impact the Pivot Table Styles
(1:25) Changing a pivot table style default only affects this workbook
(3:40) A FAST way to get to Browse during Save As
(4:54) MVP Wyn Hopkins Discovers a bizarre pivot table formatting trick!
(5:33) Change the colors and style of your borders using Format Cells
(7:23) Pivot Table Data Crunching book & wrap-up
Also in this video, a pivot table formatting oddity from Excel MVP Wyn Hopkins.
Table of Contents
(0:00) Introduction
(0:44) The colors on Page Layout impact the Pivot Table Styles
(1:25) Changing a pivot table style default only affects this workbook
(3:40) A FAST way to get to Browse during Save As
(4:54) MVP Wyn Hopkins Discovers a bizarre pivot table formatting trick!
(5:33) Change the colors and style of your borders using Format Cells
(7:23) Pivot Table Data Crunching book & wrap-up
Transcript of the video:
Hey, just a quick note after I've edited the video.
I realized that this says it's about “Pivot Table default formatting”. But along the way, we discover some really crazy things that have never been on the podcast before.
So check out those items down in the table of contents down in the YouTube description below.
Learn Excel from MrExcel podcast episode 2405.
Can we control the default pivot table style?
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen another question from my virtual seminar up at Columbus Indiana.
It is dealing with how to control the default pivot table style from this Gallery.
Can we make all future pivot tables follow some certain rule?
And, before we talk about that, just realize that the colors in this Gallery are controlled back here under Page Layout.
If you choose a different color scheme, that's going to affect the colors that are in that Gallery.
So, come here: Colors and choose something that is green and then under design you see that all of our choices are using those colors right?
So that's the first thing to realize.
This Gallery here is super annoying because they pretend that you can control this.
If you right-click you can say Set as Default right?
And you think oh, that's great.
All future pivot tables that I create will now have those color schemes.
You can even create your own color scheme back on the page layout tab that match the corporate branding.
And now we're good to go.
But Set As Default is deceptive.
It's Set As Default For All Future Pivot Tables That I Create In This Workbook.
Not all workbooks, right?
So that isn't very useful at all.
My solution for this is not perfect, but it's better than that.
So I'm just going to Control N. First I will copy this original data here.
Just any data something that you can create a pivot table.
Control C, Control N for New, Control V to paste.
Go ahead and create a pivot table in this workbook.
Insert, Pivot Table, Existing worksheet, Right here.
Click OK.
Doesn't matter what you put in it, just anything so you can come back here on the Design tab.
I guess, first go to Page Layout and choose the color scheme that you want.
Slipstream is what I'm going to use.
And then in the design tab choose whichever format you think is the right one.
I'll go with that.
That way if I have two sets of items I can have that alternating row.
And you can choose banded rows or banded columns.
Your choice there.
Then right-click and Set as Default.
What we're doing is setting a default for this book, Book4.
All right now this is going to sound crazy.
Take all of your data and the Pivot table.
Select it.
Delete all.
So now we have a completely blank workbook.
And the only thing that we've changed is the default pivot table.
We're going to File, Options.
Go to Advanced.
Scroll about halfway down in Advanced.
There is a setting that says “At Startup, Open All Files In”.
Right now, on your computer, this is most likely blank.
No one uses this, so we're going to create a folder called XLStart.
You can call it whatever you want to call it.
So XLStart and then I'm going to save this workbook into that folder with a special name.
File, Save As.
I'm going to change the file type to either XLTM or XLTX.
If you use macros then use XLTM.
If you don't then XLTX.
I will use XLTM.
And be careful when you do that - they go to a certain folder and we want to change that.
And check this out.
I want to change that path.
Normally I come down here and click Browse but I just realized that I can click here as well and that takes me into the old Save As dialog box.
Choose XLStart folder.
And I already have a file called Book.xltm.
Save over that.
We have our Book.XLTM saved.
I can File, Close that now.
The cool thing is anytime now that I use Ctrl+N to create a new workbook and I add some data to that.
So I'll say Rep. Andy.
Grab the fill handle and drag.
Products.
Apple.
Grab the film it and drag again.
Sales.
And they want to create a pivot table.
Insert, pivot table, New Worksheet and click OK.
I will automatically get those colors and settings.
It's not perfect.
If someone else sends you workbook, it's not going to know that default.
But at least for most of the workbooks that you start to create, that will be the default.
Hey, we're talking about Pivot table formatting, just a real oddity here from one of the Excel MVP's named Wyn Hopkins.
Check out his channel at YouTube (click the “I” in the top right hand corner) at Access Analytic.
Wyn has great playlists on Power BI, Power Query and General Excel.
Check this out.
Wyn was doing something else and stumbled upon this weird oddity.
So we have a pivot table there, right?
And if you come around the pivot table an add a border like a thick outside border or something like that.
Actually, I am going to go in and customize that even more so I want to show you exactly how this is working.
I'm going to choose a red border for the left so we have a thick red border on the left.
At the bottom I'll use a blue border and have it be dashed, dashed blue.
On the right hand side, I'll use purple and have that be dotted border.
And then across the top, I guess we can use whatever we have not used yet.
Orange across the top.
Is that orange?
And one of these dashed lines across the top.
Click OK.
Alright.
So now we have this pivot table and not touching the pivot table but completely surrounding the pivot table is this weird border.
And then change something in the underlying data.
And come into the pivot table and refresh.
And oddly enough, the pivot table picks up the same borders that is surrounding it.
So that blue border on the bottom becomes that horizontal line there.
The border on top becomes every other horizontal border in the entire pivot table.
The red line on the left becomes most of the vertical borders, but the purple line on the right becomes the rightmost border.
Was this planned?
Like did someone actually code this up?
What was the theory?
I'm guessing… I'm just guessing it must have predated these pivot table Designs and it might have been some way to very quickly apply some very unusual formatting.
I don't know.
I'm not sure that this is useful at all, just presenting it as an Excel Oddity.
Thanks to Wyn for noticing that and passing it along.
It's a weird one.
Hey, later this year I'll be updating my book Microsoft Excel Pivot Table Data Crunching.
So check that book out as all kinds of great pivot table tips.
Mike Alexander was my Co author and as he likes to say it is spicy.
It has lots of spicy tips.
Click the I in the top right hand corner For more information about that.
If you like these videos, please down below Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by.
We'll see next time for another netcast from MrExcel.
I realized that this says it's about “Pivot Table default formatting”. But along the way, we discover some really crazy things that have never been on the podcast before.
So check out those items down in the table of contents down in the YouTube description below.
Learn Excel from MrExcel podcast episode 2405.
Can we control the default pivot table style?
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen another question from my virtual seminar up at Columbus Indiana.
It is dealing with how to control the default pivot table style from this Gallery.
Can we make all future pivot tables follow some certain rule?
And, before we talk about that, just realize that the colors in this Gallery are controlled back here under Page Layout.
If you choose a different color scheme, that's going to affect the colors that are in that Gallery.
So, come here: Colors and choose something that is green and then under design you see that all of our choices are using those colors right?
So that's the first thing to realize.
This Gallery here is super annoying because they pretend that you can control this.
If you right-click you can say Set as Default right?
And you think oh, that's great.
All future pivot tables that I create will now have those color schemes.
You can even create your own color scheme back on the page layout tab that match the corporate branding.
And now we're good to go.
But Set As Default is deceptive.
It's Set As Default For All Future Pivot Tables That I Create In This Workbook.
Not all workbooks, right?
So that isn't very useful at all.
My solution for this is not perfect, but it's better than that.
So I'm just going to Control N. First I will copy this original data here.
Just any data something that you can create a pivot table.
Control C, Control N for New, Control V to paste.
Go ahead and create a pivot table in this workbook.
Insert, Pivot Table, Existing worksheet, Right here.
Click OK.
Doesn't matter what you put in it, just anything so you can come back here on the Design tab.
I guess, first go to Page Layout and choose the color scheme that you want.
Slipstream is what I'm going to use.
And then in the design tab choose whichever format you think is the right one.
I'll go with that.
That way if I have two sets of items I can have that alternating row.
And you can choose banded rows or banded columns.
Your choice there.
Then right-click and Set as Default.
What we're doing is setting a default for this book, Book4.
All right now this is going to sound crazy.
Take all of your data and the Pivot table.
Select it.
Delete all.
So now we have a completely blank workbook.
And the only thing that we've changed is the default pivot table.
We're going to File, Options.
Go to Advanced.
Scroll about halfway down in Advanced.
There is a setting that says “At Startup, Open All Files In”.
Right now, on your computer, this is most likely blank.
No one uses this, so we're going to create a folder called XLStart.
You can call it whatever you want to call it.
So XLStart and then I'm going to save this workbook into that folder with a special name.
File, Save As.
I'm going to change the file type to either XLTM or XLTX.
If you use macros then use XLTM.
If you don't then XLTX.
I will use XLTM.
And be careful when you do that - they go to a certain folder and we want to change that.
And check this out.
I want to change that path.
Normally I come down here and click Browse but I just realized that I can click here as well and that takes me into the old Save As dialog box.
Choose XLStart folder.
And I already have a file called Book.xltm.
Save over that.
We have our Book.XLTM saved.
I can File, Close that now.
The cool thing is anytime now that I use Ctrl+N to create a new workbook and I add some data to that.
So I'll say Rep. Andy.
Grab the fill handle and drag.
Products.
Apple.
Grab the film it and drag again.
Sales.
And they want to create a pivot table.
Insert, pivot table, New Worksheet and click OK.
I will automatically get those colors and settings.
It's not perfect.
If someone else sends you workbook, it's not going to know that default.
But at least for most of the workbooks that you start to create, that will be the default.
Hey, we're talking about Pivot table formatting, just a real oddity here from one of the Excel MVP's named Wyn Hopkins.
Check out his channel at YouTube (click the “I” in the top right hand corner) at Access Analytic.
Wyn has great playlists on Power BI, Power Query and General Excel.
Check this out.
Wyn was doing something else and stumbled upon this weird oddity.
So we have a pivot table there, right?
And if you come around the pivot table an add a border like a thick outside border or something like that.
Actually, I am going to go in and customize that even more so I want to show you exactly how this is working.
I'm going to choose a red border for the left so we have a thick red border on the left.
At the bottom I'll use a blue border and have it be dashed, dashed blue.
On the right hand side, I'll use purple and have that be dotted border.
And then across the top, I guess we can use whatever we have not used yet.
Orange across the top.
Is that orange?
And one of these dashed lines across the top.
Click OK.
Alright.
So now we have this pivot table and not touching the pivot table but completely surrounding the pivot table is this weird border.
And then change something in the underlying data.
And come into the pivot table and refresh.
And oddly enough, the pivot table picks up the same borders that is surrounding it.
So that blue border on the bottom becomes that horizontal line there.
The border on top becomes every other horizontal border in the entire pivot table.
The red line on the left becomes most of the vertical borders, but the purple line on the right becomes the rightmost border.
Was this planned?
Like did someone actually code this up?
What was the theory?
I'm guessing… I'm just guessing it must have predated these pivot table Designs and it might have been some way to very quickly apply some very unusual formatting.
I don't know.
I'm not sure that this is useful at all, just presenting it as an Excel Oddity.
Thanks to Wyn for noticing that and passing it along.
It's a weird one.
Hey, later this year I'll be updating my book Microsoft Excel Pivot Table Data Crunching.
So check that book out as all kinds of great pivot table tips.
Mike Alexander was my Co author and as he likes to say it is spicy.
It has lots of spicy tips.
Click the I in the top right hand corner For more information about that.
If you like these videos, please down below Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by.
We'll see next time for another netcast from MrExcel.
