If the images in this message are a little misaligned in your email program,
you can go here to read the online version of this newsletter
Hi fellow Excel Addict,
Thanks for joining me this week.
Sorry. My website project is taking longer than I expected. I ask for a little more patience. + I have an exciting announcement coming up in the next few weeks. Stay tuned.
This week's tip is a repeat of a one I posted a couple of years ago. I hope you'll find it useful and that it helps make your work a little easier. If you do, please be kind and share it with others who could use a little help as well.
I hope you have a fantastic week.
Francis Hayes (The Excel Addict)
To view this week's newsletter online click here.
If you missed last week's newsletter, you can click here to view it online.
You can access even more tips on my website by going to my members' page.
Create Custom Default Worksheet And Workbook Templates
Are you constantly having to change the settings for your worksheets, such as margins, font size, page headers, etc... every time you create a new workbook or insert an new worksheet? Most Excel users assume that's just the way it is. Well, I'm here to tell you those days are over.
I'm going to show you how to create a template containing your own personalized settings that will be applied to all new workbooks and worksheets.
When you open a new workbook, Excel uses a workbook template with default settings such as three worksheets, column widths of 8.43, no headers and footers, default print margins, etc... When you insert a sheet into a workbook, Excel uses a worksheet template with similar default settings.
Here's how to create your own personalized templates so that every new workbook and worksheet will already have the settings you want.
1) Open a blank workbook;
3) Delete all sheet tabs except for one;
4) If you want to change the default formatting of your worksheet cells, such as the font face, font size, number formats, etc..., on the Home tab, click the Cell Styles command in the Styles group. Then right click the Normal style and choose Modify. Click the Format button and make any changes you like to the default style and click OK;
5) Next, to select your preferred print settings, click the Page Layout tab. Make all the changes you want your default worksheets to have (i.e. margins, headers/footers, etc...);
6) When you have selected all of the custom settings for your default worksheets, click cell A1 to make it the active cell for all new worksheets;
7) Next, you need to save this custom worksheet as a template in your XLSTART folder. Click the File tab and Save As. In the Save as Type dropdown on the bottom of the dialog box, select Excel Template (*.xltx) and browse to the XLSTART folder.
Change the suggested name to SHEET.XLTX. This will now be the default sheet template used when you Insert a worksheet into a workbook;
9) Now you need to save this same personalized worksheet as your default 'workbook' template by saving it with a different file name. To do this, simply repeat Step 7 but this time save the workbook with the filename BOOK.XLTX;
Personally, I like my default workbook to have only one sheet rather than the default three sheets Excel offers, as I find the extra two sheets redundant. If you choose to go with a one sheet default workbook you can quickly insert a new sheet by clicking the Insert Worksheet command (immediately to the right of the sheet tabs). However, in workbooks with a lot of sheet tabs, the Insert Worksheet command may not be visible, so I recommend adding the Insert Worksheet command to your Quick Access Toolbar (QAT). Alternatively you can use Shift+F11 to insert a new sheet.
Add the Insert Worksheet and New workbook commands to your QAT
1) Right-click the QAT and choose Customize Quick Access Toolbar;
2) In the Choose commands from dropdown, select All Commands;
3) Scroll down and select the Insert Worksheet command and click the Add>> button;
4) Scroll down again and select the New Workbook command and click Add>> button. Note that the name in the list for this command is New, not New Workbook.
Now, whenever you open a new workbook or insert a new worksheet it will always contain your preferred settings.
If you chose to have only one sheet in each new workbook, you can easily insert additional sheets whenever needed by clicking on the Insert Worksheet button.
Remember that if you ever get a new computer or work on multiple computers, you can copy these two files (SHEET.XLTX AND BOOK.XLTX) so you won't have to create them again.
|"Spreadsheets Tips From An
Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630