Save your favorite margins, headers, footers & styles in the default workbook template. Recap:
Start with a blank workbook with a single sheet
Do all of your favorite customizations
Fit to 1 page
Narrow Margins
Custom Footer
Change styles?
Create a default pivot table style and delete the pivot table?
Decide XLTM (allows macros) or XLTX (does not)
Save the workbook to %APPDATA% then Microsoft\Excel\XLStart
Save it twice. Once as "Book". Once as "Sheet"
Use Ctrl+N to create a new workbook with the settings from Book
Insert Worksheet to insert a worksheet with settings from Sheet
Start with a blank workbook with a single sheet
Do all of your favorite customizations
Fit to 1 page
Narrow Margins
Custom Footer
Change styles?
Create a default pivot table style and delete the pivot table?
Decide XLTM (allows macros) or XLTX (does not)
Save the workbook to %APPDATA% then Microsoft\Excel\XLStart
Save it twice. Once as "Book". Once as "Sheet"
Use Ctrl+N to create a new workbook with the settings from Book
Insert Worksheet to insert a worksheet with settings from Sheet
Transcript of the video:
Learn Excel from MrExcel podcast, episode 1987 - Workbook Default Settings!
Hey, I'll be podcasting this whole book, click that "i" in the top right hand corner, to get to the playlist for all of the items.
This is one of my favorite tips of all time, every time that I create a brand new workbook, I have to go through and do several different customizations.
I'm just going to do a CTRL+N. Here's what I do all the time, I go to Page Layout, I say, Page Setup, Header/Footer, Custom Footer, whatever your company is, right.
I used to work for this guy, Highly Confidential - For Senior Executive Eyes Only.
Your sprite(?) is just as confidential, or something like that.
Margins - I've been using these margins forever, 0.25, 0.25, 0.25, the footer I put it 0.25, in the bottom at 0.5, Center on page Horizontally.
Page fit to one page wide by 'blank' pages tall.
Alright, now yeah I know, there's narrow margins here, but hey those aren't narrow, 0.75, 0.75, I want my narrow margins, and I just want them to be there by default.
Some other things you can do, and some of these, may seem really crazy, let's build a quick pivot table here.
Use that custom list that we have.
Alright, so insert pivot table, I'm just going to put it off to the right hand side.
Click OK.
Product, Sales.
See that color?
I like that color.
That's the color I want!
I've right clicked and said "set this as the default", but that's useless because it doesn't stick!
It's gone when you create a new workbook.
Well, I actually, in my original workbook, created a pivot table, made it be the default, and then, deleted the pivot table.
Right, so now there's no pivot table left!
How about on Cell Styles, do any of these annoy you?
Yeah a lot of them annoy me.
Why are the input cells supposed to be orange?
Oh well hey, they don't have to be orange!
You can right click any of these, modify, go into format, and change to whatever you want.
Click OK, click OK.
So now in this workbook, the calculation is this horrible orange on green, but at least, I got to pick the horrible orange on green, instead of having someone from the Excel team force it upon me.
Maybe you go in and you have a named range, you always create a named range of tax rate, it's 6.5%, well, define the name.
So you're creating a single worksheet book, putting all of your favorite settings in, and then you're going to save that workbook, into a special place, with a special name, and a special type.
Here's the thing you have to ask yourself: Do you use macros?
If so then you're going to save it as XLTM.
Or, are you never going to use macros?
Then you're going to save it as XLTX.
We're going to save that workbook into this folder: %APPDATA% Microsoft\excel\XLStart, with two names: "Book" and "Sheet"!
Book is what you get when you do CTRL+N for New, and sheet is what you get when you do Insert Worksheet.
Alright so, I'll come back here.
Here's our workbook, it has all my favorite settings.
File, Save As, Browse, we're going to change this from "xlsm" to, for me, "xltm".
If you're never going to use macros, then maybe you use "xltx".
And watch out, when you do that, they go deep down here, into this crazy folder.
I'm going to change that, I'm going to say: %APPDATA% , like that, and that goes to your app data, from there, Microsoft, Excel, and then XLStart.
And this is where we're going to save it as Book, and then do a second Save As, exact same file, as Sheet!
Alright, now I'm going to cancel because I've already done that.
And then here's what we get.
Anytime that you're in Excel, and you press CTRL+N for New, you will have all of your favorite settings.
Page Layout, my margins are right, my Header/Footer is right, the Cell Styles are right, if I create a pivot table it's going to be in the correct color.
The reason you need to save it as Sheet, is if you insert a sheet using either the new sheet or Insert Worksheet, it won't inherit the settings from Book.xltm but it will inherit the settings from Sheet.xltm!
Alright, now I have a bit of a rant here, about Microsoft, and the way that things work.
OK, I just closed, and reopened Excel, and they bring me to this, the Start screen, and here on the Start screen they're offering this first tile, called a Blank workbook.
This Blank workbook, if you would go back to Excel 2003, was a feature called "New...".
It was hidden, no one would ever find it.
You had to go to the file menu and define "New...", everyone just used the icon on the standard toolbar called New, no ... Well somehow, when we jumped from 2003 to 2007, the great new, icon without the dots, got lost, and "New..." became the one that we see.
And now, thanks to the Start screen, it's right here in your face!
This has its own settings, not your settings, not the Book.xltm settings - this is evil!
We don't want this! so when I get to this screen, I never click this one, because it's going to have someone else's settings.
I want my settings so I press Esc.
Esc brings me to a workbook with all of my favorite settings!
Or, I just press CTRL+N, OR, if it annoys you as much as it annoys me, we go to File, Options, and right here just say "don't show me the Start screen anymore".
That way you'll always get your Book.xltm!
I've discussed this with the Excel team, they conceptually understand why it's bad, but there's something that I don't understand, that they can't seem to change it.
So here we are.
Now a month I've been talking about how this book will save you some time.
This feature, setting that Book.xltm, you know, takes you 5 minutes to do it right the first time, and then every time you create workbook you're going to save a minute at least!
Think about that over the course of your lifetime.
All the hours, and dozens and hundreds of hours, you're going to save not having to change the margins from now on.
Click that "i" in the top-right hand corner, to go out and buy the book right now.
I would really appreciate it.
Recap of this issue: we're going to save all your favorite settings in a new workbook.
You have to start with a blank workbook with one sheet.
Do all the favorite customizations, anything you like, the ones I suggested, or yours, decide - are we doing XLTM to allow macros, or XLTX without macros.
Do a Save As, change the extension, then go to %APPDATA% , that'll get you to the right folder, where you can choose Microsoft\Excel\XLStart!
Save it twice, once as Book, once as Sheet.
From then on CTRL+N, or Insert Worksheet, to inherit the settings from Book or Sheet.
The book was crowdsourced, everyone sent in their favorite tip, it was Jo Ann Babin who sent us an idea similar this one, So, thanks to Jo Ann for this tip, thanks to you for stopping by, we'll see you next time for another netcast from MrExcel!
Hey, I'll be podcasting this whole book, click that "i" in the top right hand corner, to get to the playlist for all of the items.
This is one of my favorite tips of all time, every time that I create a brand new workbook, I have to go through and do several different customizations.
I'm just going to do a CTRL+N. Here's what I do all the time, I go to Page Layout, I say, Page Setup, Header/Footer, Custom Footer, whatever your company is, right.
I used to work for this guy, Highly Confidential - For Senior Executive Eyes Only.
Your sprite(?) is just as confidential, or something like that.
Margins - I've been using these margins forever, 0.25, 0.25, 0.25, the footer I put it 0.25, in the bottom at 0.5, Center on page Horizontally.
Page fit to one page wide by 'blank' pages tall.
Alright, now yeah I know, there's narrow margins here, but hey those aren't narrow, 0.75, 0.75, I want my narrow margins, and I just want them to be there by default.
Some other things you can do, and some of these, may seem really crazy, let's build a quick pivot table here.
Use that custom list that we have.
Alright, so insert pivot table, I'm just going to put it off to the right hand side.
Click OK.
Product, Sales.
See that color?
I like that color.
That's the color I want!
I've right clicked and said "set this as the default", but that's useless because it doesn't stick!
It's gone when you create a new workbook.
Well, I actually, in my original workbook, created a pivot table, made it be the default, and then, deleted the pivot table.
Right, so now there's no pivot table left!
How about on Cell Styles, do any of these annoy you?
Yeah a lot of them annoy me.
Why are the input cells supposed to be orange?
Oh well hey, they don't have to be orange!
You can right click any of these, modify, go into format, and change to whatever you want.
Click OK, click OK.
So now in this workbook, the calculation is this horrible orange on green, but at least, I got to pick the horrible orange on green, instead of having someone from the Excel team force it upon me.
Maybe you go in and you have a named range, you always create a named range of tax rate, it's 6.5%, well, define the name.
So you're creating a single worksheet book, putting all of your favorite settings in, and then you're going to save that workbook, into a special place, with a special name, and a special type.
Here's the thing you have to ask yourself: Do you use macros?
If so then you're going to save it as XLTM.
Or, are you never going to use macros?
Then you're going to save it as XLTX.
We're going to save that workbook into this folder: %APPDATA% Microsoft\excel\XLStart, with two names: "Book" and "Sheet"!
Book is what you get when you do CTRL+N for New, and sheet is what you get when you do Insert Worksheet.
Alright so, I'll come back here.
Here's our workbook, it has all my favorite settings.
File, Save As, Browse, we're going to change this from "xlsm" to, for me, "xltm".
If you're never going to use macros, then maybe you use "xltx".
And watch out, when you do that, they go deep down here, into this crazy folder.
I'm going to change that, I'm going to say: %APPDATA% , like that, and that goes to your app data, from there, Microsoft, Excel, and then XLStart.
And this is where we're going to save it as Book, and then do a second Save As, exact same file, as Sheet!
Alright, now I'm going to cancel because I've already done that.
And then here's what we get.
Anytime that you're in Excel, and you press CTRL+N for New, you will have all of your favorite settings.
Page Layout, my margins are right, my Header/Footer is right, the Cell Styles are right, if I create a pivot table it's going to be in the correct color.
The reason you need to save it as Sheet, is if you insert a sheet using either the new sheet or Insert Worksheet, it won't inherit the settings from Book.xltm but it will inherit the settings from Sheet.xltm!
Alright, now I have a bit of a rant here, about Microsoft, and the way that things work.
OK, I just closed, and reopened Excel, and they bring me to this, the Start screen, and here on the Start screen they're offering this first tile, called a Blank workbook.
This Blank workbook, if you would go back to Excel 2003, was a feature called "New...".
It was hidden, no one would ever find it.
You had to go to the file menu and define "New...", everyone just used the icon on the standard toolbar called New, no ... Well somehow, when we jumped from 2003 to 2007, the great new, icon without the dots, got lost, and "New..." became the one that we see.
And now, thanks to the Start screen, it's right here in your face!
This has its own settings, not your settings, not the Book.xltm settings - this is evil!
We don't want this! so when I get to this screen, I never click this one, because it's going to have someone else's settings.
I want my settings so I press Esc.
Esc brings me to a workbook with all of my favorite settings!
Or, I just press CTRL+N, OR, if it annoys you as much as it annoys me, we go to File, Options, and right here just say "don't show me the Start screen anymore".
That way you'll always get your Book.xltm!
I've discussed this with the Excel team, they conceptually understand why it's bad, but there's something that I don't understand, that they can't seem to change it.
So here we are.
Now a month I've been talking about how this book will save you some time.
This feature, setting that Book.xltm, you know, takes you 5 minutes to do it right the first time, and then every time you create workbook you're going to save a minute at least!
Think about that over the course of your lifetime.
All the hours, and dozens and hundreds of hours, you're going to save not having to change the margins from now on.
Click that "i" in the top-right hand corner, to go out and buy the book right now.
I would really appreciate it.
Recap of this issue: we're going to save all your favorite settings in a new workbook.
You have to start with a blank workbook with one sheet.
Do all the favorite customizations, anything you like, the ones I suggested, or yours, decide - are we doing XLTM to allow macros, or XLTX without macros.
Do a Save As, change the extension, then go to %APPDATA% , that'll get you to the right folder, where you can choose Microsoft\Excel\XLStart!
Save it twice, once as Book, once as Sheet.
From then on CTRL+N, or Insert Worksheet, to inherit the settings from Book or Sheet.
The book was crowdsourced, everyone sent in their favorite tip, it was Jo Ann Babin who sent us an idea similar this one, So, thanks to Jo Ann for this tip, thanks to you for stopping by, we'll see you next time for another netcast from MrExcel!
